Devin.KR
통합 검색 Ctrl K 로그인

SQLD · 기본

데이터 모델링과 SQL 기본

표준 조인 결과 예측 - INNER OUTER NATURAL USING CROSS 비등가 셀프 조인 (SQLD 기본 9장)

조인 종류별 결과 행 수를 먼저 계산하고, 외부 조인에서 ON 과 WHERE 조건의 차이, NATURAL JOIN 의 함정, 조인 뒤 집계가 부풀어 오르는 원인을 실행 결과로 확인한다.

개발자 · 원고 갱신

이 장에서 배우는 것

8장에서 한 테이블 안의 집계를 예측했다. 이 장은 두 테이블 이상을 잇는 조인이다. 조인 종류별 문법은 『SQL 실전 기초』 7단원에 정리되어 있다. 여기서는 결과가 몇 행인가를 먼저 계산하는 습관을 들인다. 조인 결과의 행 수를 맞히면 조건을 잘못 쓴 쿼리를 실행 전에 알아챌 수 있다.

  • INNER, LEFT·RIGHT·FULL OUTER, CROSS 조인의 행 수를 데이터만 보고 계산한다.
  • 외부 조인에서 조건을 ON 에 쓸 때와 WHERE 에 쓸 때의 차이, NATURAL JOIN 과 USING 의 함정을 확인한다.
  • 비등가 조인, 셀프 조인, 조인 뒤 집계가 부풀어 오르는 문제를 실행 결과로 확인한다.

핵심 개념

팀별 급여 합계와 매출 합계를 한 쿼리로 뽑은 보고서가 있었다. 급여 합계가 인사 시스템보다 20% 넘게 컸다. 쿼리는 문법상 아무 문제가 없었다. 판매 건수만큼 직원 행이 복제된 뒤에 급여를 더했기 때문이다. 조인은 행을 곱하는 연산이라는 사실을 잊으면 생기는 사고다.

조인 결과 행 수 계산법

조인결과 행 수
CROSS JOIN왼쪽 행 수 × 오른쪽 행 수
INNER JOIN조건을 만족하는 짝의 수. 1:N 이면 N 쪽 중 짝이 있는 행 수
LEFT OUTER JOININNER 결과 + 짝이 없는 왼쪽 행 수
RIGHT OUTER JOININNER 결과 + 짝이 없는 오른쪽 행 수
FULL OUTER JOININNER 결과 + 짝이 없는 왼쪽 행 + 짝이 없는 오른쪽 행

외부 조인에서 짝이 없는 행은 반대쪽 컬럼이 모두 NULL 로 채워진다.

staff 8행과 team 4행을 team_id 로 짝지으면 짝이 있는 7쌍이 INNER 결과다. 팀이 없는 강예린을 더하면 LEFT 8행, 직원이 없는 법무를 더하면 RIGHT 8행, 둘 다 더하면 FULL 9행이다(09-counts).

그림 · 조인은 행을 짝짓는다 — 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)은 비등가 조인에 쓴다.

gradelowhigh
103499
235004199
342004999
4500099999

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 JOIN7 + 팀이 없는 직원 1 (강예린)8
RIGHT OUTER JOIN7 + 직원이 없는 팀 1 (법무)8
FULL OUTER JOIN7 + 1 + 19
CROSS JOIN직원 8 × 팀 432

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 조인과 같아진다.

t.city = '서울' 을 ON 에 두면 짝을 고르는 조건이라 직원 8명이 모두 남고 영업팀 직원의 팀 이름만 NULL 이 된다. WHERE 에 두면 조인이 끝난 뒤 NULL 행을 버려 5행, 사실상 내부 조인이 된다(09-on-vs-where).

그림 · 외부 조인에서 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(실행)OracleSQL Server
RIGHT·FULL OUTER JOIN3.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

연습 문제

  1. team t LEFT JOIN staff s ON s.team_id = t.team_id 의 결과 행 수는?
  2. staff s LEFT JOIN team t ON ... WHERE t.team_id IS NULL 의 결과 행 수와, 이 쿼리가 찾는 대상을 쓰라.
  3. 직원과 관리자를 INNER 셀프 조인하면 몇 행인가?
  4. 급여 등급별 직원 수를 등급이 비어 있어도 나오도록 구하라. 각 등급의 인원을 예측하라.
  5. 직원 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
-
8

2. 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
-
1

3. 7. 관리자가 없는 대표 1명이 빠진다.

SELECT COUNT(*) AS n FROM staff e JOIN staff m ON m.staff_id = e.manager_id;

실행 결과:

n
-
7

4. 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      1

5. 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

다음 장에서는 쿼리 안에 쿼리를 넣는 서브쿼리와, 결과 집합끼리 합치고 빼는 집합 연산을 다룬다.

참고 자료

READER FEEDBACK

질문·오탈자·의견

내용에 관한 질문이나 오탈자, 더 나은 설명을 위한 의견을 남겨 주세요. 이 댓글은 원래 게시글과 같은 자리에 쌓입니다.

댓글 0

아직 댓글이 없습니다. 첫 댓글을 남겨 보세요.

댓글을 남기려면 로그인이 필요합니다.