SQL JOIN 완전 정리 - INNER LEFT RIGHT FULL CROSS SELF와 카티전 곱 (SQL 중급 7단원)

이 단원에서 배우는 것

6단원까지는 테이블 하나를 놓고 조건을 걸고 정렬하고 그룹으로 묶어 집계했다. 그런데 현업 스키마는 정규화되어 있어서 사원 이름은 emp 에, 부서 이름은 dept 에 나뉘어 있다. 조회 시점에 이 둘을 다시 붙이는 문법이 JOIN 이고, 중급 과정은 여기서 시작한다. 실습 테이블은 1단원에서 만든 dept · emp 를 그대로 쓰고, 이번 단원에 프로젝트 테이블 하나만 추가한다.

INNER / LEFT / RIGHT / FULL / CROSS / SELF 조인이 각각 어떤 행을 남기는지 구분한다
조인 결과가 카티전 곱에서 출발한다는 관점으로 결과 건수를 미리 예측한다
LEFT JOIN 이 소리 없이 INNER JOIN 으로 강등되는 상황을 잡아낸다

개념

부서 이름을 사원 테이블에 그대로 넣어 두면 부서명이 바뀔 때 사원 행을 전부 고쳐야 하고, 고치다 말면 같은 부서가 두 이름으로 남는다. 그래서 부서는 dept 에 한 번만 저장하고 사원 테이블에는 dept_id 참조 값만 둔다. 저장할 때 쪼갠 것을 읽을 때 다시 합치는 작업, 그게 조인이다.

조인을 문법부터 외우면 헷갈린다. 다음 순서로 이해하면 결과 건수까지 예측할 수 있다.

두 테이블의 모든 행 조합을 만든다. 사원 10행 × 부서 5행이면 50행이다. 이것이 카티전 곱이다.
ON 조건에 맞는 조합만 남긴다. 여기까지가 INNER JOIN 이다.
OUTER 조인이면, 짝을 하나도 못 찾은 쪽 행을 반대편 컬럼을 NULL 로 채워 되살린다.
그 결과에 WHERE 를 적용한다.

옵티마이저가 실제로 50행을 만들지는 않는다. 하지만 결과의 의미는 이 순서로 정의되고, 3번과 4번의 순서 차이가 뒤에 나올 대표적인 사고의 원인이 된다.

표준 SQL 문법과 예제

실습 데이터 확인

1단원의 dept 에는 인사팀(10) · 개발팀(20) · 영업팀(30) · 재무팀(40) · 총무팀(50) 다섯 행이, emp 에는 사원 열 명이 들어 있다. 조인 실습에서 중요한 것은 데이터에 일부러 뚫려 있는 두 개의 구멍이다. 총무팀(50)에는 사원이 하나도 없고, 홍범도(1009)는 dept_id 가 NULL 이다. 조인 종류의 차이는 정확히 이 두 행에서 드러난다.

여기에 프로젝트 테이블을 하나 추가한다. 12단원까지 계속 쓴다.

CREATE TABLE proj (
    proj_id   INT         NOT NULL,
    proj_name VARCHAR(40) NOT NULL,
    dept_id   INT,
    PRIMARY KEY (proj_id)
);

INSERT INTO proj (proj_id, proj_name, dept_id) VALUES
 (9001, '사내포털 개편',   20),
 (9002, '결제모듈 재작성', 20),
 (9003, '신규 거래처 확보', 30),
 (9004, '채용시스템 도입', NULL);

INNER JOIN — 양쪽에 짝이 있는 행만

SELECT e.emp_name, d.dept_name
FROM emp e
JOIN dept d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

9행이 나온다. 부서가 NULL 인 홍범도는 짝이 없어서 사라지고, 사원이 없는 총무팀도 나오지 않는다. INNER 키워드는 생략할 수 있고 대부분 생략한다. e.dept_id = d.dept_id 는 NULL 끼리도 비교하지 않는다. NULL = NULL 은 참이 아니라 UNKNOWN 이기 때문이다. 2단원에서 배운 NULL 규칙이 조인에서도 그대로 적용된다.

LEFT OUTER JOIN — 왼쪽은 무조건 남긴다

SELECT e.emp_name, d.dept_name
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

10행이 나오고 홍범도 행의 dept_name 만 NULL 이다. "기준 집합은 전부 보여 주고 딸린 정보는 있으면 붙여라"가 실무에서 가장 자주 나오는 요구사항이라, 현업 쿼리의 상당수는 LEFT JOIN 이다.

