SQL 완전 정리 ④ JOIN
정규화로 쪼갠 테이블을 다시 붙여 보는 연산이 JOIN 이다. 종류가 많아 보이지만 출발점은 하나다 — 카티션 곱(모든 행 × 모든 행)을 만든 뒤 조건에 맞는 행만 남긴다. INNER 는 그대로, OUTER 는 "짝을 못 찾은 행을 NULL 로 채워 되살린다"는 한 단계가 더 붙을 뿐이다. 이 그림을 머리에 두면 결과 행 수 계산, ON 과 WHERE 의 차이, 조인 뒤 집계가 부풀어 오르는 이유가 모두 같은 원리로 풀린다. 본문의 쿼리·결과 표는 SQLite 와 PostgreSQL 에서 실제로 실행해 맞췄다(Oracle
(+)문법은 대응만 소개).
0. 이 편에서 쓰는 테이블
부서
| dno | dname | loc |
|---|---|---|
| 10 | 개발 | 서울 |
| 20 | 영업 | 부산 |
| 30 | 인사 | 서울 |
| 40 | 기획 | NULL |
사원
| eno | ename | dno | job | sal | mgr | hiredate | bonus |
|---|---|---|---|---|---|---|---|
| 1 | 김하늘 | 10 | 팀장 | 500 | NULL | 2019-03-01 | NULL |
| 2 | 이도윤 | 10 | 대리 | 350 | 1 | 2021-07-15 | 30 |
| 3 | 박서준 | 10 | 사원 | 280 | 2 | 2023-01-02 | NULL |
| 4 | 최지우 | 20 | 팀장 | 450 | NULL | 2018-11-20 | 50 |
| 5 | 정민재 | 20 | 사원 | 300 | 4 | 2022-05-09 | 20 |
| 6 | 한유나 | 20 | 사원 | 300 | 4 | 2024-02-01 | NULL |
| 7 | 오세린 | NULL | 사원 | 250 | NULL | 2025-01-06 | NULL |
프로젝트
| pno | pname | dno |
|---|---|---|
| 100 | 결제 | 10 |
| 200 | 광고 | 20 |
| 300 | 신규 | NULL |
참여
| eno | pno | hours |
|---|---|---|
| 1 | 100 | 20 |
| 2 | 100 | 40 |
| 3 | 100 | 30 |
| 2 | 200 | 10 |
| 5 | 200 | 25 |
급여등급 (이 편에서 추가 — 비등가 조인용)
| grade | losal | hisal |
|---|---|---|
| 1 | 0 | 299 |
| 2 | 300 | 399 |
| 3 | 400 | 599 |
짝이 안 맞는 데이터가 일부러 들어 있다. 오세린은 부서가 없고, 인사(30)·기획(40) 은 사원이 없고, 신규(300) 프로젝트는 주관 부서가 없고, 최지우·한유나·오세린 은 어떤 프로젝트에도 참여하지 않는다. OUTER JOIN 의 결과는 전부 이 "짝 없는 행"에서 갈린다.
1. 카티션 곱 — CROSS JOIN
1.1 문법 골격
SELECT … FROM A CROSS JOIN B; -- ANSI 표기
SELECT … FROM A, B; -- 전통 표기 (조건 없이 쉼표)
A 의 모든 행과 B 의 모든 행을 하나씩 짝지은 결과. 결과 행 수 = |A| × |B|, 열 수 = A 열 수 + B 열 수. 관계대수의 교차곱(×) 이다.
SELECT COUNT(*) FROM 사원 CROSS JOIN 부서; -- 28
SELECT COUNT(*) FROM 사원, 부서; -- 28
7 × 4 = 28. 작은 예로 펼치면:
SELECT d.dno, d.dname, p.pname
FROM 부서 d CROSS JOIN 프로젝트 p
WHERE d.dno IN (10, 20)
ORDER BY d.dno, p.pno;
| dno | dname | pname |
|---|---|---|
| 10 | 개발 | 결제 |
| 10 | 개발 | 광고 |
| 10 | 개발 | 신규 |
| 20 | 영업 | 결제 |
| 20 | 영업 | 광고 |
| 20 | 영업 | 신규 |
부서 2행 × 프로젝트 3행 = 6행. "부서마다 모든 프로젝트를 한 번씩" 붙인다.
쓰임 — 실수로 조인 조건을 빠뜨리면 생기는 결과이기도 하지만(행 폭발), 의도적으로도 쓴다: 모든 (날짜 × 매장) 조합 틀 만들기, 테스트 데이터 생성 등.
2. 조인의 원리 — "곱한 뒤 거른다"
모든 조인은 논리적으로 ① 카티션 곱 → ② 조인 조건으로 필터 로 이해할 수 있다 (관계대수로 θ-조인 = σ_θ(A × B)). 실제 엔진은 28행을 다 만들지 않지만(§12), 결과는 이 방식과 같다.
김하늘(dno 10)과 최지우(dno 20) 두 명만 놓고 곱을 펼쳐 보자.
SELECT e.ename, e.dno, d.dno, d.dname
FROM 사원 e CROSS JOIN 부서 d
WHERE e.eno IN (1, 4)
ORDER BY e.eno, d.dno;
| e.ename | e.dno | d.dno | d.dname | e.dno = d.dno ? |
|---|---|---|---|---|
| 김하늘 | 10 | 10 | 개발 | ✓ |
| 김하늘 | 10 | 20 | 영업 | ✗ |
| 김하늘 | 10 | 30 | 인사 | ✗ |
| 김하늘 | 10 | 40 | 기획 | ✗ |
| 최지우 | 20 | 10 | 개발 | ✗ |
| 최지우 | 20 | 20 | 영업 | ✓ |
| 최지우 | 20 | 30 | 인사 | ✗ |
| 최지우 | 20 | 40 | 기획 | ✗ |
조건 e.dno = d.dno 를 통과한 ✓ 행 두 개만 남은 것이 INNER JOIN 의 결과다. 사원 7명 전체로 하면
28행 중 6행이 남는다 — 오세린은 dno 가 NULL 이라 NULL = 10 이 UNKNOWN 이 되어 어떤 부서와도 ✓가 안 된다.
3. INNER JOIN
3.1 문법 골격
SELECT …
FROM A [INNER] JOIN B ON 조인조건 -- 조건을 직접 쓴다
FROM A [INNER] JOIN B USING (공통열 [, …]) -- 이름이 같은 열로 등가 조인
FROM A NATURAL [INNER] JOIN B -- 이름이 같은 열 "전부"로 등가 조인
FROM A, B WHERE 조인조건 -- 전통(세타) 표기
INNER 는 생략 가능하다 — 그냥 JOIN 이면 INNER JOIN.
SELECT e.ename, d.dname
FROM 사원 e INNER JOIN 부서 d ON e.dno = d.dno
ORDER BY e.eno;
| ename | dname |
|---|---|
| 김하늘 | 개발 |
| 이도윤 | 개발 |
| 박서준 | 개발 |
| 최지우 | 영업 |
| 정민재 | 영업 |
| 한유나 | 영업 |
- 양쪽 모두 짝이 있는 행만 남는다. 오세린(부서 없음), 인사·기획(사원 없음)은 사라진다.
- 전통 표기
FROM 사원 e, 부서 d WHERE e.dno = d.dno도 결과가 같다(실행 확인).
3.2 ON / USING / NATURAL — 결과 열이 다르다
세 방식 모두 dno 로 등가 조인하면 행은 같다. 차이는 SELECT * 했을 때의 열 구성과 조건을
정하는 방식이다.
ON — 두 테이블의 dno 가 둘 다 나온다.
SELECT * FROM 프로젝트 p JOIN 부서 d ON p.dno = d.dno;
| pno | pname | dno | dno | dname | loc |
|---|---|---|---|---|---|
| 100 | 결제 | 10 | 10 | 개발 | 서울 |
| 200 | 광고 | 20 | 20 | 영업 | 부산 |
USING — 공통 열이 한 번만, 그것도 맨 앞에 나온다.
SELECT * FROM 프로젝트 JOIN 부서 USING (dno);
| dno | pno | pname | dname | loc |
|---|---|---|---|---|
| 10 | 100 | 결제 | 개발 | 서울 |
| 20 | 200 | 광고 | 영업 | 부산 |
NATURAL — 이름이 같은 열을 알아서 전부 찾아 USING 처럼 조인한다. 여기선 공통 열이 dno 뿐이라 USING 과 결과가 같다.
SELECT * FROM 프로젝트 NATURAL JOIN 부서;
| dno | pno | pname | dname | loc |
|---|---|---|---|---|
| 10 | 100 | 결제 | 개발 | 서울 |
| 20 | 200 | 광고 | 영업 | 부산 |
| 조건 지정 | SELECT * 의 공통 열 | 열 이름이 달라도? | 비등가 조건 | |
|---|---|---|---|---|
| ON | 직접 쓴 식 | 양쪽 다 (중복) | 가능 (a.x = b.y) | 가능 |
| USING | 괄호 안의 열 | 한 번만 | 불가 (이름이 같아야) | 불가 (등가만) |
| NATURAL | 같은 이름 열 전부 자동 | 한 번만 | 불가 | 불가 |
NATURAL 의 함정 — "이름이 같은 열 전부"이므로 의도치 않은 열이 끼어든다.
프로젝트 NATURAL JOIN 사원은 공통 열이dno이므로 "프로젝트 주관 부서 = 사원 소속 부서"인 짝을 만든다(결과 6행, 실행 확인). "프로젝트 참여자"를 원했다면 완전히 틀린 답이다. 실무에서는 테이블에 열이 추가되기만 해도 조인 결과가 바뀌므로 잘 쓰지 않는다.
USING/NATURAL 열에는 테이블 접두사를 못 붙인다 — Oracle 은
USING(dno)후p.dno라고 쓰면 오류(ORA-25154). 그냥dno라고 써야 한다. PostgreSQL·MySQL 은 접두사를 허용한다.
4. 등가 조인과 비등가 조인
| 분류 | 조건 | 예 |
|---|---|---|
| 등가 조인(equi join) | = | e.dno = d.dno |
| 비등가 조인(non-equi join) | = 이외 (BETWEEN, >, <, <> …) | e.sal BETWEEN g.losal AND g.hisal |
| 자연 조인(natural join) | 등가 조인 + 중복 열 제거 | NATURAL JOIN |
| 세타 조인(θ-join) | 임의의 비교 θ — 등가·비등가의 상위 개념 | 관계대수 용어 |
비등가 조인 예 — 급여 등급 매기기
SELECT e.ename, e.sal, g.grade
FROM 사원 e JOIN 급여등급 g
ON e.sal BETWEEN g.losal AND g.hisal
ORDER BY e.eno;
| ename | sal | grade |
|---|---|---|
| 김하늘 | 500 | 3 |
| 이도윤 | 350 | 2 |
| 박서준 | 280 | 1 |
| 최지우 | 450 | 3 |
| 정민재 | 300 | 2 |
| 한유나 | 300 | 2 |
| 오세린 | 250 | 1 |
같은 이름의 열이 없고 = 도 아니므로 USING/NATURAL 로는 표현할 수 없다 — ON 이 필요한 대표 사례.
등급 구간이 겹치면(예: 1등급 0~300, 2등급 300~399) 300 인 사원이 두 등급과 짝지어져 행이 늘어난다.
비등가 조인에서는 구간이 겹치지 않는지 먼저 확인할 것.
5. OUTER JOIN — 짝 없는 행을 NULL 로 살린다
5.1 문법 골격
FROM A LEFT [OUTER] JOIN B ON 조건 -- A(왼쪽)의 모든 행 보존
FROM A RIGHT [OUTER] JOIN B ON 조건 -- B(오른쪽)의 모든 행 보존
FROM A FULL [OUTER] JOIN B ON 조건 -- 양쪽 모든 행 보존
동작: ① INNER JOIN 결과를 만든다 → ② 보존할 쪽에서 짝을 하나도 못 찾은 행을 골라 → ③ 반대쪽 열을 전부 NULL 로 채워 결과에 추가한다.
5.2 LEFT JOIN
SELECT e.ename, d.dname
FROM 사원 e LEFT JOIN 부서 d ON e.dno = d.dno
ORDER BY e.eno;
| ename | dname | |
|---|---|---|
| 김하늘 | 개발 | INNER 결과 |
| 이도윤 | 개발 | INNER 결과 |
| 박서준 | 개발 | INNER 결과 |
| 최지우 | 영업 | INNER 결과 |
| 정민재 | 영업 | INNER 결과 |
| 한유나 | 영업 | INNER 결과 |
| 오세린 | NULL | 짝 없는 왼쪽 행 → NULL 채움 |
5.3 RIGHT JOIN
SELECT d.dno, d.dname, e.ename
FROM 사원 e RIGHT JOIN 부서 d ON e.dno = d.dno
ORDER BY d.dno, e.eno;
| dno | dname | ename |
|---|---|---|
| 10 | 개발 | 김하늘 |
| 10 | 개발 | 이도윤 |
| 10 | 개발 | 박서준 |
| 20 | 영업 | 최지우 |
| 20 | 영업 | 정민재 |
| 20 | 영업 | 한유나 |
| 30 | 인사 | NULL |
| 40 | 기획 | NULL |
A RIGHT JOIN B 는 B LEFT JOIN A 와 같다. 그래서 실무에선 LEFT 로 통일하는 경우가 많다.
5.4 FULL OUTER JOIN
SELECT e.ename, d.dname
FROM 사원 e FULL OUTER JOIN 부서 d ON e.dno = d.dno;
| ename | dname | |
|---|---|---|
| 김하늘 | 개발 | |
| 이도윤 | 개발 | |
| 박서준 | 개발 | |
| 최지우 | 영업 | |
| 정민재 | 영업 | |
| 한유나 | 영업 | |
| 오세린 | NULL | 왼쪽에서 짝 없음 |
| NULL | 인사 | 오른쪽에서 짝 없음 |
| NULL | 기획 | 오른쪽에서 짝 없음 |
ORDER BY 가 없으므로 행 순서는 엔진마다 다를 수 있다(PostgreSQL 은 기획·인사 순으로 냈다). 행의 집합은 같다.
| 엔진 | FULL OUTER JOIN |
|---|---|
| Oracle · PostgreSQL · SQL Server | 지원 |
| SQLite | 3.39 부터 RIGHT·FULL 지원 (이 노트는 3.54 로 실행) |
| MySQL | 미지원 — LEFT JOIN … UNION RIGHT JOIN … 으로 흉내 |
5.5 행 수 계산법
| 조인 | 행 수 |
|---|---|
| INNER | 조건을 만족하는 (A행, B행) 쌍의 수 = M |
| LEFT | M + (짝 없는 A 행 수) |
| RIGHT | M + (짝 없는 B 행 수) |
| FULL | M + (짝 없는 A) + (짝 없는 B) |
| CROSS | |A| × |B| |
사원 ⋈ 부서 (dno)에 적용:
| 계산 | 결과 | |
|---|---|---|
| INNER | 10번 3명 + 20번 3명 | 6 |
| 사원 LEFT 부서 | 6 + 오세린 1 | 7 |
| 사원 RIGHT 부서 | 6 + 인사·기획 2 | 8 |
| FULL | 6 + 1 + 2 | 9 (실행 확인 COUNT(*) = 9) |
| CROSS | 7 × 4 | 28 |
"LEFT JOIN 결과 = 왼쪽 행 수" 가 아니다. 왼쪽 한 행에 오른쪽 짝이 여럿이면 그만큼 복제된다.
사원 LEFT JOIN 참여는 사원이 7명인데 8행 — 이도윤이 프로젝트 2개에 참여해 두 줄이 된다. LEFT JOIN 의 행 수는 "왼쪽 행 수 이상"이다.
SELECT e.eno, e.ename, j.pno FROM 사원 e LEFT JOIN 참여 j ON e.eno = j.eno ORDER BY e.eno, j.pno;
| eno | ename | pno |
|---|---|---|
| 1 | 김하늘 | 100 |
| 2 | 이도윤 | 100 |
| 2 | 이도윤 | 200 |
| 3 | 박서준 | 100 |
| 4 | 최지우 | NULL |
| 5 | 정민재 | 200 |
| 6 | 한유나 | NULL |
| 7 | 오세린 | NULL |
6. ON 과 WHERE — OUTER JOIN 에서는 결과가 다르다
INNER JOIN 에서는 조건을 ON 에 두든 WHERE 에 두든 결과가 같다.
SELECT e.ename, d.dname FROM 사원 e JOIN 부서 d ON e.dno = d.dno
WHERE d.loc = '서울' AND e.sal >= 300; -- 김하늘, 이도윤
SELECT e.ename, d.dname FROM 사원 e JOIN 부서 d ON e.dno = d.dno
AND d.loc = '서울' AND e.sal >= 300; -- 김하늘, 이도윤
INNER 는 "곱한 뒤 거른다" 하나뿐이라 거르는 조건을 어디에 적든 같다. 그러나 OUTER JOIN 은 거른 뒤에 "짝 없는 행 되살리기" 단계가 있어서, 조건이 그 단계의 앞(ON) 인지 뒤(WHERE) 인지에 따라 결과가 바뀐다.
| 위치 | 처리 시점 | 의미 |
|---|---|---|
| ON | 짝을 찾을 때 | "이 조건을 만족하는 것만 짝으로 인정" — 짝이 없으면 NULL 로 되살아난다 |
| WHERE | 조인이 끝난 뒤 | "결과 행 중 이 조건을 만족하는 것만 남김" — NULL 로 채운 행도 가차 없이 탈락 |
6.1 오른쪽(보존되지 않는 쪽) 테이블 조건
"부서별로 급여 400 이상인 사원을 보여 주되, 부서는 전부 나오게."
(a) 조건을 ON 에
SELECT d.dname, e.ename
FROM 부서 d LEFT JOIN 사원 e
ON d.dno = e.dno AND e.sal >= 400
ORDER BY d.dno;
| 단계 | 내용 |
|---|---|
| 짝 찾기 | 개발 ↔ 김하늘(500) / 영업 ↔ 최지우(450). 이도윤·박서준·정민재·한유나는 400 미만이라 짝으로 인정 안 됨 |
| 되살리기 | 짝이 하나도 없는 인사·기획 → 사원 열 NULL |
| dname | ename |
|---|---|
| 개발 | 김하늘 |
| 영업 | 최지우 |
| 인사 | NULL |
| 기획 | NULL |
(b) 조건을 WHERE 에
SELECT d.dname, e.ename
FROM 부서 d LEFT JOIN 사원 e ON d.dno = e.dno
WHERE e.sal >= 400
ORDER BY d.dno;
| 단계 | 내용 |
|---|---|
| LEFT JOIN 결과 | 개발 3행 + 영업 3행 + 인사(NULL) + 기획(NULL) = 8행 |
WHERE e.sal >= 400 | 김하늘·최지우만 참. 인사·기획 행은 NULL >= 400 → UNKNOWN → 탈락 |
| dname | ename |
|---|---|
| 개발 | 김하늘 |
| 영업 | 최지우 |
LEFT JOIN 을 썼는데 결과가 INNER JOIN 과 같아졌다. 보존되지 않는 쪽(오른쪽) 테이블의 열을 WHERE 에서 거르면 OUTER JOIN 이 사실상 INNER JOIN 으로 바뀐다 — 실무·시험 공통의 1번 함정.
6.2 "OR IS NULL" 로 고치면 될까? — 안 된다
SELECT d.dname, e.ename FROM 부서 d LEFT JOIN 사원 e ON d.dno = e.dno
WHERE e.sal >= 500 OR e.eno IS NULL; -- 기준을 500 으로 올려 비교
ON 에 e.sal >= 500 | WHERE 에 e.sal >= 500 OR e.eno IS NULL |
|---|---|
| 개발 · 김하늘 | 개발 · 김하늘 |
| 영업 · NULL | (영업 사라짐) |
| 인사 · NULL | 인사 · NULL |
| 기획 · NULL | 기획 · NULL |
영업부는 사원이 있지만 500 이상인 사람이 없다. ON 버전은 "짝 없음"으로 NULL 을 채워 살리지만,
WHERE 버전에서는 영업부 행이 이미 최지우·정민재·한유나와 짝지어진 채로 만들어져 e.eno IS NULL 도
거짓이다. 결과가 같아 보이는 경우(기준 400)는 우연이다.
6.3 왼쪽(보존되는 쪽) 테이블 조건
-- (a) ON 에
SELECT d.dname, e.ename FROM 부서 d LEFT JOIN 사원 e
ON d.dno = e.dno AND d.loc = '서울';
-- (b) WHERE 에
SELECT d.dname, e.ename FROM 부서 d LEFT JOIN 사원 e
ON d.dno = e.dno
WHERE d.loc = '서울';
| (a) ON — 6행 | (b) WHERE — 4행 |
|---|---|
| 개발 · 김하늘 | 개발 · 김하늘 |
| 개발 · 이도윤 | 개발 · 이도윤 |
| 개발 · 박서준 | 개발 · 박서준 |
| 영업 · NULL | 인사 · NULL |
| 인사 · NULL | |
| 기획 · NULL |
- (a) 왼쪽 테이블 조건을 ON 에 두면 왼쪽 행을 거르지 못한다. 부산의 영업부도, loc 가 NULL 인 기획부도 다 나온다. 조건은 "짝을 붙일지"만 결정해서, 영업부는 사원이 있는데도 짝을 인정받지 못해 NULL 이 된다.
- (b) 왼쪽 행 자체를 거르려면 WHERE 에 둔다. 서울인 개발·인사만 남는다.
정리 — LEFT JOIN 에서
- 오른쪽 테이블 조건: ON 에 두면 "그런 짝만 붙인다", WHERE 에 두면 INNER 화.
- 왼쪽 테이블 조건: WHERE 에 두면 "그런 왼쪽 행만", ON 에 두면 왼쪽 행은 그대로 다 나오고 짝만 끊긴다.
7. SELF JOIN — 같은 테이블을 두 번
한 테이블 안의 행끼리 관계가 있을 때(사원의 mgr → 같은 테이블의 eno) 테이블에 별칭을 둘 붙여 서로 다른 테이블처럼 조인한다. 별칭이 없으면 어느 쪽 열인지 구분할 수 없으므로 별칭이 필수다.
SELECT e.ename AS 사원, m.ename AS 관리자
FROM 사원 e JOIN 사원 m ON e.mgr = m.eno
ORDER BY e.eno;
e = "부하로서의 사원", m = "관리자로서의 사원".
| 사원 | 관리자 |
|---|---|
| 이도윤 | 김하늘 |
| 박서준 | 이도윤 |
| 정민재 | 최지우 |
| 한유나 | 최지우 |
mgr 가 NULL 인 김하늘·최지우·오세린은 INNER 라서 빠진다. 관리자가 없는 사람도 보려면 LEFT JOIN:
SELECT e.ename AS 사원, m.ename AS 관리자
FROM 사원 e LEFT JOIN 사원 m ON e.mgr = m.eno
ORDER BY e.eno;
| 사원 | 관리자 |
|---|---|
| 김하늘 | NULL |
| 이도윤 | 김하늘 |
| 박서준 | 이도윤 |
| 최지우 | NULL |
| 정민재 | 최지우 |
| 한유나 | 최지우 |
| 오세린 | NULL |
관리자와 비교하기 — 관리자 급여의 70% 를 넘게 받는 사원:
SELECT e.ename, e.sal, m.ename, m.sal
FROM 사원 e JOIN 사원 m ON e.mgr = m.eno
WHERE e.sal > m.sal * 0.7;
| e.ename | e.sal | m.ename | m.sal |
|---|---|---|---|
| 박서준 | 280 | 이도윤 | 350 |
(이도윤 350 vs 김하늘 500×0.7 = 350 → 같으므로 탈락, 박서준 280 > 245 ✓, 정민재·한유나 300 vs 315 ✗.)
같은 부서 동료 쌍 — a.eno < b.eno 로 (A,B)/(B,A) 중복과 자기 자신 짝을 없앤다.
SELECT COUNT(*) FROM 사원 a JOIN 사원 b ON a.dno = b.dno AND a.eno < b.eno; -- 6
10번 3명에서 3쌍 + 20번 3명에서 3쌍 = 6 (₃C₂ + ₃C₂). < 대신 <> 를 쓰면 12, 조건이 없으면 자기 자신 짝
포함 18 이 된다.
8. 3개 이상 테이블 조인
조인은 두 개씩 처리된다. A JOIN B JOIN C 는 (A ⋈ B) 의 결과에 C 를 붙인다. 테이블이 n 개면 조인
조건이 최소 n − 1 개 필요하다(하나라도 빠지면 그 부분이 카티션 곱).
SELECT e.ename, p.pname, j.hours
FROM 사원 e
JOIN 참여 j ON e.eno = j.eno
JOIN 프로젝트 p ON j.pno = p.pno
ORDER BY e.eno, p.pno;
| ename | pname | hours |
|---|---|---|
| 김하늘 | 결제 | 20 |
| 이도윤 | 결제 | 40 |
| 이도윤 | 광고 | 10 |
| 박서준 | 결제 | 30 |
| 정민재 | 광고 | 25 |
참여(5행)가 사원과 프로젝트를 잇는 연결(교차) 테이블이라 결과도 5행.
같은 테이블이 두 역할로 — 다른 부서 프로젝트에 참여한 사원(사원의 소속 부서와 프로젝트 주관 부서를 따로 붙이려면 부서를 두 번 조인한다):
SELECT e.ename, d.dname AS 소속, p.pname, pd.dname AS 주관부서
FROM 사원 e
JOIN 참여 j ON e.eno = j.eno
JOIN 프로젝트 p ON j.pno = p.pno
JOIN 부서 pd ON p.dno = pd.dno
JOIN 부서 d ON e.dno = d.dno
WHERE e.dno <> p.dno;
| ename | 소속 | pname | 주관부서 |
|---|---|---|---|
| 이도윤 | 개발 | 광고 | 영업 |
8.1 LEFT 뒤에 INNER 를 이으면 LEFT 가 무너진다
SELECT d.dname, e.ename, j.pno
FROM 부서 d
LEFT JOIN 사원 e ON d.dno = e.dno
JOIN 참여 j ON e.eno = j.eno; -- 두 번째가 INNER
| dname | ename | pno |
|---|---|---|
| 개발 | 김하늘 | 100 |
| 개발 | 이도윤 | 100 |
| 개발 | 이도윤 | 200 |
| 개발 | 박서준 | 100 |
| 영업 | 정민재 | 200 |
첫 LEFT JOIN 으로 살려 둔 인사·기획(e.eno = NULL)이 두 번째 INNER JOIN 의 NULL = j.eno 에서 탈락한다.
참여하지 않은 최지우·한유나도 사라진다. 부서를 끝까지 보존하려면 뒤의 조인도 LEFT 로:
SELECT d.dname, e.ename, j.pno
FROM 부서 d
LEFT JOIN 사원 e ON d.dno = e.dno
LEFT JOIN 참여 j ON e.eno = j.eno;
| dname | ename | pno |
|---|---|---|
| 개발 | 김하늘 | 100 |
| 개발 | 이도윤 | 100 |
| 개발 | 이도윤 | 200 |
| 개발 | 박서준 | 100 |
| 영업 | 최지우 | NULL |
| 영업 | 정민재 | 200 |
| 영업 | 한유나 | NULL |
| 인사 | NULL | NULL |
| 기획 | NULL | NULL |
9행. "LEFT 를 한 번 쓰면 그 뒤에 붙는 조인도 대개 LEFT 여야 한다"는 감각을 가져 두자.
9. 조인 + 집계 — 중복 집계 함정
9.1 1:N 조인은 "1" 쪽을 복제한다
부서별 급여 합은 정상적으로 구해진다.
SELECT d.dname, SUM(e.sal)
FROM 부서 d JOIN 사원 e ON d.dno = e.dno
GROUP BY d.dno, d.dname;
| dname | SUM(e.sal) |
|---|---|
| 개발 | 1130 |
| 영업 | 1050 |
여기에 "참여 시간도 같이 보자"며 참여를 하나 더 조인하면:
SELECT d.dname, SUM(e.sal), COUNT(*)
FROM 부서 d
JOIN 사원 e ON d.dno = e.dno
JOIN 참여 j ON e.eno = j.eno
GROUP BY d.dno, d.dname;
| dname | SUM(e.sal) | COUNT(*) |
|---|---|---|
| 개발 | 1480 | 4 |
| 영업 | 300 | 1 |
조인 직후의 중간 표(개발부)를 펼쳐 보면 이유가 보인다.
| ename | sal | pno |
|---|---|---|
| 김하늘 | 500 | 100 |
| 이도윤 | 350 | 100 |
| 이도윤 | 350 | 200 |
| 박서준 | 280 | 100 |
이도윤이 프로젝트 2개에 참여해 급여가 두 번 더해졌다(500 + 350 + 350 + 280 = 1480). 반대로 영업부는 참여 기록이 정민재 하나뿐이라 최지우·한유나가 INNER JOIN 에서 사라져 300 만 남았다. 부풀기와 누락이 동시에 일어난 것이다.
9.2 SUM(DISTINCT) 는 해결책이 아니다
SELECT d.dname, SUM(DISTINCT e.sal), SUM(e.sal)
FROM 부서 d JOIN 사원 e ON d.dno = e.dno
GROUP BY d.dno, d.dname;
| dname | SUM(DISTINCT e.sal) | SUM(e.sal) |
|---|---|---|
| 개발 | 1130 | 1130 |
| 영업 | 750 | 1050 |
DISTINCT 는 "같은 행"이 아니라 "같은 값"을 접는다. 영업부의 정민재·한유나는 둘 다 300 이라 하나로
접혀 750 이 된다. (반면 COUNT(DISTINCT e.eno) 처럼 키에 DISTINCT 를 거는 건 안전하다.)
9.3 정석 — 먼저 집계하고 나중에 조인
복제되기 전, 각 테이블을 자기 단위로 먼저 집계한 뒤 조인한다.
SELECT d.dname, t.급여합, t.인원
FROM 부서 d
JOIN (SELECT dno, SUM(sal) AS 급여합, COUNT(*) AS 인원
FROM 사원 GROUP BY dno) t
ON d.dno = t.dno;
| dname | 급여합 | 인원 |
|---|---|---|
| 개발 | 1130 | 3 |
| 영업 | 1050 | 3 |
참여 시간처럼 N 쪽 값을 합칠 때는 LEFT JOIN 과 COUNT(DISTINCT 키) 를 같이 쓰면 누락도 막는다.
SELECT d.dname, COUNT(DISTINCT e.eno) AS 인원, SUM(j.hours) AS 시간
FROM 부서 d
JOIN 사원 e ON d.dno = e.dno
LEFT JOIN 참여 j ON e.eno = j.eno
GROUP BY d.dno, d.dname;
| dname | 인원 | 시간 |
|---|---|---|
| 개발 | 3 | 100 |
| 영업 | 3 | 25 |
9.4 LEFT JOIN + COUNT(*) vs COUNT(열)
SELECT d.dname, COUNT(*), COUNT(e.eno)
FROM 부서 d LEFT JOIN 사원 e ON d.dno = e.dno
GROUP BY d.dno, d.dname;
| dname | COUNT(*) | COUNT(e.eno) |
|---|---|---|
| 개발 | 3 | 3 |
| 영업 | 3 | 3 |
| 인사 | 1 | 0 |
| 기획 | 1 | 0 |
사원이 없는 부서도 NULL 로 채운 행이 1개 있으므로
COUNT(*) 는 1 이다. "부서별 인원"은 오른쪽 테이블의 NULL 불가 열(기본키)을 세야 0 이 나온다.
10. Oracle (+) 문법
ANSI JOIN 이 들어오기 전(Oracle 8i 까지) Oracle 은 WHERE 절에 (+) 를 붙여 외부 조인을 썼다.
(+) 는 "NULL 로 채워질(부족한) 쪽"에 붙인다 — 보존되는 쪽의 반대편이다.
| Oracle 전통 표기 | ANSI 대응 |
|---|---|
WHERE e.dno = d.dno(+) | 사원 e LEFT JOIN 부서 d ON e.dno = d.dno |
WHERE e.dno(+) = d.dno | 사원 e RIGHT JOIN 부서 d ON e.dno = d.dno |
(양쪽 (+) 불가) | FULL OUTER JOIN — (+) 로는 표현 못 함 |
-- Oracle 전통 표기 (실행 불가 — 표준 대응 쿼리 §5.2 의 결과와 같다)
SELECT e.ename, d.dname
FROM 사원 e, 부서 d
WHERE e.dno = d.dno(+);
조건이 여러 개면 오른쪽 테이블의 모든 조건에 (+) 를 붙여야 ON 에 둔 것과 같아진다.
WHERE d.dno = e.dno(+) AND e.sal(+) >= 400 -- = LEFT JOIN … ON d.dno = e.dno AND e.sal >= 400
WHERE d.dno = e.dno(+) AND e.sal >= 400 -- = LEFT JOIN … WHERE e.sal >= 400 (INNER 화, §6.1)
(+) 를 빠뜨린 조건은 WHERE 조건이 되어 §6 의 함정과 똑같이 OUTER 가 무너진다. Oracle 도 현재는 ANSI
표기를 권장하며, MySQL·PostgreSQL 은 (+) 를 지원하지 않는다.
11. 세미 조인과 안티 조인
| 개념 | 묻는 것 | 결과 | SQL 표현 |
|---|---|---|---|
| 세미 조인 | "짝이 있는 A 행" | A 의 열만, A 행당 최대 1번 | EXISTS, IN |
| 안티 조인 | "짝이 없는 A 행" | A 의 열만 | NOT EXISTS, LEFT JOIN … IS NULL, (NOT IN) |
11.1 세미 조인 — EXISTS 는 중복을 만들지 않는다
"프로젝트에 참여한 사원"
SELECT e.ename FROM 사원 e
WHERE EXISTS (SELECT 1 FROM 참여 j WHERE j.eno = e.eno);
| ename |
|---|
| 김하늘 |
| 이도윤 |
| 박서준 |
| 정민재 |
같은 질문을 JOIN 으로 쓰면:
SELECT e.ename FROM 사원 e JOIN 참여 j ON e.eno = j.eno;
| ename |
|---|
| 김하늘 |
| 이도윤 |
| 이도윤 |
| 박서준 |
| 정민재 |
JOIN 은 짝마다 한 행이라 이도윤이 두 번. EXISTS 는 "짝이 하나라도 있는가"만 보고 첫 짝을 찾으면 멈추므로 A 행을 복제하지 않는다. 이것이 세미 조인이다(JOIN + DISTINCT 와 같은 결과를 더 직접적으로 표현).
11.2 안티 조인 — 세 가지 쓰는 법과 NOT IN 의 함정
"사원이 없는 부서"
-- ① NOT EXISTS
SELECT d.dname FROM 부서 d
WHERE NOT EXISTS (SELECT 1 FROM 사원 e WHERE e.dno = d.dno);
-- ② LEFT JOIN + IS NULL
SELECT d.dname FROM 부서 d LEFT JOIN 사원 e ON d.dno = e.dno
WHERE e.eno IS NULL;
| dname |
|---|
| 인사 |
| 기획 |
②는 §6 의 "WHERE 에 오른쪽 조건" 을 의도적으로 쓴 것이다. 짝을 못 찾아 NULL 로 채워진 행만 고른다.
이때 검사하는 열은 원래 NULL 이 될 수 없는 열(기본키 e.eno)이어야 한다.
-- ③ NOT IN — 함정
SELECT d.dname FROM 부서 d
WHERE d.dno NOT IN (SELECT dno FROM 사원);
| dname |
|---|
| (0행) |
서브쿼리 결과에 오세린의 NULL 이 섞여 있기 때문이다. 30 NOT IN (10, 20, NULL) 은
30<>10 AND 30<>20 AND 30<>NULL = TRUE AND TRUE AND UNKNOWN = UNKNOWN → 탈락. 모든 부서가 이렇게
떨어져 결과가 비어 버린다.
| 표현 | 서브쿼리에 NULL 이 있을 때 | 결과 |
|---|---|---|
NOT EXISTS | 영향 없음 (짝 조건 e.dno = d.dno 가 그 행에서 UNKNOWN → 짝 아님) | 인사, 기획 |
LEFT JOIN … IS NULL | 영향 없음 | 인사, 기획 |
NOT IN | 전체가 UNKNOWN | 0행 |
NOT IN (… WHERE dno IS NOT NULL) | NULL 제거 | 인사, 기획 |
안티 조인은 NOT EXISTS 로 쓰는 습관을 들이자. NOT IN 은 서브쿼리 열이 NOT NULL 이라고 확신할 때만.
12. 조인 알고리즘 — DBMS 는 실제로 어떻게 붙이나
§2 의 "곱한 뒤 거른다"는 의미이고, 실제 실행은 옵티마이저가 아래 방식 중 하나를 고른다(실기 이론 수준).
12.1 Nested Loop Join (중첩 반복)
for each 행 r in 바깥(선행, driving) 테이블:
for each 행 s in 안쪽(후행) 테이블 where 조인조건(r, s): ← 안쪽은 인덱스로 찾는다
결과에 (r, s) 출력
- 바깥 행 하나마다 안쪽을 인덱스로 탐색한다. 안쪽 조인 열에 인덱스가 없으면 매번 전체 스캔이라 매우 느리다.
- 바깥 테이블이 작을수록(선행 테이블 선택) 유리. 결과의 첫 행이 빨리 나와 온라인(OLTP)·부분 범위 처리에 적합.
=아닌 비등가 조건에도 쓸 수 있다.
12.2 Sort Merge Join (정렬 병합)
1. 두 테이블을 조인 키로 각각 정렬
2. 두 정렬 결과를 앞에서부터 나란히 훑으며(병합) 키가 같은 것끼리 짝짓기
- 인덱스가 없어도 되고, 이미 정렬되어 있으면(인덱스 순서 등) 정렬 비용이 사라진다.
- 정렬 비용이 크다.
<,>,BETWEEN같은 범위(비등가) 조건에도 쓸 수 있다.
12.3 Hash Join
1. Build: 작은 테이블로 조인 키의 해시 테이블을 메모리에 만든다
2. Probe: 큰 테이블을 한 번 읽으며 각 행의 키를 해시해 짝을 찾는다
- 등가(
=) 조인에서만 가능하다(해시는 "같다"만 판별할 수 있다). - 대용량 + 인덱스 없음 + 등가 조건에서 유리. 해시 테이블을 만드는 작은 쪽이 메모리에 들어갈수록 빠르다.
- 해시 테이블을 다 만들어야 시작하므로 첫 행은 늦게 나온다 → 배치·대량(DW) 처리에 적합.
| Nested Loop | Sort Merge | Hash | |
|---|---|---|---|
| 핵심 동작 | 바깥 행마다 안쪽 탐색 | 둘 다 정렬 후 병합 | 작은 쪽 해시 → 큰 쪽 탐색 |
| 조인 조건 | 모든 조건 | 등가 + 범위 | 등가만 |
| 인덱스 | 안쪽에 필요 | 불필요 | 불필요 |
| 적합 | 소량·OLTP, 첫 행 빠름 | 이미 정렬된 데이터, 범위 조인 | 대량·배치, 인덱스 없음 |
| 주요 비용 | 랜덤 액세스 반복 | 정렬 | 해시 테이블 메모리 |
같은 쿼리라도 데이터 양·인덱스·통계에 따라 옵티마이저가 다른 알고리즘을 고른다. 알고리즘은 성능만 바꾸고 결과는 바꾸지 않는다 — 결과는 언제나 §2 의 "곱한 뒤 거른다" 그대로다.
13. 함정 모음
| 함정 | 틀린 생각 | 실제 |
|---|---|---|
| 조인 조건 누락 | 오류가 난다 | 카티션 곱 (행 폭발) |
| NULL 키 | NULL 끼리 짝지어진다 | NULL = NULL 은 UNKNOWN → 짝 없음 |
| LEFT 행 수 | 왼쪽 행 수와 같다 | 왼쪽 행 수 이상 (1:N 복제) |
| 오른쪽 조건을 WHERE 에 | LEFT 유지 | INNER 로 변함 |
| 왼쪽 조건을 ON 에 | 왼쪽 행이 걸러진다 | 왼쪽 행은 다 나오고 짝만 끊긴다 |
| LEFT 뒤 INNER | 왼쪽 보존 | 뒤 INNER 에서 NULL 행 탈락 |
| NATURAL JOIN | 내가 생각한 열로 조인 | 이름 같은 열 전부 |
USING SELECT * | 공통 열 두 번 | 한 번, 맨 앞 |
| 1:N 조인 후 SUM | 원래 합계 | 1 쪽 값이 복제돼 부풀림 |
SUM(DISTINCT) 로 복구 | 해결 | 같은 값이 접혀 틀림 |
LEFT + COUNT(*) | 짝 없으면 0 | 1 (NULL 채운 행) |
NOT IN + NULL | 짝 없는 행 반환 | 0행 |
| Hash Join | 모든 조인에 사용 | 등가 조인만 |
확인 문제
문제 1 (계산) SELECT COUNT(*) FROM 사원 CROSS JOIN 프로젝트; 의 결과는?
문제 2 (계산) 다음 각각의 결과 행 수를 쓰시오.
(가) 사원 e LEFT JOIN 부서 d ON e.dno = d.dno (나) 부서 d LEFT JOIN 사원 e ON d.dno = e.dno
(다) 사원 e FULL OUTER JOIN 부서 d ON e.dno = d.dno
문제 3 (결과 쓰기) 두 쿼리의 결과 행 수와 내용을 쓰시오.
-- (가)
SELECT d.dname, e.ename FROM 부서 d LEFT JOIN 사원 e
ON d.dno = e.dno AND e.job = '팀장';
-- (나)
SELECT d.dname, e.ename FROM 부서 d LEFT JOIN 사원 e
ON d.dno = e.dno
WHERE e.job = '팀장';
문제 4 (결과 쓰기) 관리자별 직속 부하 수를 구한다. 결과를 쓰시오.
SELECT m.ename, COUNT(*)
FROM 사원 e JOIN 사원 m ON e.mgr = m.eno
GROUP BY m.eno, m.ename;
문제 5 (결과 쓰기) 개발부의 SUM(e.sal) 값은?
SELECT d.dname, SUM(e.sal)
FROM 부서 d JOIN 사원 e ON d.dno = e.dno JOIN 참여 j ON e.eno = j.eno
GROUP BY d.dno, d.dname;
문제 6 (결과 쓰기) 두 쿼리의 결과를 각각 쓰시오.
SELECT dname FROM 부서 WHERE dno NOT IN (SELECT dno FROM 사원); -- (가)
SELECT dname FROM 부서 d WHERE NOT EXISTS (SELECT 1 FROM 사원 e WHERE e.dno = d.dno); -- (나)
문제 7 (빈칸) Oracle 의 SELECT e.ename, d.dname FROM 사원 e, 부서 d WHERE e.dno = d.dno(+); 를 ANSI
표기로 바꾸면 FROM 사원 e ( ① ) 부서 d ON e.dno = d.dno 이다.
문제 8 (OX)
(가) SELECT * FROM 프로젝트 JOIN 부서 USING (dno) 의 결과 열은 6개다. ( )
(나) Hash Join 은 e.sal BETWEEN g.losal AND g.hisal 같은 비등가 조인에 쓸 수 없다. ( )
문제 9 (결과 쓰기) 프로젝트별 참여 인원은?
SELECT p.pname, COUNT(j.eno)
FROM 프로젝트 p LEFT JOIN 참여 j ON p.pno = j.pno
GROUP BY p.pno, p.pname;
정답과 풀이
- 21 — 7 × 3. 조건 없는 곱은 행 수의 곱.
- (가) 7 (나) 8 (다) 9 — INNER 6 에 (가) 짝 없는 사원 1(오세린), (나) 짝 없는 부서 2(인사·기획), (다) 둘 다 더한다.
- (가) 4행: 개발·김하늘, 영업·최지우, 인사·NULL, 기획·NULL / (나) 2행: 개발·김하늘, 영업·최지우.
오른쪽 테이블 조건을 WHERE 에 두면 NULL 로 채운 인사·기획이
NULL = '팀장'에서 탈락해 INNER 와 같아진다. - 김하늘 1, 이도윤 1, 최지우 2 — e.mgr 로 m 을 찾으면 이도윤→김하늘, 박서준→이도윤, 정민재·한유나→최지우.
- 1480 — 이도윤이 참여 2건이라 350 이 두 번 더해진다. 실제 개발부 급여 합(1130)이 아니다.
- (가) 0행 (나) 인사, 기획 — 사원.dno 에 NULL(오세린)이 있어
NOT IN은 모든 행에서 UNKNOWN. - ① LEFT [OUTER] JOIN —
(+)는 NULL 로 채워질 쪽(부서)에 붙으므로 보존되는 쪽은 사원(왼쪽). - (가) X — USING 은 공통 열 dno 를 한 번만 내므로 dno, pno, pname, dname, loc 의 5개(ON 이면 6개). (나) O — 해시는 같은지만 판별하므로 등가 조인 전용. 비등가는 Nested Loop·Sort Merge.
- 결제 3, 광고 2, 신규 0 — 신규는 참여 기록이 없어 NULL 로 채워지고
COUNT(j.eno)가 NULL 을 세지 않아 0.COUNT(*)였다면 1 이 된다.
한 줄 요약
| 주제 | 한 줄 |
|---|---|
| 조인의 원리 | 카티션 곱 → 조건 필터 (OUTER 는 + 짝 없는 행 NULL 로 되살리기) |
| CROSS JOIN | 행 수 = |A| × |B| |
| INNER | 양쪽 짝 있는 행만. NULL 키는 짝이 없다 |
| ON / USING / NATURAL | ON 은 공통 열 둘 다, USING·NATURAL 은 한 번만 맨 앞. NATURAL 은 같은 이름 열 전부 |
| 등가 / 비등가 | = / BETWEEN·부등호 — 비등가는 ON 으로만 |
| OUTER 행 수 | INNER + 보존할 쪽의 짝 없는 행 수 |
| ON vs WHERE | OUTER 에서 오른쪽 조건을 WHERE 에 두면 INNER 화, 왼쪽 조건은 WHERE 에 |
| SELF JOIN | 별칭 둘(e, m)로 같은 테이블을 두 역할로 |
| 다중 조인 | n 테이블 → 조건 n−1 개 이상. LEFT 뒤 INNER 는 보존을 깬다 |
| 조인 + 집계 | 1:N 조인은 1 쪽을 복제 → 먼저 집계 후 조인 |
| Oracle (+) | NULL 로 채워질 쪽에 붙인다. d.dno(+) = 사원 LEFT JOIN 부서 |
| 세미 / 안티 | EXISTS(중복 없음) / NOT EXISTS — NOT IN 은 NULL 이 있으면 0행 |
| 조인 알고리즘 | NL 은 소량·인덱스, Sort Merge 는 정렬·범위, Hash 는 대량·등가만 |