서브쿼리가 왜 필요한가

13편(WHERE 절)과 15편(그룹 함수)까지는 조건에 들어가는 값을 우리가 직접 숫자나 문자로 적었다. 그런데 “평균 급여보다 많이 받는 사원을 찾아줘”처럼, 조건에 넣을 값 자체를 다른 조회 결과로부터 구해야 하는 경우가 훨씬 많다. 평균 급여는 미리 알 수 없고 EMP 테이블을 조회해봐야 나오는 값이기 때문이다.

이럴 때 하나의 SQL 문(쿼리) 안에 또 다른 SELECT 문을 괄호로 넣어서, 안쪽 쿼리의 결과를 바깥쪽 쿼리의 조건이나 값으로 쓰는 것이 서브쿼리(Subquery, 하위 질의)다. 바깥쪽 쿼리를 메인쿼리(main query) 또는 아우터 쿼리(outer query)라고 부른다.

쉽게 말하면: 서브쿼리는 “괄호 안의 질문을 먼저 풀고, 그 답을 이 자리에 넣어라”는 SQL 안의 SQL이다.

실습용 데이터 — 16편 테이블에 급여를 추가한다

16편(표준 조인)에서 쓴 EMP·DEPT 테이블을 그대로 쓰되, 급여를 비교할 수 있도록 EMP에 SALARY(급여) 컬럼을 하나 추가한다.

DEPT(부서) 테이블

EMP(사원) 테이블 — SALARY(급여, 단위: 만 원) 추가

16편에서 짚었듯, 최지우는 DEPT_ID가 NULL이고 정하은은 DEPT 테이블에 없는 DEPT_ID(50)를 갖고 있다. 이 두 행이 이번 편의 NULL 함정에서도 다시 등장한다.

서브쿼리의 위치별 분류

서브쿼리는 SQL 문 안 어디에 놓이느냐에 따라 세 가지로 나뉜다. 위치가 달라져도 “괄호 안에 SELECT 문을 넣는다”는 본질은 같다.

스칼라 서브쿼리 예시. 사원마다 전체 평균 급여를 나란히 보여준다.

SELECT NAME, SALARY,
       (SELECT ROUND(AVG(SALARY)) FROM EMP) AS AVG_SAL
FROM EMP;

EMP가 5행이므로 이 스칼라 서브쿼리는 5번 반복 평가되는 것처럼 보이지만, 실제로는 같은 입력에 대해 옵티마이저가 한 번만 계산하고 캐싱해서 재사용하는 경우가 많다. 다만 개념적으로는 “메인쿼리의 각 행마다 실행된다”고 이해해두면 된다. 스칼라 서브쿼리가 2건 이상을 반환하면 에러가 나고, 0건을 반환하면 에러가 아니라 NULL을 돌려준다는 점이 자주 출제된다.

인라인 뷰 예시. 부서별 평균 급여를 먼저 구하고, 그 결과를 DEPT와 다시 조인한다.

SELECT D.DNAME, V.AVG_SAL
FROM DEPT D
JOIN (SELECT DEPT_ID, AVG(SALARY) AS AVG_SAL
      FROM EMP
      GROUP BY DEPT_ID) V
ON D.DEPT_ID = V.DEPT_ID;

괄호 안의 서브쿼리가 만들어낸 결과(DEPT_ID별 평균 급여 표)를 V라는 임시 이름의 테이블처럼 취급해서 DEPT와 조인한 것이다. 이것이 FROM 절 서브쿼리, 즉 인라인 뷰다.

단일행 서브쿼리와 단일행 연산자

서브쿼리가 딱 1개의 값(1행)만 반환한다고 보장될 때는 =, >, <, >=, <=, <> 같은 단일행 비교 연산자를 그대로 쓸 수 있다.

SELECT NAME, SALARY
FROM EMP
WHERE SALARY > (SELECT AVG(SALARY) FROM EMP);

EMP 전체 급여 (5000, 4500, 3000, 3500, 4000)의 평균은 4000이다. 이 값보다 급여가 많은 사원은 김민준(5000)과 이서연(4500) 2명이다.

단일행 연산자에 다중행 서브쿼리를 잘못 연결하면 실행 시점에 에러가 난다. 예를 들어 WHERE SALARY = (SELECT SALARY FROM EMP WHERE DEPT_ID = 20)처럼 쓰면, 괄호 안 서브쿼리가 이서연(4500)과 박도윤(3000) 두 값을 반환하므로 “단일 행 하위 질의에 두 개 이상의 행이 반환됨” 오류가 발생한다. 서브쿼리가 몇 건을 반환할지 미리 확신할 수 없다면 단일행 연산자를 쓰면 안 된다는 뜻이다.

