서브쿼리와 집합 연산 - 단일행 다중행 상관 서브쿼리 EXISTS UNION EXCEPT (SQLD 기본 10장)

이 장에서 배우는 것

9장에서 테이블을 옆으로 이어 붙였다. 이 장은 쿼리 안에 쿼리를 넣는 서브쿼리와, 결과 집합을 위아래로 합치고 빼는 집합 연산이다. 기본 문법은 『SQL 실전 기초』 8단원에 있다. 여기서는 서브쿼리가 몇 행을 돌려주는가, NULL 이 섞이면 무엇이 바뀌는가를 기준으로 결과를 예측한다.

• 서브쿼리를 위치(WHERE·SELECT·FROM)와 반환 형태(단일행·다중행·다중 컬럼)로 나누고, 비교 연산자와의 짝을 정리한다.
• NOT IN 과 NULL, 빈 집합에 대한 ALL, 상관 서브쿼리의 동작을 실행으로 확인한다.
• UNION·UNION ALL·INTERSECT·EXCEPT 의 행 수와, 결과 컬럼 이름·정렬 규칙을 예측한다.

핵심 개념

"직원이 한 명도 없는 팀을 찾아 달라"는 요청에 NOT IN (SELECT team_id FROM staff) 로 답한 쿼리가 몇 달 동안 잘 돌다가 어느 날 빈 결과를 내기 시작했다. 그날 팀이 없는 직원이 한 명 입사했다. 쿼리는 바뀌지 않았고 데이터만 바뀌었다. 6장의 3값 논리가 실제로 사고를 내는 장면이다.

서브쿼리의 분류

기준	종류	설명
위치	스칼라 서브쿼리	SELECT 목록 등 값 하나가 올 자리. 1행 1열을 돌려줘야 하고, 0행이면 NULL
위치	인라인 뷰	FROM 절. 테이블처럼 쓴다
위치	중첩 서브쿼리	WHERE·HAVING 절의 조건
반환 형태	단일행	=, <, > 같은 단일행 비교 연산자와 쓴다
반환 형태	다중행	IN, ANY(SOME), ALL, EXISTS 와 쓴다
반환 형태	다중 컬럼	(a, b) IN (SELECT x, y ...) 처럼 여러 컬럼을 한꺼번에 비교
실행 방식	비상관 / 상관	상관 서브쿼리는 바깥 쿼리의 컬럼을 참조하므로 바깥 행마다 다시 평가된다(개념상)

그림 · 서브쿼리가 놓이는 세 자리 — SELECT 목록의 스칼라 서브쿼리는 행마다 값 하나를(0행이면 NULL), FROM 의 인라인 뷰는 표 하나를, WHERE 의 중첩 서브쿼리는 조건에 쓸 값이나 집합을 돌려준다.

ANY 와 ALL

x > ANY (집합) 은 "집합의 어느 하나보다 크다", 즉 최솟값보다 크다. x > ALL (집합) 은 "모든 값보다 크다", 즉 최댓값보다 크다. 여기까지는 외우기 쉽다. 함정은 빈 집합이다. 비교할 값이 하나도 없으면 ANY 는 거짓이고, ALL 은 참이다. "모든 원소가 조건을 만족한다"는 문장은 원소가 없을 때 참이기 때문이다.

집합 연산

UNION 은 합치고 중복을 없앤다. UNION ALL 은 중복을 그대로 둔다. INTERSECT 는 양쪽에 모두 있는 것, EXCEPT(Oracle 은 MINUS)는 왼쪽에만 있는 것이다. 결과 컬럼 이름은 첫 번째 SELECT 의 것을 쓰고, ORDER BY 는 맨 마지막에 한 번만 쓴다. 집합 연산은 NULL 끼리를 같은 값으로 보고 중복을 없앤다.

예제 스키마

7장과 같은 스키마다. 집합 연산 예제는 판매(sale)의 담당 직원 번호를 쓴다. A 상품을 판 직원은 102, 104, 105, 102, 107 이고, B 상품을 판 직원은 103, 103, 104 다.

