서브쿼리·CTE·윈도우 함수

서브쿼리(subquery)는 다른 SQL 문 안에 괄호로 넣은 SELECT다. 이 문서는 서브쿼리를 위치별로 나누어 보고, 자주 비교되는 IN과 EXISTS, 결과를 통째로 비우는 NOT IN의 NULL 함정, 바깥 행마다 다시 계산되는 상관 서브쿼리(correlated subquery)를 다룬다. 서브쿼리에 이름을 붙이는 CTE(Common Table Expression, 공통 테이블 식)는 2편에서, 윈도우 함수(window function)는 3편에서 이어진다.

위치별 서브쿼리

서브쿼리는 들어가는 자리에 따라 스칼라 서브쿼리(scalar subquery), 파생 테이블(derived table, 흔히 인라인 뷰(inline view)라고도 한다), WHERE 절 서브쿼리로 나눈다.

위치	이름	돌려줘야 하는 것
SELECT 목록	스칼라 서브쿼리	값 하나(1행 1열)
FROM 절	파생 테이블	테이블 모양 결과(별칭 필수)
WHERE·HAVING	비교·IN·EXISTS 서브쿼리	비교 방식에 따라 값 하나 또는 여러 행

스칼라 서브쿼리는 값 하나를 돌려주는 서브쿼리로, 칼럼이나 상수가 올 자리에 쓸 수 있다.

sql

SELECT u.id,
       (SELECT MAX(o.created_at) FROM orders o WHERE o.user_id = u.id) AS last_order
FROM users u;

결과가 0행이면 NULL이 된다. 2행 이상이면 "Subquery returns more than 1 row"라는 에러 1242로 문장이 실패한다. 개발 때 한 행이던 결과가 데이터가 쌓여 두 행이 되는 순간 깨지므로, 한 행이 보장되지 않으면 MAX()·LIMIT 1로 한 행을 확정하거나 조인으로 바꾼다.

파생 테이블은 FROM 절에 넣은 서브쿼리 결과다. FROM 절의 모든 테이블에는 이름이 있어야 하므로 별칭이 필수다. 옵티마이저(optimizer)는 파생 테이블을 바깥 쿼리에 병합(merge)해 서브쿼리가 없었던 것처럼 실행하거나, 결과를 내부 임시 테이블(temporary table)에 구체화(materialize)해 두고 읽는다. 집계 함수, DISTINCT, GROUP BY, LIMIT, UNION, 윈도우 함수가 들어 있으면 병합할 수 없어 구체화된다. 구체화된 결과는 원래 테이블의 인덱스를 그대로 쓰지 못한다. 옵티마이저가 조인에 쓸 인덱스를 임시로 붙여 주기도 하지만, 큰 결과를 파생 테이블로 만들고 다시 조인하는 쿼리는 실행 계획(execution plan)을 보여 주는 EXPLAIN으로 확인한다. 파생 테이블은 원래 같은 FROM 절의 앞 테이블 칼럼을 참조할 수 없는데, LATERAL을 붙인 파생 테이블은 앞 테이블 칼럼을 참조할 수 있어, "회원마다 최근 주문 3건"처럼 바깥 행마다 다른 결과를 만들 수 있다.

WHERE 절 서브쿼리는 비교 연산자와 함께 쓰면 값 하나를 돌려줘야 하고, IN·ANY·ALL과 쓰면 한 칼럼의 여러 행을, EXISTS와 쓰면 행이 있는지만 본다.

IN과 EXISTS

sql

-- IN: 값이 서브쿼리 결과 목록 안에 있는가
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- EXISTS: 조건에 맞는 행이 하나라도 있는가
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

IN은 바깥 값이 서브쿼리가 돌려준 값 목록에 들어 있는지 본다. EXISTS는 서브쿼리가 한 행이라도 돌려주는지만 보므로 SELECT 목록에 무엇을 쓰든 상관없고, 관례로 SELECT 1을 쓴다. 두 쿼리의 뜻은 "짝이 있는 회원을 한 번씩"으로 같다. 이렇게 짝이 있는지만 보는 조인을 세미 조인(semi-join)이라 한다.