다중행 서브쿼리와 다중행 연산자 — IN, ANY, ALL

서브쿼리가 여러 행을 반환할 수 있을 때는 IN, ANY(또는 SOME, 같은 뜻이다), ALL 같은 다중행 연산자를 쓴다. 세 연산자의 의미가 서로 다르므로 하나씩 수치로 확인한다.

IN — 목록 중 하나와 같으면 참

IN은 서브쿼리가 반환한 값들의 목록 중 어느 하나와라도 같으면 참이다.

SELECT NAME
FROM EMP
WHERE DEPT_ID IN (SELECT DEPT_ID FROM DEPT WHERE DNAME IN ('영업부', '개발부'));

안쪽 서브쿼리는 영업부와 개발부의 DEPT_ID인 (10, 20)을 반환한다. 바깥쪽은 DEPT_ID가 10이거나 20인 사원, 즉 김민준·이서연·박도윤 3명을 찾는다.

ANY(SOME) — 하나라도 만족하면 참, > ANY는 최솟값과 비교하는 것과 같다

ANY는 서브쿼리가 반환한 값들 중 적어도 하나와 비교해서 조건을 만족하면 참이다. > ANY (여러 값)은 “그 값들 중 가장 작은 값보다 크기만 해도 통과”라는 뜻이 되므로, 결국 최솟값과 비교하는 것과 같은 효과를 낸다.

SELECT NAME, SALARY
FROM EMP
WHERE SALARY > ANY (SELECT SALARY FROM EMP WHERE DEPT_ID = 20);

개발부(DEPT_ID 20) 사원의 급여는 이서연 4500, 박도윤 3000이다. 이 중 하나보다만 크면 되므로, 사실상 SALARY > 3000(최솟값보다 크다)과 같은 조건이 된다. 조건을 만족하는 사원은 김민준(5000), 이서연(4500), 정하은(4000), 최지우(3500)로 4명이다. 박도윤 자신은 3000으로 3000보다 크지 않으므로 제외된다.

ALL — 전부 만족해야 참, > ALL은 최댓값과 비교하는 것과 같다

ALL은 서브쿼리가 반환한 값 전부에 대해 조건을 만족해야 참이다. > ALL (여러 값)은 “그 값들 중 가장 큰 값보다도 커야 통과”라는 뜻이므로, 최댓값과 비교하는 것과 같은 효과를 낸다.

SELECT NAME, SALARY
FROM EMP
WHERE SALARY > ALL (SELECT SALARY FROM EMP WHERE DEPT_ID = 20);

같은 서브쿼리 값 (4500, 3000) 중 가장 큰 값인 4500보다도 커야 하므로, 사실상 SALARY > 4500과 같은 조건이 된다. 이 조건을 만족하는 사원은 김민준(5000) 1명뿐이다.

> ANY는 최솟값 비교로, > ALL은 최댓값 비교로 바뀐다는 것이 이 단원의 가장 흔한 함정이자 기출 단골 포인트다. 반대로 < ANY는 최댓값보다 작기만 해도 되므로 최댓값 비교로, < ALL은 최솟값보다도 작아야 하므로 최솟값 비교로 바뀐다는 점도 함께 기억해두면 좋다.

상관 서브쿼리 — 메인쿼리 행마다 반복 실행된다

지금까지의 서브쿼리는 메인쿼리와 무관하게 독립적으로 딱 한 번 실행되고, 그 결과값(또는 목록)이 메인쿼리에 그대로 쓰였다. 이런 서브쿼리를 비연관(non-correlated) 서브쿼리라고 부른다.

이와 달리 상관 서브쿼리(Correlated Subquery, 연관 서브쿼리)는 서브쿼리 안에서 메인쿼리의 컬럼을 참조한다. 그래서 메인쿼리가 검토하는 행이 바뀔 때마다 서브쿼리도 그 행의 값을 가지고 다시 실행된다.

SELECT E.NAME, E.SALARY
FROM EMP E
WHERE E.SALARY > (
  SELECT AVG(E2.SALARY)
  FROM EMP E2
  WHERE E2.DEPT_ID = E.DEPT_ID
);

이처럼 상관 서브쿼리는 메인쿼리의 행 수만큼 반복 실행된다는 점이 비연관 서브쿼리와의 결정적 차이다. “부서 평균보다 급여가 많은 사원 찾기”처럼 그룹별 기준값과 개별 행을 동시에 비교해야 하는 문제는 상관 서브쿼리가 아니면 한 번에 풀기 어렵다.