sale_id	staff_id	sale_month	region	product	amount
1	102	2026-07	서울	A	100
2	103	2026-07	서울	B	50
3	104	2026-07	부산	A	70
4	105	2026-07	부산	A	30
5	102	2026-08	서울	A	120
6	103	2026-08	서울	B	NULL
7	104	2026-08	부산	B	80
8	107	2026-08	부산	A	40

SQL과 실행 결과

단일행 서브쿼리

전체 평균 급여는 33,100 ÷ 8 = 4,137.5 다. 이보다 큰 사람은 4명이다.

SELECT name, salary
FROM staff
WHERE salary > (SELECT AVG(salary) FROM staff)
ORDER BY staff_id;

실행 결과:

name    salary
------  ------
한지수  5200
오민재  4300
이도현  4300
정우진  4800

단일행 자리에 여러 행이 오면

-- 서브쿼리가 2행을 돌려준다. 표준에서는 오류여야 하는 문장이다
SELECT name, salary
FROM staff
WHERE salary = (SELECT salary FROM staff WHERE team_id = 20)
ORDER BY staff_id;

실행 결과:

name    salary
------  ------
오민재  4300
이도현  4300

영업팀 급여는 4,300 과 3,600 두 개다. 표준 SQL 과 Oracle·SQL Server 에서는 "단일행 서브쿼리가 여러 행을 반환했다"는 오류가 나야 하는 문장이다. SQLite 는 오류를 내지 않고 첫 행의 값만 쓴다. 그래서 4,300 과 같은 두 명이 나왔다. 어느 행이 첫 행인지는 보장되지 않으므로 이 결과에 기대면 안 된다.

다중행 서브쿼리: IN

SELECT name, team_id
FROM staff
WHERE team_id IN (SELECT team_id FROM team WHERE city = '서울')
ORDER BY staff_id;

실행 결과:

name    team_id
------  -------
한지수  10
오민재  10
박서윤  10
정우진  30
윤태오  30

ANY·ALL 을 MIN·MAX 로 바꿔 읽기

SQLite 에는 ANY·ALL 연산자가 없다. 그대로 쓰면 문법 오류다.

SELECT name FROM staff WHERE salary > ANY (SELECT salary FROM staff WHERE team_id = 20);

실행 결과:

