서브쿼리

서브쿼리(subquery)는 SQL 문 안에 괄호로 넣은 SELECT 문입니다. 서브쿼리가 먼저 돌려준 값이 메인 쿼리의 조건이나 테이블 자리에 들어갑니다.

"박지호보다 월급이 많은 사원은 누구인가?"에 답하려면 먼저 박지호의 월급을 알아야 합니다. 쿼리를 두 번 돌려 결과를 옮겨 적는 대신, 서브쿼리로 앞 질문의 답을 뒤 질문의 조건에 넘깁니다. 서브쿼리를 품은 바깥쪽 쿼리는 메인 쿼리(main query)라 합니다. 자료에 따라 바깥 쿼리라고도 부르며, 어느 이름으로 나와도 같은 것입니다.

SELECT ENAME,  SALFROM EMPWHERE SAL > (  서브쿼리SELECT SAL
FROM EMP
WHERE ENAME = '박지호')ORDER BY SAL DESC;

SELECT ENAME,  SALFROM EMPWHERE SAL > ()ORDER BY SAL DESC;

SELECT ENAME,  SALFROM EMPWHERE SAL > (410)ORDER BY SAL DESC;

SELECT ENAME,  DEPTNOFROM EMPWHERE DEPTNO IN (10,40);

SELECT DEPTNO,  DNAMEFROM DEPTWHERE DEPTNO NOT IN (  10,20,20,30,NULL,20);

SELECT E.ENAME,  E.SALFROM EMP EWHERE E.SAL > (420);

스칼라 서브쿼리SELECT E.ENAME,  (SELECT …) AS DNAME…인라인 뷰SELECT *FROM (SELECT …)WHERE AVG_SAL >= 450…중첩 서브쿼리…WHERE E.SAL > (SELECT …);

SELECT ENAME,  SALFROM EMPWHERE SAL > (  서브쿼리SELECT SAL
FROM EMP
WHERE ENAME = '박지호')ORDER BY SAL DESC;

SELECT ENAME,  SALFROM EMPWHERE SAL > ()ORDER BY SAL DESC;

SELECT ENAME,  SALFROM EMPWHERE SAL > (410)ORDER BY SAL DESC;

SELECT ENAME,  SALFROM EMPWHERE SAL > (  서브쿼리SELECT SAL
FROM EMP
WHERE ENAME = '박지호')ORDER BY SAL DESC;

1서브쿼리와 메인 쿼리

조건 SAL > 410에서 박지호 자신은 410이 410보다 크지 않아 빠집니다. 한 행만 돌려주는 서브쿼리를 단일행 서브쿼리(single-row subquery)라 하고, =·<>·>·>=·<·<= 같은 단일행 연산자와 씁니다. 단일행 서브쿼리가 0행을 돌려주면 그 자리는 NULL이 됩니다. 그래서 없는 사원 '홍길동'의 월급과 견주면 오류 없이 0행입니다.

2여러 행을 돌려줄 때

값 하나만 받는 = 자리에 서브쿼리가 두 행 이상을 돌려주면 ORA-01427 오류입니다. 여러 행을 돌려줄 수 있는 다중행 서브쿼리(multiple-row subquery)는 다중행 연산자와 씁니다.

NOT IN은 목록의 모든 값과 달라야 참입니다. 목록에 NULL이 있으면 'NULL과 다르다'가 알 수 없음(UNKNOWN)이라 어느 행도 남지 않습니다. = ANY는 IN과, <> ALL은 NOT IN과 같은 뜻이라 <> ALL에도 같은 함정이 있습니다. NOT EXISTS에는 이 함정이 없습니다.

3연관 서브쿼리

연관 서브쿼리(correlated subquery)는 서브쿼리 안에서 메인 쿼리의 열을 씁니다. 그래서 개념상 메인 쿼리 행마다 다시 실행되고, 메인 쿼리의 열을 쓰지 않는 비연관 서브쿼리(uncorrelated subquery)는 한 번만 실행됩니다. 자기 부서 평균과 견줄 때 정하늘은 DEPTNO = NULL이 참인 행이 없어 평균이 NULL이라 빠집니다. 거꾸로 메인 쿼리는 WHERE 절 서브쿼리에만 쓴 테이블의 열을 쓸 수 없습니다(ORA-00904). 그 열이 필요하면 조인이나 인라인 뷰(FROM 절의 서브쿼리)로 씁니다.

4쓰는 자리에 따른 이름

