서브쿼리

서브쿼리(subquery)는 SQL 문 안에 괄호로 넣은 SELECT 문입니다. 안쪽의 서브쿼리가 먼저 실행되고, 그 결과가 바깥 쿼리의 조건 값 자리에 들어갑니다.

'운영체제를 들은 학생의 이름'을 구하려면 표 셋을 차례로 봐야 합니다. 과목 표에서 운영체제의 과목코드를 찾고, 수강 표에서 그 과목을 들은 학번을 찾고, 학생 표에서 그 학번의 이름을 찾습니다. 쿼리를 세 번 나눠 돌리고 앞 결과를 손으로 옮겨 적을 수도 있습니다. 서브쿼리를 쓰면 앞 단계의 답을 한 문장 안에서 다음 단계의 조건으로 넘깁니다. 서브쿼리를 품은 바깥쪽 쿼리는 메인 쿼리(main query)라고 부릅니다. 실기에는 서브쿼리를 세 겹으로 겹친 문장의 결과를 쓰는 문제가 나온 적이 있습니다.

SELECT 이름FROM 학생WHERE 학번 IN (가운데SELECT 학번FROM 수강WHERE 과목코드 = (안쪽SELECT 과목코드FROM 과목WHERE 과목명 = '운영체제'))ORDER BY 학번;

SELECT 이름FROM 학생WHERE 학번 IN (가운데SELECT 학번FROM 수강WHERE 과목코드 = ('OS'))ORDER BY 학번;

SELECT 이름FROM 학생WHERE 학번 IN (101,102,104)ORDER BY 학번;

SELECT 이름FROM 학생WHERE 학번 = (101,102,104)ORDER BY 학번;

SELECT 이름FROM 학생WHERE 학번 IN (가운데SELECT 학번FROM 수강WHERE 과목코드 = (안쪽SELECT 과목코드FROM 과목WHERE 과목명 = '운영체제'))ORDER BY 학번;

1안쪽부터 풀리는 차례

그림도 안쪽 괄호부터 한 겹씩 풀어 보입니다. 가장 안쪽 서브쿼리가 먼저 실행됩니다. 과목명이 '운영체제'인 행에서 과목코드 OS 하나를 돌려줍니다. OS가 괄호 자리에 들어가 가운데 쿼리의 조건은 과목코드 = 'OS'가 됩니다. 운영체제를 들은 학번 101·102·104가 나옵니다. 값이 셋이라 바깥 조건에는 여러 값을 받는 IN을 씁니다. 조건이 학번 IN (101, 102, 104)⁠가 되어 김민수·이서준·박서연 3행이 나옵니다.

2돌려주는 행 수로 갈리는 두 가지

서브쿼리는 돌려주는 행이 하나인지 여럿인지에 따라 받는 연산자가 달라집니다. 그림의 마지막 장면처럼 바깥의 IN을 =로 바꾸면 오류가 납니다. =는 값 하나만 받는데 서브쿼리가 세 값을 돌려주기 때문입니다.

3여러 값을 받는 연산자

ANY는 서브쿼리가 돌려준 값 가운데 하나라도 조건을 만족하면 참입니다. ALL은 돌려준 값 모두에 대해 조건을 만족해야 참입니다. EXISTS는 서브쿼리 결과가 한 행이라도 있으면 참이고, NOT EXISTS는 한 행도 없으면 참입니다. 어떤 값이 나오는지는 보지 않고 행이 있는지만 봅니다.

예제

결과 표는 Oracle에서 돌린 결과입니다.

세 겹 서브쿼리

그림의 쿼리입니다. 괄호 안쪽부터 한 겹씩 풀면 됩니다.

SELECT 이름
FROM 학생
WHERE 학번 IN (
  SELECT 학번
  FROM 수강
  WHERE 과목코드 = (
    SELECT 과목코드
    FROM 과목
    WHERE 과목명 = '운영체제'
  )
)
ORDER BY 학번;

단일행 서브쿼리와 다중행 서브쿼리

값 하나만 돌려주는 서브쿼리를 단일행 서브쿼리라고 합니다. =·>·< 같은 비교 연산자로 받습니다. 아래 서브쿼리는 전체 평균 점수 75 하나를 돌려주고, 바깥 조건은 점수 > 75가 됩니다.