EXISTS — 존재 여부만 확인하는 상관 서브쿼리

EXISTS는 대표적인 상관 서브쿼리 연산자다. 서브쿼리가 행을 1건이라도 찾으면 그 순간 TRUE로 확정하고 더 이상 탐색하지 않는다. 값 자체가 아니라 “존재하느냐 아니냐”만 본다.

SELECT D.DNAME
FROM DEPT D
WHERE EXISTS (
  SELECT 1 FROM EMP E WHERE E.DEPT_ID = D.DEPT_ID
);

DEPT의 각 부서를 메인쿼리가 하나씩 검토하면서, “이 부서 번호를 가진 사원이 EMP에 한 명이라도 있는가”를 서브쿼리로 확인한다. 영업부(김민준)와 개발부(이서연, 박도윤)는 사원이 있으므로 EXISTS가 참이 되어 결과에 남는다. 인사부와 마케팅부는 소속 사원이 EMP 어디에도 없으므로 EXISTS가 거짓이 되어 제외된다. 결과는 2행(영업부, 개발부)이다. EXISTS의 서브쿼리에 SELECT 1처럼 아무 값이나 쓰는 관행은, 어차피 값 자체는 보지 않고 행의 존재 여부만 확인하기 때문이다.

IN과 EXISTS의 차이, 그리고 NOT IN의 NULL 함정

IN과 EXISTS는 비슷한 결과를 낼 때가 많아 자주 헷갈리지만 동작 방식이 다르다.

여기서 가장 위험한 함정이 NOT IN이다. IN의 반대말인 NOT IN은 “서브쿼리가 반환한 값 목록 중 어느 것과도 같지 않아야” 참이 되는데, 그 목록 안에 NULL이 단 하나라도 섞여 있으면 전체 결과가 텅 비어버린다.

왜 그런지 EMP 데이터로 직접 확인해본다. 최지우의 DEPT_ID가 NULL이라는 점을 다시 활용한다.

SELECT NAME FROM DEPT_LIST
WHERE DEPT_ID NOT IN (SELECT DEPT_ID FROM EMP);

EMP.DEPT_ID의 값 목록은 (10, 20, 20, NULL, 50)이다. NOT IN은 내부적으로 “값 <> 10 그리고 값 <> 20 그리고 값 <> 20 그리고 값 <> NULL 그리고 값 <> 50이 전부 참이어야 한다”는 식으로 풀어서 판단한다. 그런데 어떤 값과 NULL을 비교하는 값 <> NULL은 참도 거짓도 아닌 UNKNOWN이 된다. AND로 연결된 조건 중 하나라도 UNKNOWN이 섞이면 전체 결과도 참이 될 수 없으므로, NOT IN 전체가 모든 행에 대해 참이 아닌 것으로 처리되어 결과가 0건이 되어버린다. 부서가 하나도 안 남는, 명백히 의도와 다른 결과다.

이 함정을 피하는 방법은 두 가지다. 첫째, 서브쿼리에 WHERE DEPT_ID IS NOT NULL 조건을 추가해 NULL을 미리 제거한다. 둘째, NOT IN 대신 NOT EXISTS(상관 서브쿼리)를 쓴다. NOT EXISTS는 값 목록과의 비교가 아니라 “존재하지 않음”만 확인하므로 NULL이 섞여 있어도 왜곡되지 않는다. 서브쿼리 결과에 NULL이 포함될 가능성이 있다면 NOT IN보다 NOT EXISTS를 쓰는 것이 안전하다는 것이 이 단원에서 가장 자주 출제되는 결론이다.

직접 해보기

직접 해보기 1: NOT IN에 NULL이 섞이면 결과가 통째로 비는가

먼저 EMP.DEPT_ID 목록에 NULL이 섞여 있는지 눈으로 확인한 뒤, 같은 조건을 NOT IN과 NOT EXISTS로 각각 실행해 비교한다.

SELECT DEPT_ID FROM EMP;

-- 예상: EMP에 없는 부서(30, 40)가 나올 것 같은데, 실제로 몇 행이 나올까?
SELECT NAME FROM DEPT_LIST
WHERE DEPT_ID NOT IN (SELECT DEPT_ID FROM EMP);

SELECT D.NAME FROM DEPT_LIST D
WHERE NOT EXISTS (
  SELECT 1 FROM EMP E WHERE E.DEPT_ID = D.DEPT_ID
);