HAVING 절과 ORDER BY 절에도 서브쿼리를 쓸 수 있지만, GROUP BY 절에는 못 씁니다. WHERE 절 서브쿼리 안에는 ORDER BY를 쓸 수 없고, 인라인 뷰 안에는 쓸 수 있습니다. 그래서 Top N 쿼리(정렬한 결과에서 위의 N행만 꺼내는 쿼리 — Top N 쿼리 레슨에서 자세히)는 인라인 뷰 안에서 먼저 정렬합니다. 인라인 뷰에서 붙인 별칭은 메인 쿼리의 WHERE에서 쓸 수 있습니다. 같은 쿼리의 SELECT 별칭을 WHERE에 못 쓰는 것과 다릅니다.

예제

사원 없는 부서를 NOT IN으로 찾으면 서브쿼리 결과 10, 20, 20, 30, NULL, 20⁠의 NULL 때문에 0행입니다.

SELECT DEPTNO,
  DNAME
FROM DEPT
WHERE DEPTNO NOT IN (
  SELECT DEPTNO
  FROM EMP
);

서브쿼리에서 NULL을 빼면 40번 인사 부서가 나옵니다.

SELECT DEPTNO,
  DNAME
FROM DEPT
WHERE DEPTNO NOT IN (
  SELECT DEPTNO
  FROM EMP
  WHERE DEPTNO IS NOT NULL
);

NOT EXISTS로 써도 같습니다. EXISTS는 행이 있는지만 봐서 SELECT 목록에 흔히 상수 1을 씁니다.

SELECT D.DEPTNO,
  D.DNAME
FROM DEPT D
WHERE NOT EXISTS (
  SELECT 1
  FROM EMP E
  WHERE E.DEPTNO = D.DEPTNO
);

열 둘 이상을 짝지어 견주는 다중열 서브쿼리(multiple-column subquery)로 부서마다 월급이 가장 많은 사원을 찾습니다.

SELECT ENAME,
  DEPTNO,
  SAL
FROM EMP
WHERE (DEPTNO, SAL) IN (
  SELECT DEPTNO,
    MAX(SAL)
  FROM EMP
  GROUP BY DEPTNO
)
ORDER BY DEPTNO;

서브쿼리가 NULL 그룹의 (NULL, 300)도 돌려주지만 NULL = NULL이 참이 아니라 정하늘은 빠집니다. 다중열 서브쿼리는 Oracle에서 되고, SQL Server의 IN은 서브쿼리 결과가 한 열이어야 해서 이 꼴을 쓸 수 없습니다.

시험 포인트

• 단일행 연산자 자리에 여러 행이 오면 오류이고, 서브쿼리가 0행이면 결과가 0행입니다.
• > ALL은 가장 큰 값보다, > ANY는 가장 작은 값보다 크다는 뜻입니다.
• NOT IN·<> ALL의 목록에 NULL이 있으면 0행입니다.
• 자료에 따라 연관 서브쿼리를 상관 서브쿼리라고도 부릅니다. 어느 이름으로 나와도 같은 것입니다.
• 메인 쿼리는 WHERE 절 서브쿼리에만 쓴 테이블의 열을 쓸 수 없습니다.
• GROUP BY 절에는 서브쿼리를, WHERE 절 서브쿼리 안에는 ORDER BY를 쓸 수 없습니다.

확인 문제

1. 부하가 없는 사원을 찾으려고 다음 쿼리를 썼습니다. 결과는 몇 행일까요?

SELECT EMPNO,
  ENAME
FROM EMP
WHERE EMPNO NOT IN (
  SELECT MGR
  FROM EMP
);

SELECT EMPNO,
  ENAME
FROM EMP
WHERE EMPNO NOT IN (
  SELECT MGR
  FROM EMP
  WHERE MGR IS NOT NULL
)
ORDER BY EMPNO;

2. 다음 쿼리는 어떤 사원을 찾고, 결과는 무엇일까요?

SELECT E.EMPNO,
  E.ENAME
FROM EMP E
WHERE EXISTS (
  SELECT 1
  FROM EMP M
  WHERE M.MGR = E.EMPNO
)
ORDER BY E.EMPNO;

3. 강도윤이 관리하는 사원(MGR이 1006) 누구보다도 월급이 적은 사원을 찾다가 오류가 났습니다. 까닭과 고칠 방법은 무엇일까요?

SELECT ENAME
FROM EMP
WHERE SAL < (
  SELECT SAL
  FROM EMP
  WHERE MGR = 1006
);

SELECT ENAME
FROM EMP
WHERE SAL < ALL (
  SELECT SAL
  FROM EMP
  WHERE MGR = 1006
);