표준 조인 결과 예측 - INNER OUTER NATURAL USING CROSS 비등가 셀프 조인 (SQLD 기본 9장)
이 장에서 배우는 것
8장에서 한 테이블 안의 집계를 예측했다. 이 장은 두 테이블 이상을 잇는 조인이다. 조인 종류별 문법은 『SQL 실전 기초』 7단원에 정리되어 있다. 여기서는 결과가 몇 행인가를 먼저 계산하는 습관을 들인다. 조인 결과의 행 수를 맞히면 조건을 잘못 쓴 쿼리를 실행 전에 알아챌 수 있다.
- INNER, LEFT·RIGHT·FULL OUTER, CROSS 조인의 행 수를 데이터만 보고 계산한다.
- 외부 조인에서 조건을 ON 에 쓸 때와 WHERE 에 쓸 때의 차이, NATURAL JOIN 과 USING 의 함정을 확인한다.
- 비등가 조인, 셀프 조인, 조인 뒤 집계가 부풀어 오르는 문제를 실행 결과로 확인한다.
핵심 개념
팀별 급여 합계와 매출 합계를 한 쿼리로 뽑은 보고서가 있었다. 급여 합계가 인사 시스템보다 20% 넘게 컸다. 쿼리는 문법상 아무 문제가 없었다. 판매 건수만큼 직원 행이 복제된 뒤에 급여를 더했기 때문이다. 조인은 행을 곱하는 연산이라는 사실을 잊으면 생기는 사고다.
조인 결과 행 수 계산법
| 조인 | 결과 행 수 |
|---|---|
| CROSS JOIN | 왼쪽 행 수 × 오른쪽 행 수 |
| INNER JOIN | 조건을 만족하는 짝의 수. 1:N 이면 N 쪽 중 짝이 있는 행 수 |
| LEFT OUTER JOIN | INNER 결과 + 짝이 없는 왼쪽 행 수 |
| RIGHT OUTER JOIN | INNER 결과 + 짝이 없는 오른쪽 행 수 |
| FULL OUTER JOIN | INNER 결과 + 짝이 없는 왼쪽 행 + 짝이 없는 오른쪽 행 |
외부 조인에서 짝이 없는 행은 반대쪽 컬럼이 모두 NULL 로 채워진다.
그림 · 조인은 행을 짝짓는다 — staff 8행과 team 4행을 team_id 로 짝지으면 짝이 있는 7쌍이 INNER 결과다. 팀이 없는 강예린을 더하면 LEFT 8행, 직원이 없는 법무를 더하면 RIGHT 8행, 둘 다 더하면 FULL 9행이다(09-counts).
표준 조인 문법의 종류
JOIN ... ON 조건: 조건을 직접 쓴다. 등가·비등가 모두 가능하다.JOIN ... USING (컬럼): 이름이 같은 컬럼을 지정해 등가 조인한다. 결과에 그 컬럼은 한 번만 나온다.NATURAL JOIN: 이름이 같은 모든 컬럼으로 등가 조인한다. 조건을 쓰지 않는다.CROSS JOIN: 조건 없이 모든 조합을 만든다.
시험에는 표준 문법과 Oracle 의 옛 외부 조인 표기 (+) 를 서로 바꿔 쓰는 문제가 자주 나온다. 이 표기는 "표준과 구현의 차이"에서 다룬다.
예제 스키마
7장과 같은 스키마다. 직원 8명 중 강예린은 팀이 없고, 팀 4개 중 법무팀은 직원이 없다. 이 두 구멍이 외부 조인의 행 수를 결정한다. 급여 등급(grade)은 비등가 조인에 쓴다.
| grade | low | high |
|---|---|---|
| 1 | 0 | 3499 |
| 2 | 3500 | 4199 |
| 3 | 4200 | 4999 |
| 4 | 5000 | 99999 |
SQL과 실행 결과
INNER 와 LEFT
SELECT s.name, t.team_name
FROM staff s
INNER JOIN team t ON t.team_id = s.team_id
ORDER BY s.staff_id;실행 결과:
name team_name
------ ---------
한지수 개발
오민재 개발
박서윤 개발
이도현 영업
최하늘 영업
정우진 디자인
윤태오 디자인팀이 있는 7명만 나온다. 강예린은 짝이 없어 빠졌다.
SELECT s.name, t.team_name
FROM staff s
LEFT OUTER JOIN team t ON t.team_id = s.team_id
ORDER BY s.staff_id;실행 결과:
name team_name
------ ---------
한지수 개발
오민재 개발
박서윤 개발
이도현 영업
최하늘 영업
정우진 디자인
강예린 NULL
윤태오 디자인조인 종류별 행 수
데이터만 보고 먼저 계산한다. INNER 7. LEFT 는 짝 없는 직원 1명을 더해 8. RIGHT 는 짝 없는 팀 1개(법무)를 더해 8. FULL 은 둘 다 더해 9. CROSS 는 8 × 4 = 32.
SELECT (SELECT COUNT(*) FROM staff s JOIN team t ON t.team_id = s.team_id) AS inner_n,
(SELECT COUNT(*) FROM staff s LEFT JOIN team t ON t.team_id = s.team_id) AS left_n,
(SELECT COUNT(*) FROM staff s RIGHT JOIN team t ON t.team_id = s.team_id) AS right_n,
(SELECT COUNT(*) FROM staff s FULL OUTER JOIN team t ON t.team_id = s.team_id) AS full_n,
(SELECT COUNT(*) FROM staff CROSS JOIN team) AS cross_n;실행 결과:
inner_n left_n right_n full_n cross_n
------- ------ ------- ------ -------
7 8 8 9 32표 · 조인 결과 행 수를 계산해 보고 실제 값과 맞춰 보기 (09-counts 실행 결과)
| 조인 | 계산 | 실제 행 수 |
|---|---|---|
| INNER JOIN | 팀이 있는 직원 7명의 짝 | 7 |
| LEFT OUTER JOIN | 7 + 팀이 없는 직원 1 (강예린) | 8 |
| RIGHT OUTER JOIN | 7 + 직원이 없는 팀 1 (법무) | 8 |
| FULL OUTER JOIN | 7 + 1 + 1 | 9 |
| CROSS JOIN | 직원 8 × 팀 4 | 32 |
FULL OUTER 로 양쪽 고아 찾기
SELECT s.name, t.team_name
FROM staff s
FULL OUTER JOIN team t ON t.team_id = s.team_id
WHERE s.staff_id IS NULL OR t.team_id IS NULL;실행 결과:
name team_name
------ ---------
강예린 NULL
NULL 법무외부 조인에서 ON 과 WHERE
"서울에 있는 팀 이름을 붙여 달라"는 요청을 두 가지로 쓴다.
SELECT s.name, t.team_name
FROM staff s
LEFT JOIN team t ON t.team_id = s.team_id AND t.city = '서울'
ORDER BY s.staff_id;
SELECT s.name, t.team_name
FROM staff s
LEFT JOIN team t ON t.team_id = s.team_id
WHERE t.city = '서울'
ORDER BY s.staff_id;실행 결과:
name team_name
------ ---------
한지수 개발
오민재 개발
박서윤 개발
이도현 NULL
최하늘 NULL
정우진 디자인
강예린 NULL
윤태오 디자인
name team_name
------ ---------
한지수 개발
오민재 개발
박서윤 개발
정우진 디자인
윤태오 디자인ON 에 쓴 조건은 짝을 찾는 조건이다. 서울 팀과만 짝을 지으므로, 부산 팀 직원은 짝이 없는 왼쪽 행이 되어 팀 이름이 NULL 로 남는다. 8행이 그대로 유지된다. WHERE 에 쓴 조건은 조인이 끝난 결과를 거르는 조건이다. NULL 로 채워진 행은 t.city = '서울' 이 UNKNOWN 이라 사라지고, 결과적으로 INNER 조인과 같아진다.
그림 · 외부 조인에서 ON 조건과 WHERE 조건 — t.city = '서울' 을 ON 에 두면 짝을 고르는 조건이라 직원 8명이 모두 남고 영업팀 직원의 팀 이름만 NULL 이 된다. WHERE 에 두면 조인이 끝난 뒤 NULL 행을 버려 5행, 사실상 내부 조인이 된다(09-on-vs-where).
USING 과 NATURAL JOIN
SELECT team_id, name, team_name
FROM staff JOIN team USING (team_id)
WHERE team_id = 20
ORDER BY name;실행 결과:
team_id name team_name
------- ------ ---------
20 이도현 영업
20 최하늘 영업SELECT team_id, name, team_name
FROM staff NATURAL JOIN team
WHERE team_id = 20
ORDER BY name;실행 결과:
team_id name team_name
------- ------ ---------
20 이도현 영업
20 최하늘 영업지금은 두 결과가 같다. 두 테이블에 이름이 같은 컬럼이 team_id 하나뿐이기 때문이다. 누군가 컬럼 이름을 바꾸면 이야기가 달라진다.
-- 누군가 team 을 감싼 뷰에서 team_name 을 name 으로 바꿔 두었다
CREATE VIEW team_v AS SELECT team_id, team_name AS name, city FROM team;
SELECT COUNT(*) AS natural_rows FROM staff NATURAL JOIN team_v;
SELECT COUNT(*) AS using_rows FROM staff JOIN team_v USING (team_id);실행 결과:
natural_rows
------------
0
using_rows
----------
7뷰에 name 컬럼이 생기자 NATURAL JOIN 은 team_id 와 name 두 컬럼으로 조인했다. 직원 이름과 팀 이름이 같을 리 없으니 0행이다. 쿼리는 한 글자도 바뀌지 않았는데 결과가 사라졌다. NATURAL JOIN 은 스키마 변경에 조용히 깨진다. 실무에서는 USING 이나 ON 을 쓴다.
비등가 조인
SELECT s.name, s.salary, g.grade
FROM staff s
JOIN grade g ON s.salary BETWEEN g.low AND g.high
ORDER BY s.staff_id;실행 결과:
name salary grade
------ ------ -----
한지수 5200 4
오민재 4300 3
박서윤 3900 2
이도현 4300 3
최하늘 3600 2
정우진 4800 3
강예린 3100 1
윤태오 3900 2조건이 = 가 아니라 BETWEEN 이다. 등급 구간이 겹치지 않으므로 직원마다 정확히 한 행이 나온다. 구간이 겹치거나 빈틈이 있으면 행이 늘거나 사라진다.
셀프 조인
SELECT e.name AS staff, m.name AS manager
FROM staff e
LEFT JOIN staff m ON m.staff_id = e.manager_id
ORDER BY e.staff_id;실행 결과:
staff manager
------ -------
한지수 NULL
오민재 한지수
박서윤 오민재
이도현 한지수
최하늘 이도현
정우진 한지수
강예린 이도현
윤태오 정우진같은 테이블에 별칭 두 개를 붙여 "직원"과 "관리자" 역할로 읽었다. 대표 한지수는 관리자가 없으므로 LEFT 조인이어야 결과에 남는다.
조인 뒤 집계가 부풀어 오르는 문제
-- 틀린 방법: 조인 뒤에 급여를 더한다
SELECT t.team_name, SUM(s.salary) AS salary_sum, SUM(x.amount) AS sale_sum
FROM team t
JOIN staff s ON s.team_id = t.team_id
JOIN sale x ON x.staff_id = s.staff_id
GROUP BY t.team_name
ORDER BY t.team_name;
-- 맞는 방법: 매출을 직원별로 먼저 줄인 뒤 조인한다
SELECT t.team_name, SUM(s.salary) AS salary_sum, SUM(x.sale_sum) AS sale_sum
FROM team t
JOIN staff s ON s.team_id = t.team_id
LEFT JOIN (SELECT staff_id, SUM(amount) AS sale_sum FROM sale GROUP BY staff_id) x
ON x.staff_id = s.staff_id
GROUP BY t.team_name
ORDER BY t.team_name;실행 결과:
team_name salary_sum sale_sum
--------- ---------- --------
개발 16400 270
영업 12200 180
team_name salary_sum sale_sum
--------- ---------- --------
개발 13400 270
디자인 8700 NULL
영업 7900 180첫 쿼리의 개발팀 급여 합계는 16,400 이다. 실제 개발팀 급여 합계는 13,400(8장)이다. 오민재와 박서윤이 판매를 두 건씩 해서 각자 두 번 더해졌고, 판매가 없는 한지수는 INNER 조인에서 빠졌다. 두 번째 쿼리처럼 N 쪽을 먼저 집계해 1:1 로 만든 뒤 조인하면 급여가 정확해진다. 판매가 없는 디자인팀도 LEFT 조인으로 남았다.
표준과 구현의 차이
아래 Oracle·SQL Server 동작은 비교 설명이며 실행하지 않았다.
| 항목 | SQLite(실행) | Oracle | SQL Server |
|---|---|---|---|
| RIGHT·FULL OUTER JOIN | 3.39.0 부터 지원 | 지원 | 지원 |
| 옛 외부 조인 표기 | 없음 | WHERE s.team_id = t.team_id(+) | 옛 *= 표기는 폐지됨 |
| USING 컬럼에 테이블 별칭 붙이기 | 허용 | 오류(USING 컬럼은 별칭 없이 써야 함) | USING 자체를 지원하지 않음 |
| NATURAL JOIN | 지원 | 지원 | 지원하지 않음 |
[구현 차이] Oracle 의
(+)는 NULL 로 채워질 쪽, 즉 짝이 없어도 되는 쪽 컬럼에 붙인다.s.team_id = t.team_id(+)는 직원을 모두 남기는 LEFT 조인(staff s LEFT JOIN team t)이다. 방향을 반대로 외우기 쉬우므로 "(+) 가 붙은 쪽이 NULL 이 될 수 있다"로 기억한다. 또 ON 대신 WHERE 에t.city(+) = '서울'처럼 모든 조건에 (+) 를 붙여야 위 ON 예제와 같은 결과가 된다.
USING 컬럼에 별칭을 붙인 아래 쿼리는 SQLite 에서는 실행되지만, Oracle 에서는 같은 문장이 오류다. 시험에서 "오류가 나는 문장"을 고르라고 하면 Oracle 기준으로 판단한다.
SELECT s.team_id, s.name FROM staff s JOIN team t USING (team_id) WHERE t.team_id = 30 ORDER BY s.name;실행 결과:
team_id name
------- ------
30 윤태오
30 정우진시험에서 헷갈리는 지점
판단 1. "LEFT OUTER JOIN 의 결과 행 수는 항상 왼쪽 테이블의 행 수와 같다"
틀렸다. 왼쪽 행 하나에 짝이 여럿이면 그만큼 늘어난다. 팀 LEFT JOIN 직원은 팀이 4개지만 결과는 8행이다. "왼쪽 행 수 이상"이 맞다.
판단 2. "외부 조인에서 안쪽 테이블 조건을 WHERE 에 쓰면 내부 조인과 같은 결과가 될 수 있다"
맞다. 위 ON·WHERE 예제가 그 경우다. NULL 로 채운 행이 WHERE 조건에서 UNKNOWN 이 되어 사라진다. 단 WHERE t.team_id IS NULL 처럼 NULL 을 찾는 조건은 예외다. 짝 없는 행만 남긴다.
판단 3. "NATURAL JOIN 에서 두 테이블에 이름이 같은 컬럼이 없으면 오류가 난다"
틀렸다(적어도 SQLite 와 표준의 정의에서). 공통 컬럼이 없으면 조인 조건이 없는 것과 같아져 CROSS JOIN 처럼 동작한다. 등급 4행과 팀 4행이 16행이 됐다. 오류가 나지 않고 행이 폭발하므로 더 위험하다.
-- grade 와 team 에는 이름이 같은 컬럼이 없다
SELECT COUNT(*) AS natural_rows FROM grade NATURAL JOIN team;실행 결과:
natural_rows
------------
16연습 문제
team t LEFT JOIN staff s ON s.team_id = t.team_id의 결과 행 수는?staff s LEFT JOIN team t ON ... WHERE t.team_id IS NULL의 결과 행 수와, 이 쿼리가 찾는 대상을 쓰라.- 직원과 관리자를 INNER 셀프 조인하면 몇 행인가?
- 급여 등급별 직원 수를 등급이 비어 있어도 나오도록 구하라. 각 등급의 인원을 예측하라.
- 직원 FULL OUTER JOIN 팀에서 어느 한쪽이 NULL 인 행만 세면 몇인가?
정답과 해설
1. 8. 직원이 있는 팀 3개에서 7행, 직원이 없는 법무팀 1행.
SELECT COUNT(*) AS n FROM team t LEFT JOIN staff s ON s.team_id = t.team_id;실행 결과:
n
-
82. 1. 팀이 배정되지 않은 직원(강예린)을 찾는 쿼리다. 외부 조인 + IS NULL 은 "짝이 없는 행"을 찾는 전형적인 방법이다.
SELECT COUNT(*) AS n FROM staff s LEFT JOIN team t ON s.team_id = t.team_id WHERE t.team_id IS NULL;실행 결과:
n
-
13. 7. 관리자가 없는 대표 1명이 빠진다.
SELECT COUNT(*) AS n FROM staff e JOIN staff m ON m.staff_id = e.manager_id;실행 결과:
n
-
74. 1등급 1명(3,100), 2등급 3명(3,900 두 명과 3,600), 3등급 3명(4,300 두 명과 4,800), 4등급 1명(5,200). 등급을 기준으로 LEFT 조인하고 COUNT(s.staff_id) 로 센다.
SELECT g.grade, COUNT(s.staff_id) AS n
FROM grade g LEFT JOIN staff s ON s.salary BETWEEN g.low AND g.high
GROUP BY g.grade ORDER BY g.grade;실행 결과:
grade n
----- -
1 1
2 3
3 3
4 15. 2. 팀 없는 직원 1행과 직원 없는 팀 1행이다.
SELECT COUNT(*) AS n
FROM staff s FULL OUTER JOIN team t ON s.team_id = t.team_id
WHERE s.staff_id IS NULL OR t.team_id IS NULL;실행 결과:
n
-
2다음 장에서는 쿼리 안에 쿼리를 넣는 서브쿼리와, 결과 집합끼리 합치고 빼는 집합 연산을 다룬다.