무엇을 보아야 하나: 첫 쿼리에서 NULL이 섞여 있는 것을 확인한다. 두 번째 NOT IN 쿼리가 예상과 다르게 몇 행을 내는지, 세 번째 NOT EXISTS 쿼리와 결과가 어떻게 다른지 비교한다.

왜 이걸 해보나: NOT IN에 NULL이 섞이면 전체 결과가 0건이 되는 함정은 SQLD 최다 오답 유형 중 하나다. 직접 빈 결과를 보고, 같은 조건을 NOT EXISTS로 바꾸자 결과가 정상적으로 나오는 것까지 봐야 왜 NOT EXISTS를 권장하는지 확신이 생긴다.

직접 해보기 2: ANY와 ALL이 각각 최솟값·최댓값 비교로 바뀌는 것 확인하기

개발부(DEPT_ID 20) 사원 급여는 이서연 4500, 박도윤 3000이다. 같은 서브쿼리에 ANY와 ALL만 바꿔 넣고 결과 인원 수를 비교한다.

-- 예상: > ANY는 몇 명이 나올까? (최솟값 3000보다 크면 통과)
SELECT NAME, SALARY FROM EMP
WHERE SALARY > ANY (SELECT SALARY FROM EMP WHERE DEPT_ID = 20);

-- 예상: > ALL은 몇 명이 나올까? (최댓값 4500보다 커야 통과)
SELECT NAME, SALARY FROM EMP
WHERE SALARY > ALL (SELECT SALARY FROM EMP WHERE DEPT_ID = 20);

무엇을 보아야 하나: ANY 결과의 인원 수가 ALL 결과의 인원 수보다 많은지, 두 결과에서 차이 나는 사람이 정확히 누구인지 확인한다.

왜 이걸 해보나: ANY와 ALL을 반대로 외우는 실수가 기출 단골이다. 같은 데이터에 두 연산자를 번갈아 돌려 인원 수 차이를 직접 보면, “ANY는 관대하게(최솟값), ALL은 까다롭게(최댓값)“라는 규칙이 암기가 아니라 확인된 사실로 남는다.

자주 틀리는 점

• 다중행 서브쿼리에 = 같은 단일행 연산자를 쓰면 실행 시점 에러가 난다. 서브쿼리가 몇 건을 반환하는지 코드만 보고 판단하는 훈련이 필요하다.
• > ANY와 > ALL을 반대로 외우는 실수가 매우 잦다. ANY는 “관대한” 쪽(최솟값 기준)이고 ALL은 “까다로운” 쪽(최댓값 기준)이라고 짝지어 기억하면 헷갈리지 않는다.
• NOT IN 서브쿼리에 NULL이 섞이면 결과가 0건이 된다는 것을 모르고 지나가는 경우가 가장 흔한 실수다. NOT IN을 쓸 때는 반드시 서브쿼리 결과에 NULL 가능성이 있는지부터 점검하는 습관을 들인다.
• 상관 서브쿼리가 몇 번 실행되는지 묻는 문항에서, “딱 한 번 실행된다”고 잘못 답하는 경우가 많다. 상관 서브쿼리는 원칙적으로 메인쿼리의 행 수만큼 반복 실행된다.
• 스칼라 서브쿼리가 0건을 반환하면 에러가 난다고 착각하기 쉽지만, 실제로는 에러가 아니라 NULL을 반환한다. 2건 이상을 반환할 때만 에러가 난다.

핵심 정리

• 서브쿼리는 위치에 따라 스칼라 서브쿼리(SELECT 절), 인라인 뷰(FROM 절), 중첩 서브쿼리(WHERE·HAVING 절)로 나뉜다.
• 서브쿼리가 1행만 반환하면 단일행 연산자(=, > 등), 여러 행을 반환하면 다중행 연산자(IN, ANY, ALL)를 쓴다.
• > ANY는 최솟값과의 비교로, > ALL은 최댓값과의 비교로 바뀐다.
• 상관 서브쿼리는 메인쿼리의 컬럼을 참조하며, 메인쿼리 행 수만큼 반복 실행된다. EXISTS가 대표적이다.
• NOT IN 서브쿼리 결과에 NULL이 섞이면 전체 결과가 0건이 되므로, NULL 가능성이 있으면 NOT EXISTS를 쓴다.

마무리 복습

참고 자료

• Oracle Database SQL Language Reference - Subqueries  — 단일행·다중행·상관 서브쿼리와 EXISTS 문법