Parse error near line 1: near "SELECT": syntax error
  SELECT name FROM staff WHERE salary > ANY (SELECT salary FROM staff WHERE team
                               error here ---^

그래서 같은 뜻의 MIN·MAX 로 실행했다.

-- "> ALL (영업팀 급여)" 와 같은 뜻: 영업팀 최고 급여보다 크다
SELECT name, salary FROM staff
WHERE salary > (SELECT MAX(salary) FROM staff WHERE team_id = 20)
ORDER BY staff_id;

-- "> ANY (영업팀 급여)" 와 같은 뜻: 영업팀 최저 급여보다 크다
SELECT COUNT(*) AS gt_any FROM staff
WHERE salary > (SELECT MIN(salary) FROM staff WHERE team_id = 20);

실행 결과:

name    salary
------  ------
한지수  5200
정우진  4800
gt_any
------
6

영업팀 최고 급여 4,300 보다 큰 사람은 2명(> ALL), 최저 급여 3,600 보다 큰 사람은 6명(> ANY)이다.

빈 집합에서 ALL 과 MAX 는 다르다

-- 법무팀(40)은 직원이 없다. "> ALL (빈 집합)" 은 참이어야 한다
SELECT COUNT(*) AS via_max FROM staff s
WHERE s.salary > (SELECT MAX(x.salary) FROM staff x WHERE x.team_id = 40);

SELECT COUNT(*) AS via_not_exists FROM staff s
WHERE NOT EXISTS (SELECT 1 FROM staff x WHERE x.team_id = 40 AND x.salary >= s.salary);

실행 결과:

via_max
-------
0
via_not_exists
--------------
8

법무팀은 직원이 없다. MAX 는 빈 집합에서 NULL 을 돌려주고, salary > NULL 은 UNKNOWN 이라 0명이다. 그러나 > ALL (빈 집합) 은 참이어야 하므로 8명 모두가 답이다. NOT EXISTS 로 쓴 두 번째 쿼리가 ALL 의 정확한 뜻을 재현한다. ALL 을 MAX 로 바꿔 쓸 때는 빈 집합일 때 결과가 뒤집힌다.

EXISTS

SELECT t.team_name
FROM team t
WHERE EXISTS (SELECT 1 FROM staff s WHERE s.team_id = t.team_id AND s.bonus > 0)
ORDER BY t.team_id;

실행 결과:

team_name
---------
개발
영업

EXISTS 는 서브쿼리가 행을 하나라도 돌려주는지만 본다. SELECT 목록(1)은 결과에 영향이 없다.

NOT IN 과 NULL

SELECT COUNT(*) AS not_in_rows
FROM team WHERE team_id NOT IN (SELECT team_id FROM staff);

SELECT team_name AS not_exists_result
FROM team t WHERE NOT EXISTS (SELECT 1 FROM staff s WHERE s.team_id = t.team_id);

SELECT team_name AS not_in_fixed
FROM team WHERE team_id NOT IN (SELECT team_id FROM staff WHERE team_id IS NOT NULL);

실행 결과:

not_in_rows
-----------
0
not_exists_result
-----------------
법무
not_in_fixed
------------
법무

직원 테이블의 team_id 에 강예린의 NULL 이 있다. 40 NOT IN (10, 10, 10, 20, 20, 30, NULL, 30) 은 마지막에 40 <> NULL 이 UNKNOWN 이라 전체가 UNKNOWN 이 된다. 그래서 0행이다. NOT EXISTS 는 NULL 과 비교할 일이 없어 법무팀을 찾는다. NOT IN 을 꼭 쓰려면 서브쿼리에서 NULL 을 걸러 낸다.

상관 서브쿼리

SELECT s.name, s.team_id, s.salary
FROM staff s
WHERE s.salary > (SELECT AVG(x.salary) FROM staff x WHERE x.team_id = s.team_id)
ORDER BY s.staff_id;

실행 결과:

name    team_id  salary
------  -------  ------
한지수  10       5200
이도현  20       4300
정우진  30       4800

직원마다 "자기 팀 평균"과 비교했다. 팀이 NULL 인 강예린은 x.team_id = NULL 이 되어 서브쿼리가 NULL 을 돌려주고, 비교에서 빠진다.

그림 · 상관 서브쿼리는 바깥 행마다 다시 계산된다(개념상) — 바깥 staff 행의 team_id 를 안쪽 AVG 가 받아 그 팀 평균과 비교한다. 팀 평균보다 급여가 높은 사람은 한지수·이도현·정우진이고, 팀이 NULL 인 강예린은 평균이 NULL 이라 비교가 UNKNOWN 이 되어 빠진다(10-correlated).

스칼라 서브쿼리와 인라인 뷰

SELECT s.name,
       (SELECT t.team_name FROM team t WHERE t.team_id = s.team_id) AS team_name
FROM staff s
WHERE s.staff_id IN (101, 104, 107)
ORDER BY s.staff_id;

실행 결과:

name    team_name
------  ---------
한지수  개발
이도현  영업
강예린  NULL

스칼라 서브쿼리는 짝이 없으면 NULL 을 돌려준다. 그래서 외부 조인처럼 행이 빠지지 않는다.

SELECT s.team_id, s.name, s.salary
FROM staff s
JOIN (SELECT team_id, MAX(salary) AS top FROM staff GROUP BY team_id) m
  ON m.team_id = s.team_id AND m.top = s.salary
ORDER BY s.team_id;

실행 결과:

team_id  name    salary
-------  ------  ------
10       한지수  5200
20       이도현  4300
30       정우진  4800

인라인 뷰에서 팀별 최고 급여를 구한 뒤 조인했다. m.team_id = s.team_id 에서 NULL 팀은 짝을 못 찾아 빠졌다.

집합 연산

A = {102, 104, 105, 102, 107}, B = {103, 103, 104} 로 먼저 계산한다. UNION 은 중복을 없애 {102, 103, 104, 105, 107} 로 5. UNION ALL 은 5 + 3 = 8. INTERSECT 는 {104} 로 1. EXCEPT(A − B)는 {102, 105, 107} 로 3.

SELECT (SELECT COUNT(*) FROM (SELECT staff_id FROM sale WHERE product = 'A'
                              UNION     SELECT staff_id FROM sale WHERE product = 'B')) AS union_n,
       (SELECT COUNT(*) FROM (SELECT staff_id FROM sale WHERE product = 'A'
                              UNION ALL SELECT staff_id FROM sale WHERE product = 'B')) AS union_all_n,
       (SELECT COUNT(*) FROM (SELECT staff_id FROM sale WHERE product = 'A'
                              INTERSECT SELECT staff_id FROM sale WHERE product = 'B')) AS intersect_n,
       (SELECT COUNT(*) FROM (SELECT staff_id FROM sale WHERE product = 'A'
                              EXCEPT    SELECT staff_id FROM sale WHERE product = 'B')) AS except_n;

실행 결과:

union_n  union_all_n  intersect_n  except_n
-------  -----------  -----------  --------
5        8            1            3

SELECT staff_id FROM sale WHERE product = 'A'
EXCEPT
SELECT staff_id FROM sale WHERE product = 'B'
ORDER BY staff_id;

실행 결과:

staff_id
--------
102
105
107

표 · 상품 A 판매자와 B 판매자에 집합 연산을 한 결과 (10-set-ops 실행 결과)

연산	뜻	실제 행 수
UNION	A 또는 B 를 판 직원, 중복 제거	5
UNION ALL	A 판매 행 + B 판매 행, 중복 그대로	8
INTERSECT	A 와 B 를 모두 판 직원	1
EXCEPT (Oracle MINUS)	A 만 판 직원	3

결과 컬럼 이름은 첫 SELECT 에서

SELECT name AS who, 'staff' AS kind FROM staff WHERE team_id = 30
UNION ALL
SELECT team_name, 'team' FROM team WHERE team_id = 30
ORDER BY who;

실행 결과:

who     kind
------  -----
디자인  team
윤태오  staff
정우진  staff

두 번째 SELECT 의 team_name 이 아니라 첫 SELECT 의 별칭 who 가 결과 이름이 됐고, ORDER BY 도 그 이름을 쓴다.

뷰

CREATE VIEW v_team_pay AS
SELECT team_id, COUNT(*) AS n, SUM(salary) AS total
FROM staff GROUP BY team_id;

SELECT team_id, n, total FROM v_team_pay WHERE total > 8000 ORDER BY team_id;

실행 결과:

team_id  n  total
-------  -  -----
10       3  13400
30       2  8700

뷰는 저장된 SELECT 문이다. 이름 붙인 인라인 뷰라고 생각하면 된다. 뷰는 데이터를 따로 저장하지 않는다.

표준과 구현의 차이

아래 Oracle·SQL Server 동작은 비교 설명이며 실행하지 않았다.

항목	SQLite(실행)	Oracle	SQL Server
단일행 자리에 여러 행	첫 행 값 사용(오류 없음)	오류	오류
ANY / SOME / ALL	없음	지원	지원
차집합	EXCEPT	MINUS(21c 부터 EXCEPT 도)	EXCEPT
INTERSECT ALL / EXCEPT ALL	없음	21c 부터 지원	없음
인라인 뷰 별칭	생략 가능	생략 가능	반드시 필요
다중 컬럼 IN	지원	지원	지원하지 않음(EXISTS 로 우회)

[구현 차이] 시험에서 "실행 시 오류가 발생하는 SQL"을 고르라는 문제는 대개 단일행 비교 연산자(=) 뒤의 서브쿼리가 여러 행을 돌려주는 경우다. SQLite 로 연습할 때는 오류가 나지 않으므로, 서브쿼리를 따로 실행해 행 수를 직접 확인하는 습관을 들인다.

시험에서 헷갈리는 지점

판단 1. "NOT IN 과 NOT EXISTS 는 언제나 같은 결과를 낸다"

틀렸다. 서브쿼리 결과에 NULL 이 있으면 NOT IN 은 0행, NOT EXISTS 는 정상 결과를 낸다. 위 법무팀 예제가 그 경우다.

판단 2. "UNION 은 중복을 없애므로 결과가 항상 정렬되어 나온다"

틀렸다. 중복 제거를 위해 내부적으로 정렬하는 제품이 많아 정렬된 것처럼 보일 뿐, 표준은 순서를 보장하지 않는다. 순서가 필요하면 마지막에 ORDER BY 를 쓴다.

판단 3. "스칼라 서브쿼리가 0행을 돌려주면 바깥 쿼리의 그 행이 결과에서 빠진다"

틀렸다. 값이 NULL 이 될 뿐 행은 남는다. 위 예제의 강예린이 그렇다.

연습 문제

1. team_id NOT IN (SELECT team_id FROM team WHERE city = '서울') 을 만족하는 직원 수는?
2. SELECT COUNT(*) FROM team WHERE team_id IN (SELECT team_id FROM staff) 의 결과는? 서브쿼리에 NULL 이 있는 것이 IN 에 영향을 주는가?
3. 서울 지역 판매 담당자와 A 상품 판매 담당자의 교집합을 구하라.
4. SELECT product FROM sale UNION SELECT region FROM sale 의 결과 행 수는?
5. 판매 기록이 한 건도 없는 직원의 이름을 직원 번호 순으로 구하라.

정답과 해설

1. 2. 서울 팀은 10, 30 이다. 서브쿼리 결과에는 NULL 이 없으므로 NOT IN 이 정상 동작해 영업팀 2명이 남는다. 팀이 NULL 인 강예린은 NULL NOT IN (10, 30) 이 UNKNOWN 이라 빠진다.

SELECT COUNT(*) AS n FROM staff
WHERE team_id NOT IN (SELECT team_id FROM team WHERE city = '서울');

실행 결과:

n
-
2

2. 3. IN 은 OR 로 풀리므로 짝이 하나라도 있으면 참이다. NULL 이 있어도 참인 행은 참으로 남는다. 영향을 받는 것은 짝이 없는 법무팀뿐이고, 어차피 결과에 없다.

SELECT COUNT(*) AS n FROM team WHERE team_id IN (SELECT team_id FROM staff);

실행 결과:

n
-
3

3. 서울 담당자는 {102, 103}, A 상품 담당자는 {102, 104, 105, 107}. 교집합은 102.

SELECT staff_id FROM sale WHERE region = '서울'
INTERSECT
SELECT staff_id FROM sale WHERE product = 'A';

실행 결과:

staff_id
--------
102

4. 4. A, B, 서울, 부산. 두 SELECT 의 컬럼 이름이 달라도 타입과 개수만 맞으면 합칠 수 있다.

SELECT COUNT(*) AS n FROM (SELECT product FROM sale UNION SELECT region FROM sale);

실행 결과:

n
-
4

5. 한지수, 정우진, 윤태오. NOT EXISTS 를 쓰면 판매 테이블의 NULL 여부를 신경 쓸 필요가 없다.

SELECT name FROM staff s
WHERE NOT EXISTS (SELECT 1 FROM sale x WHERE x.staff_id = s.staff_id)
ORDER BY staff_id;

실행 결과:

name
------
한지수
정우진
윤태오

다음 장에서는 소계를 만드는 그룹 함수와, 행을 줄이지 않고 집계하는 윈도 함수를 다룬다.

참고 자료

• SQLite 서브쿼리 식
• SQLite 복합 SELECT