소스: sql-src/mid_03_join/01_outer.sql. 샘플은 dept(부서, 직원 없는 총무 포함)와 emp(직원, 부서가 없는 윤프리 포함)이고, 주문 예제는 orders 를 함께 씁니다.
SELECT
d.dname
, e.name
FROM dept d
JOIN emp e ON d.code = e.dept
ORDER BY d.dname, e.name;DNAME | NAME
-----------+-------
개발팀 | 박선임
개발팀 | 이팀장
개발팀 | 최사원
경영지원팀 | 김대표
(총 10행, 뒤 6행 생략)직원 없는 총무, 부서 없는 윤프리 모두 결과에 없습니다. 둘 다 매칭 상대가 없어서입니다.
SELECT
d.dname
, e.name
FROM dept d
LEFT JOIN emp e ON d.code = e.dept
ORDER BY d.dname, e.name;DNAME | NAME
-----------+-------
개발팀 | 박선임
개발팀 | 이팀장
개발팀 | 최사원
경영지원팀 | 김대표
고객지원팀 | 강팀장
영업팀 | 오사원
영업팀 | 정팀장
영업팀 | 한주임
인사팀 | 문사원
인사팀 | 서팀장
총무팀 | NULL
(11행)SELECT
e.name
, d.dname
FROM dept d
RIGHT JOIN emp e ON d.code = e.dept
ORDER BY d.dname, e.name;NAME | DNAME
-------+-----------
윤프리 | NULL
박선임 | 개발팀
이팀장 | 개발팀
최사원 | 개발팀
김대표 | 경영지원팀
(총 11행, 뒤 6행 생략)LEFT JOIN 은 dept 가 기준이라 직원 없는 총무팀이 NULL 짝과 함께 남고, RIGHT JOIN 은 emp 가 기준이라 부서 없는 윤프리가 남습니다.
SELECT d.dname, e.name FROM dept d FULL JOIN emp e ON d.code = e.dept ORDER BY d.dname, e.name;예상 오류: Syntax error in SQL statement "SELECT d.dname, e.name FROM dept d FULL [*]JOIN emp e ON d.code = e.dept ORDER BY d.dname, e.name"H2 2.3.232 는 FULL JOIN·FULL OUTER JOIN 문법 자체를 모든 모드에서 지원하지 않습니다. 표준 문법인데도 실행이 안 되는 경우라, LEFT JOIN 결과와 RIGHT JOIN 결과를 UNION 으로 합쳐 대신 씁니다.
SELECT
d.dname
, e.name
FROM dept d
LEFT JOIN emp e ON d.code = e.dept
UNION
SELECT
d.dname
, e.name
FROM dept d
RIGHT JOIN emp e ON d.code = e.dept
ORDER BY dname, name;DNAME | NAME
-----------+-------
NULL | 윤프리
개발팀 | 박선임
개발팀 | 이팀장
개발팀 | 최사원
경영지원팀 | 김대표
고객지원팀 | 강팀장
영업팀 | 오사원
영업팀 | 정팀장
영업팀 | 한주임
인사팀 | 문사원
인사팀 | 서팀장
총무팀 | NULL
(12행)10건의 매칭 행에 짝 없는 총무팀과 윤프리가 더해져 12행입니다. UNION 은 중복을 자동으로 지워 주므로, LEFT·RIGHT 두 결과에 공통으로 있는 10행이 한 번씩만 남습니다.
SELECT
d.dname
, e.name
, e.sal
FROM dept d
LEFT JOIN emp e ON d.code = e.dept
WHERE e.sal > 400
ORDER BY d.dname, e.name;DNAME | NAME | SAL
-----------+--------+----
개발팀 | 박선임 | 500
개발팀 | 이팀장 | 650
경영지원팀 | 김대표 | 900
고객지원팀 | 강팀장 | 500
영업팀 | 정팀장 | 600
인사팀 | 서팀장 | 550
(6행)총무팀이 사라졌습니다. LEFT JOIN 이 만든 총무팀 | NULL | NULL 행이 WHERE e.sal > 400 을 통과하지 못했기 때문입니다. 아래처럼 조건을 ON 절로 옮기면 결과가 달라집니다.
SELECT
d.dname
, e.name
, e.sal
FROM dept d
LEFT JOIN emp e ON d.code = e.dept AND e.sal > 400
ORDER BY d.dname, e.name;DNAME | NAME | SAL
-----------+--------+-----
개발팀 | 박선임 | 500
개발팀 | 이팀장 | 650
경영지원팀 | 김대표 | 900
고객지원팀 | 강팀장 | 500
영업팀 | 정팀장 | 600
인사팀 | 서팀장 | 550
총무팀 | NULL | NULL
(7행)총무팀이 다시 나타났습니다. e.sal > 400 이 이제는 "짝짓기 규칙"의 일부라, 조건에 안 맞아도 dept 쪽 행은 NULL 짝과 함께 살아남습니다.
SELECT
d.dname
, COUNT(*) AS wrong_cnt
FROM dept d
JOIN emp e ON d.code = e.dept
LEFT JOIN orders o ON o.emp_id = e.id
GROUP BY d.dname
ORDER BY d.dname;DNAME | WRONG_CNT
-----------+----------
개발팀 | 3
경영지원팀 | 2
고객지원팀 | 1
영업팀 | 5
인사팀 | 2
(5행)경영지원팀은 직원이 김대표 1명인데 wrong_cnt 는 2입니다. 김대표의 주문이 2건이라, orders 와 조인하면서 같은 직원 행이 주문 건수만큼 늘어났기 때문입니다. 영업팀도 실제 인원 3명인데 5가 나왔습니다.
SELECT
d.dname
, COUNT(DISTINCT e.id) AS emp_cnt
FROM dept d
JOIN emp e ON d.code = e.dept
LEFT JOIN orders o ON o.emp_id = e.id
GROUP BY d.dname
ORDER BY d.dname;DNAME | EMP_CNT
-----------+--------
개발팀 | 3
경영지원팀 | 1
고객지원팀 | 1
영업팀 | 3
인사팀 | 2
(5행)COUNT(DISTINCT e.id) 로 바꾸자 경영지원팀 1, 영업팀 3으로 실제 인원과 같아졌습니다. 같은 e.id 가 주문 건수만큼 중복돼도 DISTINCT 가 한 번만 세 줍니다.