SQL 표준 조인
IT 위키
더 많은 작업
- 상위 문서: SQL
- ANSI/ISO Standard Join
- FROM 절에 JOIN 키워드와 ON·USING 조건으로 조인을 명시하는 ANSI/ISO 표준 SQL 조인 문법
표준 조인은 ANSI/ISO SQL 표준이 정한 조인 작성 방식이다. 전통적인 Oracle 문법은 FROM 절에 테이블을 쉼표로 나열하고 조인 조건을 WHERE 절에 적으며, 외부 조인은 (+) 기호로 표시한다. 표준 조인은 조인 조건을 ON 절에, 행을 거르는 조건을 WHERE 절에 따로 써서 두 가지가 섞이지 않는다. Oracle은 9i부터 표준 조인 문법을 지원하며, SQL Server와 다른 DBMS도 같은 문법을 쓴다. 조인의 일반 개념과 처리 방식은 데이터베이스 조인 문서를 참고한다.
B_EMP (부서번호가 NULL인 한신입은 어느 부서와도 맞지 않는다)
| 사원번호 | 이름 | 부서번호 | 관리자번호 | 급여 |
|---|---|---|---|---|
| 1001 | 김대표 | 10 | NULL | 9000 |
| 1002 | 이부장 | 20 | 1001 | 6000 |
| 1003 | 박과장 | 20 | 1002 | 4500 |
| 1004 | 최대리 | 30 | 1001 | 4000 |
| 1005 | 정사원 | 30 | 1004 | 3000 |
| 1006 | 한신입 | NULL | 1002 | 2500 |
B_DEPT (40번 연구 부서에는 사원이 없다)
| 부서번호 | 부서명 | 지역 |
|---|---|---|
| 10 | 총무 | 서울 |
| 20 | 개발 | 부산 |
| 30 | 영업 | 대전 |
| 40 | 연구 | 서울 |
- B_EMP에는 입사일, 커미션 칼럼도 있으나 이 문서에서는 생략한다. 두 테이블에 공통으로 있는 칼럼은 부서번호 하나이다.
| 조인 | Oracle 전통 문법 | ANSI/ISO 표준 문법 |
|---|---|---|
| 내부 조인 | FROM B_EMP E, B_DEPT D WHERE E.부서번호 = D.부서번호 |
FROM B_EMP E [INNER] JOIN B_DEPT D ON E.부서번호 = D.부서번호
|
| 왼쪽 외부 조인 | WHERE E.부서번호 = D.부서번호(+) |
B_EMP E LEFT [OUTER] JOIN B_DEPT D ON ...
|
| 오른쪽 외부 조인 | WHERE E.부서번호(+) = D.부서번호 |
B_EMP E RIGHT [OUTER] JOIN B_DEPT D ON ...
|
| 완전 외부 조인 | 직접 표현 불가(양쪽 (+) 금지) | B_EMP E FULL [OUTER] JOIN B_DEPT D ON ...
|
| 카테시안 곱 | FROM B_EMP, B_DEPT(조인 조건 없음) |
B_EMP CROSS JOIN B_DEPT
|
(+)는 데이터가 없어 NULL로 채워질 쪽, 즉 기준 테이블의 반대쪽 칼럼에 붙인다.- INNER와 OUTER는 생략할 수 있다.
JOIN만 쓰면 내부 조인이다.
SELECT E.이름, D.부서명
FROM B_EMP E INNER JOIN B_DEPT D
ON E.부서번호 = D.부서번호
ORDER BY E.사원번호;
결과는 5행이다(김대표 총무, 이부장 개발, 박과장 개발, 최대리 영업, 정사원 영업). 부서번호가 NULL인 한신입과 사원이 없는 40번 부서는 빠진다. ON 절에는 등호가 아닌 조건도 쓸 수 있다(데이터베이스 세타 조인).
두 테이블에서 이름이 같은 칼럼 가운데 조인에 쓸 칼럼만 골라 동등 조인한다.
SELECT 부서번호, E.이름, D.부서명
FROM B_EMP E JOIN B_DEPT D USING (부서번호); -- 5행
SELECT E.부서번호 FROM B_EMP E JOIN B_DEPT D USING (부서번호);
-- ORA-25154: column part of USING clause cannot have qualifier
- USING에 쓴 칼럼은 결과에 한 번만 나오며, 테이블 이름이나 별칭을 접두사로 붙일 수 없다. 다른 칼럼에는 접두사를 붙일 수 있다.
두 테이블에서 이름이 같은 칼럼을 모두 찾아 동등 조인한다(데이터베이스 자연 조인). ON이나 USING 절을 쓰지 않는다.
SELECT * FROM B_EMP NATURAL JOIN B_DEPT ORDER BY 사원번호;
| 부서번호 | 사원번호 | 이름 | 관리자번호 | 급여 | 입사일 | 커미션 | 부서명 | 지역 |
|---|---|---|---|---|---|---|---|---|
| 10 | 1001 | 김대표 | NULL | 9000 | 2015-01-05 | NULL | 총무 | 서울 |
| 20 | 1002 | 이부장 | 1001 | 6000 | 2017-03-15 | NULL | 개발 | 부산 |
| 20 | 1003 | 박과장 | 1002 | 4500 | 2019-07-01 | NULL | 개발 | 부산 |
| 30 | 1004 | 최대리 | 1001 | 4000 | 2020-11-20 | 500 | 영업 | 대전 |
| 30 | 1005 | 정사원 | 1004 | 3000 | 2022-02-28 | 0 | 영업 | 대전 |
SELECT *의 칼럼 순서는 공통 칼럼(부서번호)이 맨 앞에 한 번, 그다음 왼쪽 테이블의 나머지 칼럼, 오른쪽 테이블의 나머지 칼럼 순이다.- 공통 칼럼에는 접두사를 붙일 수 없다.
SELECT E.부서번호 ... NATURAL JOIN은 ORA-25155 오류이다. 공통이 아닌 칼럼(E.이름)에는 붙일 수 있다. - 이름이 같은 칼럼이 의도치 않게 더 있으면 그 칼럼도 조건에 들어가므로 결과가 달라진다. 공통 칼럼의 데이터 형식도 같아야 한다.
조인 조건 없이 두 테이블의 모든 행을 짝짓는 카테시안 곱(곱집합)이다. B_EMP 6행 × B_DEPT 4행 = 24행이다. Oracle 문법에서 조인 조건을 빠뜨린 FROM B_EMP, B_DEPT도 24행이 된다.
조인 조건에 맞지 않는 행도 결과에 남기고, 반대쪽 칼럼은 NULL로 채운다.
SELECT E.이름, D.부서번호, D.부서명
FROM B_EMP E LEFT OUTER JOIN B_DEPT D ON E.부서번호 = D.부서번호; -- (1)
SELECT E.이름, D.부서번호, D.부서명
FROM B_EMP E RIGHT OUTER JOIN B_DEPT D ON E.부서번호 = D.부서번호; -- (2)
SELECT E.이름, E.부서번호, D.부서번호, D.부서명
FROM B_EMP E FULL OUTER JOIN B_DEPT D ON E.부서번호 = D.부서번호; -- (3)
| 조인 | 행 수 | 내부 조인 결과(5행)에 더해지는 행 |
|---|---|---|
| (1) LEFT OUTER | 6 | 한신입, NULL, NULL |
| (2) RIGHT OUTER | 6 | NULL, 40, 연구 |
| (3) FULL OUTER | 7 | 한신입 행과 40번 연구 행 둘 다 |
- Oracle 문법의
WHERE E.부서번호 = D.부서번호(+)는 (1)과 같은 6행이다. - 완전 외부 조인은 데이터베이스 Full Outer Join 문서를 참고한다.
외부 조인에서 ON 절의 조건은 "어떤 행과 짝지을지"를 정하고, WHERE 절의 조건은 조인이 끝난 결과에서 행을 거른다. 내부 조인에서는 둘의 결과가 같지만 외부 조인에서는 다르다.
-- (A) 조건을 ON 절에
SELECT D.부서번호, D.부서명, E.이름, E.급여
FROM B_DEPT D LEFT OUTER JOIN B_EMP E
ON D.부서번호 = E.부서번호 AND E.급여 >= 4500;
-- (B) 조건을 WHERE 절에
SELECT D.부서번호, D.부서명, E.이름, E.급여
FROM B_DEPT D LEFT OUTER JOIN B_EMP E
ON D.부서번호 = E.부서번호
WHERE E.급여 >= 4500;
| 부서번호 | 부서명 | (A) ON 절 조건 | (B) WHERE 절 조건 |
|---|---|---|---|
| 10 | 총무 | 김대표 9000 | 김대표 9000 |
| 20 | 개발 | 이부장 6000 | 이부장 6000 |
| 20 | 개발 | 박과장 4500 | 박과장 4500 |
| 30 | 영업 | NULL, NULL | (행 없음) |
| 40 | 연구 | NULL, NULL | (행 없음) |
| 행 수 | 5 | 3 | |
- (A)에서 30번 부서는 급여 4500 이상인 사원이 없어 짝이 없지만, 왼쪽 테이블 행이므로 NULL로 채워 남는다.
- (B)에서는 조인 결과의 E.급여가 NULL인 행이 WHERE 조건에서 UNKNOWN이 되어 버려진다. 결과적으로 내부 조인과 같아진다.
- Oracle 문법에서는 (A)를
WHERE D.부서번호 = E.부서번호(+) AND E.급여(+) >= 4500로, (B)를(+)없이AND E.급여 >= 4500으로 쓴다. 실행 결과는 각각 5행, 3행으로 같다. - 기준 테이블(왼쪽) 쪽 조건을 ON에 넣으면 행이 줄지 않는다.
ON D.부서번호 = E.부서번호 AND D.지역 = '서울'은 부서 4행을 모두 남기고, 서울이 아닌 부서에는 사원을 붙이지 않는다(10 총무 김대표, 나머지 3개 부서는 NULL).
| 조인 | 계산 | 행 수 |
|---|---|---|
| CROSS JOIN | 6 × 4 | 24 |
| INNER JOIN (부서번호) | 짝이 맞는 사원 5명 | 5 |
| B_EMP LEFT OUTER JOIN B_DEPT | 내부 조인 5 + 짝 없는 사원 1(한신입) | 6 |
| B_EMP RIGHT OUTER JOIN B_DEPT | 내부 조인 5 + 짝 없는 부서 1(40번) | 6 |
| FULL OUTER JOIN | 내부 조인 5 + 1 + 1 | 7 |
- 한 조건의 양쪽에
(+)를 붙일 수 없다.WHERE E.부서번호(+) = D.부서번호(+)는 ORA-01468(a predicate may reference only one outer-joined table) 오류이다. 완전 외부 조인은 FULL OUTER JOIN 표준 문법으로 쓴다. 전통 문법만으로는 왼쪽·오른쪽 외부 조인을 UNION으로 합쳐 흉내 낼 수 있으나, UNION이 중복 행을 없애므로 원래 중복된 행이 있으면 결과가 달라질 수 있다. - 조인 조건이 여러 개면 기준 반대쪽 테이블의 모든 조건에
(+)를 붙여야 한다. 하나라도 빠뜨리면 외부 조인이 내부 조인처럼 동작한다(위 (B)와 같은 효과). - Oracle은 새로 작성하는 SQL에
(+)대신 표준 OUTER JOIN 문법을 쓰도록 권장한다.
- USING과 NATURAL JOIN의 공통 칼럼에는 별칭·테이블 접두사를 붙일 수 없다(ORA-25154, ORA-25155).
- NATURAL JOIN은 이름이 같은 칼럼 전부로 동등 조인하며,
SELECT *에서 공통 칼럼이 맨 앞에 한 번 나온다. - 외부 조인에서 안쪽(NULL이 채워지는 쪽) 테이블 조건을 WHERE에 쓰면 내부 조인처럼 행이 줄어든다. ON 절 조건과 결과 행 수 차이를 묻는다.
- CROSS JOIN 행 수 = 두 테이블 행 수의 곱. LEFT·RIGHT·FULL OUTER JOIN의 결과 행 수 계산
- Oracle
(+)와 ANSI 문법의 대응, 양쪽(+)불가