[데이터베이스] 13. SQL 서브 쿼리

SQL의 쿼리는 FROM, WHERE, HAVING 절 안에서도 중첩되어 등장할 수 있다.

103번 보트를 예약한 선원의 이름을 출력할 때 다음과 같이 중첩된 쿼리를 통해서도 문제를 해결할 수 있다.

 

 

이는 마치 중첩된 반복문을 작성하는 것과 비슷하다.

선원 테이블에서 모든 선원을 가져오고, 각 선원에 대해서 돌면서 (for each) 예약테이블을 돌면서 선원 정보를 찾는 것과 비슷하다.

이때 IN 연산자는 집합 연산자로서 해당 서브쿼리의 결과 집합에 S.sid 가 존재하는 sid 만을 조회한다.

 

 

서브쿼리와 관련된 간단한 연습문제를 몇 가지 풀어보자.

 

1. 빨간색 배를 예약한 적이 있는 / 없는 선원 이름 조회

SELECT S.sname
FROM sailors S
WHERE S.sid IN (
    SELECT R.sid
    FROM Reserves R, Boats B
    WHERE R.bid = B.bid AND B.color = 'red'
)

 

예약한 적이 없는 선원은 IN 을 NOT IN 으로 바꾸면 된다.

 

 

103번 보트를 예약한 선원의 이름을 조회할 때, 다음과 같이 쿼리를 작성할 수도 있다.

 

그림에서 보는 것처럼, 서브쿼리에는 바깥에 있는 쿼리의 테이블 데이터를 참조할 수 있다.

EXISTS 연산자는 해당 집합이 공집합인지 아닌지를 검사하며, 공집합이 아니라면 TRUE 를 반환한다.

이 SQL의 실행 과정은 역시 중첩 반복문처럼 생각하면 된다.

각 Sailors 의 튜플에 대해 서브 쿼리가 반복적으로 실행되면서 그 조건이 TRUE 가나오는 튜플만을 선별한다.

 

(강의록에서는 EXISTS 자리에 UNIQUE 도 사용할 수 있는 것처럼 말하는데, postgresql 기준으로는 지원하지 않는 것 같다.)

 

---

 

EXISTS 외에도 op ANY, op ALL 과 같은 연산자도 있다.

이 연산자는 op 를 만족하는 튜플이 하나라도 있으면 참 (any), 모든 튜플에 대해 op를 만족하면 참 (all) 을 명시할 수 있다.

 

예를 들면 다음과 같은 문제를 푸는데 사용될 수 있다.

 

 

2. 'Horatio' 라는 이름의 선원들 중 적어도 한 명보다 등급이 높은 선원 찾기

SELECT *
FROM sailors S
WHERE S.rating > ANY (SELECT S2.rating
                      FROM sailors S2
                      WHERE S2.sname = 'Horatio');

 

 

3. 'Horatio' 라는 이름의 선원들 모두보다 등급이 높은 선원 찾기

'Horatio' 라는 이름의 선원들 중 제일 높은 등급을 가진 사람보다 더 큰 등급을 갖는 사람을 찾는 것과도 같다.

SELECT *
FROM sailors S
WHERE S.rating > ALL (SELECT S2.rating
                      FROM sailors S2
                      WHERE S2.sname = 'Horatio');

 

 

4. 가장 높은 등급을 가진 선원 찾기

SELECT *
FROM sailors S
WHERE S.rating >= ALL (SELECT S2.rating
                       FROM sailors S2);

 

 

 

또는 max() 집계함수를 사용하여

SELECT *
FROM sailors S
WHERE S.rating = (SELECT max(S2.rating)
                  FROM sailors S2);

 

이렇게 해도 된다.

 

 

5. 빨간 배, 녹색 배를 둘 다 예약한 적이  있는 선원의 이름

이전 글에서 기본 쿼리를 정리할 때 INTERSECT 를 사용해서 풀었던 문제다.

만약 SQL 에서 INTERSECT를 지원하지 않는다면 다음과 같이 서브쿼리와 IN 을 사용해서 풀 수 있다.

SELECT S.sname
FROM sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid
AND B.color = 'red'
AND S.sid IN (SELECT S2.sid
              FROM sailors S2, Reserves R2, Boats B2
              WHERE S2.sid = R2.sid AND R2.bid = B2.bid
              AND B2.color = 'green');

 

 

 

---

마지막으로 관계 대수에서 division 연산을 기본 연산으로 바꾸어서 표현했던 것처럼,

division 연산을 SQL로 작성해보자.

 

6. 모든 배를 예약한 적이 있는 선원의 이름 구하기

모든 배를 예약한 적이 있다는 것은, 이 사람이 예약한 배 리스트를 전체 배 리스트와 차집합 연산한 결과가 공집합이라는 것과 같다.

(전체 배 리스트 - 이 사람이 예약한 배 리스트 = φ)

 

SELECT S.sname
FROM sailors S
WHERE NOT EXISTS (SELECT B1.bid
                  FROM Boats B1
                  EXCEPT
                  SELECT R.bid
                  FROM Reserves R
                  WHERE R.sid = S.sid);

 

 

만약 EXCEPT 를 지원하지 않으면 NOT IN 연산자를 사용하면 된다.

이 풀이는 생략한다. (Postgresql 은 잘 지원하는듯)