RIGHT OUTER JOIN — 방향만 반대

SELECT e.emp_name, d.dept_name
FROM emp e
RIGHT JOIN dept d ON e.dept_id = d.dept_id;

9행에 총무팀 행 하나가 더 붙어 10행이다. 기능은 LEFT 와 대칭이지만 FROM 절을 읽는 순서와 기준 테이블이 어긋나 사람이 헷갈린다. 팀 규약으로 OUTER 조인은 LEFT 하나로 통일하고, 필요하면 테이블 순서를 바꾸는 편이 낫다.

FULL OUTER JOIN — 양쪽 다 남긴다

SELECT e.emp_name, d.dept_name
FROM emp e
FULL OUTER JOIN dept d ON e.dept_id = d.dept_id;

11행이다. 9행 + 홍범도 + 총무팀. MySQL/MariaDB 는 이 문법이 없어서 UNION 으로 흉내 낸다.

SELECT e.emp_name, d.dept_name
FROM emp e LEFT JOIN dept d ON e.dept_id = d.dept_id
UNION
SELECT e.emp_name, d.dept_name
FROM emp e RIGHT JOIN dept d ON e.dept_id = d.dept_id;

CROSS JOIN — 조건 없는 모든 조합

SELECT COUNT(*) FROM emp CROSS JOIN dept;

10 × 5 = 50 이 나온다. 실무에서 일부러 쓰는 경우는 "모든 부서 × 모든 월"처럼 빈칸까지 포함한 격자를 만들 때 정도다. 나머지 경우에 50이 나왔다면 그건 사고다.

SELF JOIN — 같은 테이블을 두 번 부른다

관리자도 사원이라 emp 안에 있다. 같은 테이블에 다른 별칭을 붙이면 남남처럼 조인할 수 있다.

SELECT e.emp_name AS 사원, m.emp_name AS 관리자
FROM emp e
LEFT JOIN emp m ON e.mgr_id = m.emp_id
ORDER BY e.emp_id;

LEFT 를 쓴 이유는 mgr_id 가 NULL 인 유관순 · 안중근 · 윤봉길 · 김구 네 명을 빠뜨리지 않기 위해서다. INNER 로 쓰면 팀장급이 조직도에서 통째로 사라진다.

테이블 세 개 이상

SELECT d.dept_name, e.emp_name, p.proj_name
FROM dept d
LEFT JOIN emp  e ON e.dept_id = d.dept_id
LEFT JOIN proj p ON p.dept_id = d.dept_id
ORDER BY d.dept_id, e.emp_id;

조인은 왼쪽부터 차례로 누적된다. 그리고 한 번 LEFT 로 시작했으면 뒤따르는 조인도 LEFT 여야 한다. 중간에 INNER 가 끼면 앞에서 살려 둔 NULL 행이 거기서 다시 잘려 나간다.

DB별 차이

항목	MySQL · MariaDB	PostgreSQL	Oracle	SQL Server
FULL OUTER JOIN	미지원, UNION 으로 대체	지원	지원	지원
구식 외부조인 문법	없음	없음	`WHERE e.dept_id = d.dept_id(+)`	`*=` 는 폐기됨
테이블 별칭 AS	가능	가능	`AS` 불가, `emp e` 만	가능
USING 절	지원	지원	지원(단, 해당 컬럼에 별칭 금지)	미지원
LATERAL / APPLY	MySQL 8.0.14+ LATERAL	LATERAL	12c+ CROSS/OUTER APPLY	CROSS/OUTER APPLY

NATURAL JOIN 은 SQL Server 를 뺀 셋이 지원하지만 쓰지 않는 편이 좋다. 같은 이름의 컬럼을 알아서 조인 조건으로 삼기 때문에, 나중에 누가 두 테이블에 created_at 컬럼을 추가하는 순간 조인 조건이 조용히 바뀌어 결과가 달라진다.

실무에서 자주 틀리는 것

1. LEFT JOIN 뒤 WHERE 가 조인을 INNER 로 강등시킨다

-- 의도: 모든 사원을 보여 주되, 서울 근무 부서면 부서명도 같이
SELECT e.emp_name, d.dept_name
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.dept_id
WHERE d.location = '서울';        -- 홍범도가 사라진다

LEFT JOIN 이 홍범도 행을 살려 놓아도 그 행의 d.location 은 NULL 이고, NULL = '서울' 은 참이 아니므로 WHERE 에서 잘린다. 오른쪽 테이블 컬럼에 거는 조건은 ON 절로 올려야 한다.

