SQLD · 기본
데이터 모델링과 SQL 기본
서브쿼리와 집합 연산 - 단일행 다중행 상관 서브쿼리 EXISTS UNION EXCEPT (SQLD 기본 10장)
서브쿼리를 위치와 반환 행 수로 나누고, NOT IN 과 NULL, 빈 집합에 대한 ALL, 상관 서브쿼리, UNION·INTERSECT·EXCEPT 의 행 수를 예측한 뒤 실행해 확인한다.
개발자 · 원고 갱신
이 장에서 배우는 것
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
윤태오 30ANY·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 3SELECT 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 이 될 뿐 행은 남는다. 위 예제의 강예린이 그렇다.
연습 문제
team_id NOT IN (SELECT team_id FROM team WHERE city = '서울')을 만족하는 직원 수는?SELECT COUNT(*) FROM team WHERE team_id IN (SELECT team_id FROM staff)의 결과는? 서브쿼리에 NULL 이 있는 것이 IN 에 영향을 주는가?- 서울 지역 판매 담당자와 A 상품 판매 담당자의 교집합을 구하라.
SELECT product FROM sale UNION SELECT region FROM sale의 결과 행 수는?- 판매 기록이 한 건도 없는 직원의 이름을 직원 번호 순으로 구하라.
정답과 해설
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
-
22. 3. IN 은 OR 로 풀리므로 짝이 하나라도 있으면 참이다. NULL 이 있어도 참인 행은 참으로 남는다. 영향을 받는 것은 짝이 없는 법무팀뿐이고, 어차피 결과에 없다.
SELECT COUNT(*) AS n FROM team WHERE team_id IN (SELECT team_id FROM staff);실행 결과:
n
-
33. 서울 담당자는 {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
--------
1024. 4. A, B, 서울, 부산. 두 SELECT 의 컬럼 이름이 달라도 타입과 개수만 맞으면 합칠 수 있다.
SELECT COUNT(*) AS n FROM (SELECT product FROM sale UNION SELECT region FROM sale);실행 결과:
n
-
45. 한지수, 정우진, 윤태오. 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
------
한지수
정우진
윤태오다음 장에서는 소계를 만드는 그룹 함수와, 행을 줄이지 않고 집계하는 윈도 함수를 다룬다.
READER FEEDBACK
질문·오탈자·의견
내용에 관한 질문이나 오탈자, 더 나은 설명을 위한 의견을 남겨 주세요. 이 댓글은 원래 게시글과 같은 자리에 쌓입니다.
댓글 0
아직 댓글이 없습니다. 첫 댓글을 남겨 보세요.