예전에는 "큰 서브쿼리에는 EXISTS가 빠르다"는 규칙이 널리 쓰였다. MySQL 8.x의 옵티마이저는 IN과 EXISTS 서브쿼리를 모두 세미 조인으로 바꿔, 조인 순서와, 주문이 여러 건인 회원을 한 번만 남기는 중복 제거 방법을 비용으로 고를 수 있다. 그래서 둘 중 무엇이 빠른지는 문장 모양보다 실행 계획이 정한다. 뜻에서 정말로 갈리는 곳은 부정형과 NULL이다.

NOT IN의 NULL 함정

x NOT IN (1, 2, NULL)은 x <> 1 AND x <> 2 AND x <> NULL과 같다. NULL과 비교한 마지막 x <> NULL이 참도 거짓도 아닌 '알 수 없음'(NULL)이라 전체가 참이 될 수 없고, 결과는 한 행도 나오지 않는다.

sql

-- orders.user_id에 NULL이 하나라도 있으면 빈 결과
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);
-- 안전한 두 가지
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

NOT EXISTS는 "짝인 행이 있는가"만 보므로 NULL에 영향을 받지 않는다. 그래서 짝이 없는 행을 찾는 안티 조인(anti-join)은 NOT EXISTS로 쓰는 것이 기본이다. 성능에도 차이가 생길 수 있다. 8.0.17부터 옵티마이저가 NOT IN·NOT EXISTS를 안티 조인으로 바꿔 최적화하는데, 비교하는 칼럼이 NULL을 허용하면 NOT IN은 대부분의 경우 이 변환을 받지 못한다. NULL이 하나라도 있으면 결과가 비는 NOT IN의 뜻이 안티 조인과 달라지기 때문이다.

상관 서브쿼리

상관 서브쿼리는 바깥 쿼리의 칼럼을 참조하는 서브쿼리다. 위 EXISTS 예의 o.user_id = u.id가 그렇다. 뜻으로는 바깥 행 하나마다 서브쿼리를 다시 계산하는 것이라, 바깥 행이 많고 서브쿼리 안쪽이 인덱스를 못 타면 느려진다. 참조하지 않는 서브쿼리는 비상관 서브쿼리(non-correlated subquery)라 하며, 결과를 한 번만 계산해 둘 수 있다.

"부서 평균보다 급여가 높은 직원"을 예로 보자.

sql

-- 상관 서브쿼리: 직원마다 그 부서 평균을 다시 구하는 뜻
SELECT e.* FROM emp e
WHERE e.salary > (SELECT AVG(salary) FROM emp WHERE dept_id = e.dept_id);
-- 파생 테이블: 부서별 평균을 한 번 만들고 조인
SELECT e.* FROM emp e
JOIN (SELECT dept_id, AVG(salary) AS avg_sal FROM emp GROUP BY dept_id) d
  ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_sal;

둘은 같은 결과를 낸다. 옵티마이저가 상관 서브쿼리를 스스로 바꿔 주는 경우도 있지만 항상은 아니다. 서브쿼리 안쪽 조건 칼럼(dept_id)에 인덱스가 있는지 먼저 보고, 느리면 파생 테이블 조인이나 윈도우 함수로 바꿔 EXPLAIN으로 비교한다.

면접 포인트

"IN과 EXISTS 중 뭐가 빠른가요?" → 8.x에서는 둘 다 세미 조인 변환 대상이라 실행 계획으로 판단. 뜻이 갈리는 곳은 NOT과 NULL.
"NOT IN이 빈 결과를 내요" → 서브쿼리에 NULL이 있다. NOT EXISTS나 IS NOT NULL 조건으로 푼다.
"상관 서브쿼리가 느려요" → 안쪽 조건 인덱스 확인 → 파생 테이블 조인·윈도우 함수로 바꿔 비교.

서브쿼리·CTE·윈도우 함수 ​

위치별 서브쿼리 ​

IN과 EXISTS ​

NOT IN의 NULL 함정 ​

상관 서브쿼리 ​

면접 포인트 ​