SELECT e.emp_name, d.dept_name
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.dept_id AND d.location = '서울';

반대로 WHERE d.dept_id IS NULL 처럼 "짝이 없는 행만" 골라내려는 안티 조인은 WHERE 에 두는 것이 맞다. 규칙은 하나다. 오른쪽 테이블을 걸러내려면 ON, 조인 결과를 걸러내려면 WHERE.

2. 1:N 조인 뒤의 집계는 값을 부풀린다

SELECT d.dept_name, SUM(e.salary) AS 인건비
FROM dept d
JOIN emp  e ON e.dept_id = d.dept_id
JOIN proj p ON p.dept_id = d.dept_id
GROUP BY d.dept_name;

개발팀은 사원 3명과 프로젝트 2건이 있으므로 조인 결과가 6행이 되고, 인건비 17,200 이 34,400 으로 두 배가 된다. 쿼리는 에러 없이 성공하고 숫자만 틀리기 때문에 가장 위험한 부류다. 서로 다른 축의 집계는 각각 집계한 뒤 조인해야 한다.

SELECT d.dept_name, s.인건비, c.프로젝트수
FROM dept d
LEFT JOIN (SELECT dept_id, SUM(salary) AS 인건비   FROM emp  GROUP BY dept_id) s ON s.dept_id = d.dept_id
LEFT JOIN (SELECT dept_id, COUNT(*)    AS 프로젝트수 FROM proj GROUP BY dept_id) c ON c.dept_id = d.dept_id;

FROM 절 안에 들어간 저 서브쿼리를 인라인 뷰라고 부른다. 8단원에서 정식으로 다룬다.

3. LEFT JOIN 에서 COUNT(*) 는 0을 세지 못한다

SELECT d.dept_name, COUNT(*) AS 잘못, COUNT(e.emp_id) AS 정답
FROM dept d
LEFT JOIN emp e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name;

총무팀은 NULL 로 채워진 행 하나가 남아 있으므로 COUNT(*) 가 1을 반환한다. 사람 수를 세려면 오른쪽 테이블의 NOT NULL 컬럼을 지정해 COUNT(e.emp_id) 로 써야 0이 나온다.

4. 조인 조건 누락

FROM emp e, dept d WHERE ... 같은 콤마 조인은 조건을 빼먹어도 문법 오류가 아니라 카티전 곱을 돌려준다. 100만 행 × 10만 행이면 그대로 서버가 멎는다. 명시적 JOIN ... ON 구문을 쓰면 PostgreSQL · Oracle · SQL Server 는 ON 이 빠졌을 때 문법 오류로 잡아 준다. 다만 MySQL · MariaDB 에서는 JOIN 이 CROSS JOIN 과 동의어라 ON 을 빠뜨려도 오류 없이 카티전 곱이 된다. 여기서는 파서를 믿을 수 없으므로, 카티전 곱을 의도했을 때만 CROSS JOIN 을 명시하는 규약으로 리뷰에서 걸러야 한다.

스스로 확인하기

사원이 한 명도 없는 부서까지 포함해서, 부서명과 사원 수를 출력하라.
모든 사원의 이름과 그 사원의 관리자 이름을 출력하라. 관리자가 없으면 없음 으로 표시한다.
부서가 배정되지 않은 사원과, 사원이 배정되지 않은 부서를 각각 찾아라.

-- 1  (총무팀이 0 으로 나와야 정답이다)
SELECT d.dept_name, COUNT(e.emp_id) AS emp_cnt
FROM dept d
LEFT JOIN emp e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;

-- 2
SELECT e.emp_name, COALESCE(m.emp_name, '없음') AS mgr_name
FROM emp e
LEFT JOIN emp m ON e.mgr_id = m.emp_id
ORDER BY e.emp_id;

-- 3
SELECT e.emp_name
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;          -- 홍범도

SELECT d.dept_name
FROM dept d
LEFT JOIN emp e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;           -- 총무팀

1번에서 GROUP BY 에 d.dept_id 를 함께 넣은 이유는 부서명이 중복될 가능성 때문이다. 이름이 아니라 키로 묶는 습관을 들이는 편이 안전하다.

다음 8단원에서는 위 2번 트랩에서 잠깐 쓴 인라인 뷰를 정식으로 다루고, 조인으로는 풀기 까다로운 "짝이 없는 행 찾기"를 EXISTS 로 다시 쓴다.

참고: MySQL JOIN 구문 문서, PostgreSQL 테이블 표현식 문서