SELECT 학번,
  과목코드,
  점수
FROM 수강
WHERE 점수 > (
  SELECT AVG(점수)
  FROM 수강
)
ORDER BY 점수 DESC;

여러 행을 돌려줄 수 있는 서브쿼리는 다중행 서브쿼리입니다. IN·ANY·ALL·EXISTS 같은 연산자로 받습니다. 같은 자리에 =를 쓰면 Oracle은 ORA-01427(single-row subquery returns more than one row) 오류를 냅니다.

SELECT 이름
FROM 학생
WHERE 학번 = (
  SELECT 학번
  FROM 수강
  WHERE 과목코드 = 'OS'
);

ANY와 ALL

아래 두 쿼리는 자료구조(DS) 점수를 운영체제(OS) 점수 75·75·60과 견줍니다. > ALL은 가장 큰 값 75보다 커야 하므로 2행입니다.

SELECT 학번,
  점수
FROM 수강
WHERE 과목코드 = 'DS'
  AND 점수 > ALL (
    SELECT 점수
    FROM 수강
    WHERE 과목코드 = 'OS'
  )
ORDER BY 학번;

> ANY는 가장 작은 값 60보다 크기만 하면 되므로 DS 4행이 모두 나옵니다.

SELECT 학번,
  점수
FROM 수강
WHERE 과목코드 = 'DS'
  AND 점수 > ANY (
    SELECT 점수
    FROM 수강
    WHERE 과목코드 = 'OS'
  )
ORDER BY 학번;

EXISTS

아래 서브쿼리는 바깥 학생 행의 S.학번을 받아, 학생 행마다 다시 실행됩니다. 수강 기록이 한 행도 없는 학생은 106 정하윤뿐입니다.

SELECT S.학번,
  S.이름
FROM 학생 S
WHERE NOT EXISTS (
  SELECT *
  FROM 수강 E
  WHERE E.학번 = S.학번
);

시험 포인트

• 필기: 서브쿼리는 가장 안쪽부터 풉니다. 안쪽 결과가 바깥 조건의 값 자리에 들어갑니다. 값 하나는 =·> 같은 비교 연산자로, 여러 값은 IN·ANY·ALL로 받습니다. = 자리에 여러 행이 오면 오류입니다. > ALL은 가장 큰 값보다 크다, > ANY는 가장 작은 값보다 크다는 뜻입니다. IN은 = ANY와 같습니다. EXISTS는 서브쿼리 결과에 행이 하나라도 있으면 참입니다.
• 실기: 세 겹 서브쿼리의 결과를 안쪽부터 풀어 표로 씁니다. 이름은 서브쿼리(Subquery), 메인 쿼리(Main Query)로 씁니다.

확인 문제

1. 다음 네 서브쿼리 가운데 단일행 서브쿼리인 것을 고르세요.

• ① SELECT 학과코드 FROM 학생 WHERE 학년 = 3
• ② SELECT 점수 FROM 수강 WHERE 과목코드 = 'OS'
• ③ SELECT 학점 FROM 과목 GROUP BY 학점
• ④ SELECT COUNT(*) FROM 과목

2. 다음 (가)·(나)에 맞는 이름을 한글 이름과 영문으로 함께 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 학과별 학생 수를 먼저 구해 종이에 적고 그 값을 다음 SELECT 문의 WHERE 절에 손으로 옮겨 적는 대신, 앞 SELECT 문을 괄호에 넣어 WHERE 절 안에 그대로 적었습니다. 괄호 안에 들어간 쪽의 이름입니다.
• (나) 같은 문장에서 그 괄호를 품은 쪽, 곧 괄호의 결과를 조건 값으로 받아 최종 결과를 내는 쪽의 이름입니다.

3. 다음 쿼리의 결과를 표로 쓰세요.

SELECT 학번,
  점수
FROM 수강
WHERE 과목코드 = 'NW'
  AND 점수 >= ALL (
    SELECT AVG(점수)
    FROM 수강
    GROUP BY 과목코드
  )
ORDER BY 학번;

더 자세히: SQLD의 서브쿼리