조인 심화: INNER·OUTER·SELF·CROSS, Oracle (+)

4. 응용 변형 예제

소스: sql-src/mid_03_join/02_self_cross.sql, 03_dialects.sql. SELF·CROSS·비등가 조인은 02 파일, DB별 문법 비교는 03 파일입니다.

변형 1: SELF JOIN — 사원과 관리자

SELECT
       e.name AS 직원
     , m.name AS 관리자
  FROM emp e
  LEFT JOIN emp m ON e.mgr = m.id
 ORDER BY e.name;

직원   | 관리자
-------+-------
강팀장 | 김대표
김대표 | NULL
문사원 | 서팀장
박선임 | 이팀장
(총 11행, 뒤 7행 생략, 외주 윤프리도 관리자 NULL)

같은 emp 테이블을 e(직원), m(관리자) 두 별칭으로 읽습니다. e.mgr = m.id 로 자기 자신의 상위 행을 찾는 구조라, 관리자가 없는 김대표·외주인 윤프리는 LEFT JOIN 덕분에 관리자 칸만 NULL 로 남습니다.

변형 2: CROSS JOIN — 부서 x 분기 보고서 틀

SELECT
       d.dname
     , q.q
  FROM dept d
 CROSS JOIN quarter q
 ORDER BY d.dname, q.q;

DNAME      | Q
-----------+------
개발팀     | 1분기
개발팀     | 2분기
개발팀     | 3분기
개발팀     | 4분기
경영지원팀 | 1분기
경영지원팀 | 2분기
(총 24행, 부서마다 4분기씩 반복, 뒤 16행 생략)

부서 6개 x 분기 4개라 6 x 4 = 24행입니다. 조인 조건이 없어 모든 조합이 그대로 나오고, 여기에 분기별 실적을 LEFT JOIN 하면 실적 없는 칸도 빠짐없이 보이는 보고서 틀이 됩니다.

변형 3: 비등가 조인 — 급여 등급표(BETWEEN)

SELECT
       e.name
     , e.sal
     , g.grade
  FROM emp e
  JOIN sal_grade g ON e.sal BETWEEN g.lo AND g.hi
 ORDER BY e.sal DESC;

NAME   | SAL | GRADE
-------+-----+------
김대표 | 900 | 4등급
이팀장 | 650 | 4등급
정팀장 | 600 | 3등급
서팀장 | 550 | 3등급
박선임 | 500 | 3등급
(총 11행, 뒤 6행은 2·1등급으로 이어짐)

= 대신 BETWEEN g.lo AND g.hi 로 급여가 어느 등급 구간에 속하는지 찾았습니다. 등급표처럼 구간을 정의하는 작은 테이블과 큰 테이블을 엮을 때 쓰는 전형적인 비등가 조인입니다.

변형 4: DB별 외부 조인 문법 비교

Oracle · Tibero: 구식 (+) 외부 조인

SET MODE Oracle;
SELECT
       d.dname
     , e.name
  FROM dept d
     , emp e
 WHERE d.code = e.dept(+)
 ORDER BY d.dname, e.name;

예상 오류: Schema "E" not found

d.code = e.dept(+) 는 "emp 가 짝이 없어도 dept 행을 살린다"는 뜻으로, dept LEFT JOIN emp 와 같습니다. H2 는 (+) 표기를 해석하지 못해 위처럼 스키마 오류로 나타나고, 문법 자체는 검토만 했습니다.

실제 Oracle·Tibero 에서도 (+) 는 ANSI LEFT·RIGHT JOIN 으로 바꾸는 것이 권장됩니다. FROM dept d LEFT JOIN emp e ON d.code = e.dept 로 바꿔 실행하면 3절 예제 2의 LEFT JOIN 결과와 같은 11행(총무팀 | NULL 포함)이 나옵니다.

주의

(+) 는 한쪽에만 붙일 수 있어 FULL OUTER JOIN 표현이 안 되고, WHERE 절 조건과 섞어 쓰면 실수하기 쉽습니다. 오래된 코드를 읽을 줄만 알면 되고, 새 코드는 ANSI JOIN 으로 씁니다.

MySQL: FULL OUTER JOIN 미지원

SET MODE MySQL;
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"

실제 MySQL 은 최신 버전까지 FULL OUTER JOIN 문법이 없습니다. 8.0.31 에 추가된 것은 INTERSECT·EXCEPT 이고 FULL JOIN 은 아닙니다. H2 의 MySQL 모드로 확인해도 마찬가지로 문법 오류가 나, 3절 예제 3과 똑같은 LEFT JOIN·RIGHT JOIN UNION 쿼리로 대체하고 결과도 같은 12행입니다.

MSSQL: 구식 *=, =* (문법 검토만)

옛 SQL Server(2000 이전)는 WHERE a.col *= b.col 로 LEFT JOIN, =* 로 RIGHT JOIN 을 표현했습니다. 호환성 수준이 90 이상이면 아예 파싱되지 않을 만큼 완전히 폐지된 문법이라, H2 로도 재현하지 않고 존재만 알아 둡니다. 오래된 SQL Server 코드에서 마주치면 ANSI LEFT JOIN·RIGHT JOIN 으로 바꿔 읽으면 됩니다.

USING 절 지원 비교

SELECT
       dept.code
     , dname
     , head_name
  FROM dept
  JOIN dept_head USING (code)
 ORDER BY dept.code;

dept.code, dept_head.code 처럼 두 테이블의 조인 컬럼 이름이 같으면 ON dept.code = dept_head.code 대신 USING (code) 로 짧게 쓸 수 있습니다. 부서장이 있는 4개 부서(개발·경영·영업·인사)가 나오고, Regular·Oracle·MySQL·MSSQL 네 모드 모두 같은 결과입니다.

주의

실제 MSSQL 은 USING 절을 지원하지 않아 ON 만 써야 합니다. H2 의 MSSQL 모드는 이 제약을 검사하지 않고 그대로 실행하므로, 이 차이는 H2 로 재현되지 않고 문법 검토만 했습니다.