ANSI/ISO 표준 조인과 일반 조인 비교(INNER JOIN, NATURAL JOIN, USING 조건절, ON 조건절, CROSS JOIN, OUTER JOIN)

ANSI/ISO 표준 조인과 일반 조인 비교(INNER JOIN, NATURAL JOIN, USING 조건절, ON 조건절, CROSS JOIN, OUTER JOIN)

 

FROM 절 조인 형태

 

ANSI/ISO 표준 SQL에서 표시하는 FROM 절의 조인의 종류는 다음과 같습니다.

 

- INNER JOIN
- NATURAL JOIN
- USING 조건절
- ON 조건절
- CROSS JOIN
- OUTER JOIN

 

ANSI/ISO SQL에서 규정한 조인 문법은 WHERE 절에 조인 조건을 기술하는 전통적인 방식인 조인 문법과 차이가 있습니다. 사용자는 기존 WHERE 절의 검색 조건과 테이블 간의 조인 조건을 구분 없이 사용하던 방식을 그대로 사용하면서, 추가된 선택 기능으로 테이블 간의 조인 조건을 FROM 절에 명시적으로 정의할 수 있습니다.

 

INNER JOIN

 

INNER JOIN은 조인의 DEFAULT 옵션으로 조인조건을 만족하는 행만을 반환합니다.

 

-- WHERE 절 조인 조건
SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A, DEPT B
WHERE B.DEPTNO = A.DEPTNO;

-- FROM 절 조인 조건
SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A INNER JOIN DEPT B
ON B.DEPTNO = A.DEPTNO;

 

DEFAULT 옵션이므로 INNER 를 생략해서 아래와 같이 표현할 수 있습니다.

 

SELECT A.EMPLOYEE_ID , B.DEPARTMENT_NAME
FROM HR.EMPLOYEES A JOIN HR.DEPARTMENTS B
ON A.DEPARTMENT_ID  = B.DEPARTMENT_ID;

 

NATURAL JOIN

 

두 테이블 간에 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN을 수행합니다. NATURAL JOIN을 명시하면 추가로 USING 조건절, ON 조건절, WHERE 절에서 조인 조건을 정의할 수 없습니다.

 

-- 사원 번호와 사원 이름, 소속부서 번호와 소속부서 이름을 찾아본다.
SELECT A.EMPNO,A.ENAME,B.DNAME,DEPTNO
FROM EMP A NATURAL JOIN DEPT B;

 

별도의 조인 컬럼을 지정하지 않았지만, 두 개의 테이블에서 DEPTNO라는 공통 칼럼을 자동으로 인식해 조인을 처리합니다. 조인에 사용된 칼럼들은 같은 데이터 유형이어야 하며 ALIAS나 테이블 명과 같은 접두사를 붙일 수 없습니다.

 

-- 조인의 조건이 되는 DEPARTMENT_ID에 테이블명을 표기하며 오류가 발생한다.
-- ORA-25155: column used in NATURAL join cannot have qualifier

SELECT A.EMPLOYEE_ID, B.DEPARTMENT_NAME, A.DEPARTMENT_ID
FROM HR.EMPLOYEES A NATURAL JOIN HR.DEPARTMENTS B

 

별도의 칼럼 순서를 지정하지 않으면 NATURAL JOIN의 기준이 되는 칼럼들이 다른 칼럼보다 먼저 출력됩니다. 이때 NATURAL JOIN은 조인에 사용된 같은 이름의 칼럼을 하나로 처리합니다.

 

-- 조인의 조건으로 사용된 DEPTNO가 가장 먼저 출력된다
SELECT * FROM EMP A NATURAL JOIN DEPT B;

 

INNER JOIN의 경우 첫 번째 테이블, 두 번째 테이블의 칼럼 순서대로 데이터가 출력됩니다. 이때 NATURAL JOIN은 조인에 사용된 같은 이름의 칼럼을 하나로 처리하지만 INNER JOIN은 별개의 칼럼으로 표시합니다

 

SELECT *
FROM EMP A INNER JOIN DEPT B
ON B.DEPTNO = A.DEPTNO

 

NATURAL JOIN 과 INNER JOIN 비교

 

-- NATURAL JOIN 과 INNER JOIN의 차이를 알아보기 위해 DEPT_TEMP 테이블을 임시로 만든다.
CREATE TABLE DEPT_TEMP AS SELECT * FROM DEPT

-- 임시 테이블 중 DNAME이 RESEARCH 인 레코드의 DNAME을 CONSULTING으로 변경합니다.

UPDATE DEPT_TEMP
SET DNAME = 'CONSULTING'
WHERE DNAME = 'RESEARCH';

-- 임시 테이블 중 DNAME이 SALES 인 레코드의 DNAME을 MARKETING으로 변경합니다.

UPDATE DEPT_TEMP
SET DNAME = 'MARKETING'
WHERE DNAME = 'SALES';

-- 부서번호가 20 또는 30인 레코드의 DNAME이 각각 'CONSULTING' 과 'MARKETING' 으로 변경된 것을 확인할 수 있습니다.
SELECT * FROM DEPT_TEMP;

-- 세 개의 칼럼명이 모두 같은 DEPT와 DEPT_TEMP 테이블을 NATURAL INNER JOIN 으로 수행합니다.
-- DNAME의 내용이 바뀐 부서번호 20, 30 의 데이터는 조인 실행 결과에서 제외됩니다.
SELECT * FROM DEPT A NATURAL INNER JOIN DEPT_TEMP B

-- 같은 조건이지만 출력 칼럼에서 차이가 나는 일반적인 INNER JOIN을 수행합니다.
-- DNAME의 내용이 바뀐 부서번호 20, 30 의 데이터는 실행 결과에서 제외됩니다.
-- NATURAL JOIN은 조인에 사용된 같은 이름의 칼럼을 하나로 처리하지만 INNER JOIN은 2개의 칼럼으로 표시합니다.

SELECT *
FROM DEPT A JOIN DEPT_TEMP B
ON B.DEPTNO = A.DEPTNO
AND B.DNAME = A.DNAME
AND B.LOC = A.LOC;

 

USING 조건절

 

NATURAL JOIN에서는 같은 이름을 가진 모든 칼럼에 대해 조인하지만, FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있습니다.

 

-- 세 개의 컬럼명이 모두 같은 DEPT와 DEPT_TEMP 테이블의 DEPTNO 칼럼을 이용한 INNER JOIN의 USING 조건절로 수행합니다.
-- 위 SQL의 * 처럼 별도의 컬럼 순수를 지정하지 않으면 USING 조건절의 기준이 되는 컬럼이 다른 컬럼보다 먼저 출력됩니다.
-- 이때 USING JOIN은 조인에 사용된 같은 이름의 칼럼을 하나로 처리합니다.

SELECT *
FROM DEPT A JOIN DEPT_TEMP B
USING (DEPTNO)

 

USING 조건절을 이용한 EQUI JOIN에서도 NATURAL JOIN과 마찬가지로 조인 칼럼에 대해서는 ALIAS나 테이블이름과 같은 접두사를 붙일 수 없다.

 

-- (A.DEPTNO => DEPTNO)

SELECT A.DEPTNO, A.DNAME, A.LOC, B.DNAME, B.LOC
FROM DEPT A JOIN DEPT_TEMP B
USING (DEPTNO);

SELECT DEPTNO, A.DNAME, A.LOC, B.DNAME, B.LOC
FROM DEPT A JOIN DEPT_TEMP B
USING (DEPTNO);

-- 이번에는 DEPT와 DEPT_TEMP 테이블의 일부 데이터 내용이 변경됐던 DNAME 칼럼을 조인 조건으로 INNER JOIN의 USING 조건절을 수행한다.
-- 위 SQL의 경우 DNAME의 내용이 바뀐 부서번호 20,30 의 경우는 결과에서 제외된 것을 알 수 있다. 그리고 USING 조건절에 사용된 DNAME이 첫 번째 칼럼으로 출력된 것과 함께 조인 조건에 참여하지 않은 DEPTNO와  LOC가 2개의 칼럼으로 표시됐다.

SELECT *
FROM DEPT A JOIN DEPT_TEMP B
USING (DNAME);

-- 이번에는 세 개의 컬럼명이 모두 같은 DEPT와 DEPT_TEMP 테이블을 LOC, DEPTNO 2개 컬럼을 이용해 INNER JOIN의 USING 조건절로 수행한다.
-- USING에 사용된 LOC 와 DEPTNO가 첫 번째, 두 번째 칼럼으로 출력, 조인에 참여하지 않은 DNAME은 2개의 칼럼으로 출력된 것을 알 수 있다.

SELECT *
FROM DEPT A JOIN DEPT_TEMP B
USING(LOC, DEPTNO);

-- 이번에는 DEPTNO, DNAME 2개의 칼럼을 이용해 INNER JOIN의 USING 조건절로 수행한다.
-- 위 SQL 의 경우 DNAME의 내용이 바뀐 부서번호 20,30 의 경우는 결과에서 제외된다.
-- USING에 사용된 DEPTNO 와 DNAME이 각각 첫 번째, 두 번째 칼럼으로 출력, 조인에 참여하지 않은 LOC가 2개의 칼럼으로 출력된 것을 알 수 있따.
SELECT *
FROM DEPT A JOIN DEPT_TEMP B
USING(DEPTNO, DNAME);

 

ON 조건절

 

조인 서술부(ON 조건절)을 활용한 표준조인은 비 조인 서술부 (WHERE 조건절)와 조인조건절이 분리되어 이해가 쉬우며, 칼럼명이 다르더라도 조인 조건에 사용할 수 있는 장점이 있습니다.

 

-- 사원 테이블과 부서 테이블에서 사원 번호와 사원 이름, 소속부서 코드, 소속부서 이름을 출력한다.
SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A JOIN DEPT B
ON B.DEPTNO = A.DEPTNO;

 

NATURAL JOIN의 조인 조건은 기본적으로 같은 이름을 가진 모든 칼럼들에 대한 동등 조건입니다. 하지만 임의의 조인 조건을 지정하고, 이름이 다른 칼럼명을 조인 조건으로 이용하고, 조인 칼럼명을 명시하기 위해서는 ON 조건절을 사용합니다.

USING 조건절을 이용한 조인에서는 조인 칼럼에 대해 ALIAS나 테이블 명과 같은 접두사를 사용하면 SYNTAX 에러가 발생하지만, ON 조건절을 사용한 조인의 경우 이름이 같은 칼럼들에 대해 ALIAS나 테이블 명과 같은 접두사를 명확하게 지정해주어야 합니다.

 

ON 조건절은 WHERE 절의 조인 조건과 같은 기능을 하면서도, 명시적으로 조인의 조건을 구분할 수 있습니다. 다만 많은 테이블을 조인할 경우 가독성이 떨어지는 단점이 있습니다.

 

WHERE 절과의 혼용

 

ON 조건절과 WHERE 검색 조건은 충돌 없이 사용할 수 있습니다.

 

-- 부서번호 30인 부서의 소속 사원 이름 및 소속 부서번호, 부서 번호, 부서 이름을 검색합니다.

SELECT A.ENAME, A.DEPTNO, B.DEPTNO, B.DNAME
FROM EMP A JOIN DEPT B
ON B.DEPTNO = A.DEPTNO
WHERE B.DEPTNO = 30;

 

ON 조건절 예제

 

-- 팀과 경기장 테이블을 경기장 ID로 조인해 팀이름, 경기장 id, 경기장명을 검색합니다.

SELECT A.TEAM_NAME, A.STADIUM_ID, B.STADIUM_NAME
FROM TEAM A JOIN STADIUM B
ON B.STADIUM_ID = A.STADIUM_ID
ORDER BY A. STADIUM_ID;

-- STADIUM_ID 라는 공통 컬럼이 있기 때문에 아래처럼 USING 조건절로도 구현할 수 있습니다.
SELECT A.TEAM_NAME, A.STADIUM_ID, B.STADIUM_NAME
FROM TEAM A JOIN STADIUM B
USING (STADIUM)
ORDER BY A. STADIUM_ID;

-- 팀과 경기장 테이블을 팀 ID로 조인해 팀이름, 팀ID, 경기장명을 검색합니다
-- 조인 조건으로 TEAM_ID 와 HOMETEAM_ID 라는 다른 이름의 칼럼을 사용하기 때문에 USING 조건절을 사용할 수는 없다.
SELECT A.TEAM_NAME, A.STADIUM_ID, B.STADIUM_NAME
FROM TEAM A JOIN STADIUM B
ON B.HOMETEAM_ID = A.TEAM_ID
ORDER BY A.TEAM_ID;

 

다중 테이블 조인

 

2개 이상의 테이블을 표준조인으로 조인하는 방식은 다음과 같습니다.

 

-- EMP 테이블의 사원번호를 기준으로 DEPT 테이블의 소속 부서명, DEPT_TEMP 테이블의 바뀐 부서명 정보를 출력합니다.

SELECT A.EMPNO, A.DEPTNO, B.DNAME, C.DNAME AS NEW_DNAME
FROM EMP A JOIN DEPT B
ON B.DEPTNO = A.DEPTNO
JOIN DEPT_TEMP C
ON C.DEPTNO = B.DEPTNO

 

전통적인 방식의 WHERE 절의 INNER JOIN 방식으로도 구현할 수 있습니다.

 

-- WHERE 절의 INNER JOIN
SELECT A.EMPNO, A.DEPTNO, B.DNAME, C.DNAME AS NEW_DNAME
FROM EMP A, DEPT B, DEPT_TEMP C
WHERE B.DEPTNO = A.DEPTNO
AND C.DEPTNO = B.DEPTNO;

-- GK 포지션의 선수별 연고지명, 팀명, 구장명을 출력한다.
-- 표준조인
SELECT A.PLAYER_NAME, A.POSITION, B.REGION_NAME, B.TEAM_NAME, C.STADIUM_NAME
FROM PLAYER A JOIN TEAM B
ON B.TEAM_ID = A.TEAM_ID
JOIN STADIUM C
ON C.STADIUM_ID = B.STADIUM_ID
WHERE A.POSITION = 'GK'
ORDER BY 선수명;

-- WHERE 절의 INNER JOIN
SELECT A.PLAYER_NAME, A.POSITION, B.REGION_NAME, B.TEAM_NAME, C.STADIUM_NAME
FROM PLAYER A, TEAM B, STADIUM C
WHERE A.POSITION = 'GK'
AND B.TEAM_ID = A.TEAM_ID
AND C.STADIUM_ID = B.STADIUM_ID
ORDER BY 선수명;

-- 홈팀이 3점 이상 차이로 승리한 경기의 경기장 이름, 경기 일정 ,홈팀 이름과 원정팀 이름 정보를 출력한다.
-- 표준조인
SELECT B.STADIUM_NAME, B.STADIUM_ID, A.SCHE_DATE
, C.TEAM_NAME, D.TEAM_NAME,
, A.HOME_SCORE, A.AWAY_SCORE
FROM SCHEDULE A
JOIN STADIUM B
ON B.STADIUM_ID = A.STADIUM_ID
JOIN TEAM C
ON C.TEAM_ID = A.HOMETEAM_ID
JOIN TEAM D
ON D.TEAD_ID = A.AWAYTEAM_ID
WHERE A.HOME_SCORE >= A.AWAY+SCORE + 3

-- 전통적인 방식의 WHERE 절의 INNER JOIN 으로 구현할 수도 있다.
--  WHERE 절의 INNER JOIN
SELECT B.STADIUM_NAME, B.STADIUM_ID, A.SCHE_DATE
, C.TEAM_NAME, D.TEAM_NAME,
, A.HOME_SCORE, A.AWAY_SCORE
FROM SCHEDULE A, STADIUM B, TEAM C, TEAM D
WHERE 1=1
AND A.HOME_SCORE >= A.AWAY+SCORE + 3
AND  B.STADIUM_ID = A.STADIUM_ID
AND C.TEAM_ID = A.HOMETEAM_ID
AND D.TEAD_ID = A.AWAYTEAM_ID

CROSS JOIN

 

CROSS JOIN은 일반 집합 연산자의 PRODUCT 개념으로 테이블 간 조인 조건이 없는 경우 생길수 있는 모든 데이터의 조합을 말합니다. 두 개의 테이블에 대한 CARTESIAN PRODUCT 또는 CROSS PRODUCT와 같은 표현으로, 결과는 양쪽 집합의 M*N건의 데이터 조합이 발생합니다.

 

-- 실행 결과에서 모든 사원 각각은 DEPT의 모든 부서명과 연결됩니다.
SELECT A.ENAME, B.DNAME
FROM EMP A CROSS JOIN DEPT B
ORDER BY A.ENAME

 

NATURAL JOIN의 경우 WHERE 절에서 조인 조건을 추가할 수 없지만, CROSS JOIN의 경우 WHERE 절에 조인 조건을 추가할 수 있습니다. 그러나, 이 경우 조인조건을 한정하여 CROSS JOIN이 아니라 INNER JOIN과 같은 결과를 얻기 때문에 권고하지 않습니다.

 

SELECT A.ENAME, B.DNAME
FROM EMP A CROSS JOIN DEPT B
WHERE B.DEPTNO = A.DEPTNO

 

 

OUTER JOIN

 

전통적인 방식의 OUTER 조인 문법에서 ORACLE은 조인대상 컬럼 뒤에 (+)를 표시합니다. 이 방식은 조인 조건과 WHERE 절 검색 조건의 구분이 불명확하다는 단점, IN이나 OR 연산자 사용 시 에러 발생, (+) 누락 시 INNER JOIN으로 수행, FULL OUTER JOIN 미지원 등 불편함이 있습니다.

 

ANSI/ISO SQL 표준 방식의 OUTER JOIN 문법을 사용하면 위에서 언급한 문제점들을 해결할 수 있습니다. OUTER JOIN 역시 조인 조건을 FROM 절에서 정의하겠다는 의미이므로 조인조건에 대해 USING 조건절이나 ON 조건절을 필수적으로 사용해야 합니다.

 

LEFT OUTER JOIN

 

조인 수행 시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 조인 대상 데이터를 읽어옵니다.

 

TABLE A와 B가 있을 때, A와 B를 비교해서 B의 조인 칼럼에 조인 조건을 만족하는 값이 있으면 해당 로우의 데이터를 가져오고, 조인 조건을 만족하는 값이 없는 경우 B 테이블에서 가져오는 칼럼들은 NULL 값으로 채웁니다. LEFT JOIN으로 OUTER 키워드를 생략해서 사용할 수 있습니다.

 

-- STADIUM 에 등록된 경기장 중에는 홈팀이 없는 경기장도 있다. STADIUM과 TEAM을 조인하되 홈팀이 없는 경기장의 정보도 같이 출력하도록 한다.

SELECT A.STADIUM_NAME, A.STADIUM_ID, A.SEAT_COUNT, A.HOMETEAM_ID, B.TEAM_NAME
FROM STADIUM A LEFT OUTER JOIN TEAM B
ON B.TEAM_ID = A.HOMETEAM_ID
ORDER BY A.HOMETEAM_ID;

-- OUTER는 생략가능하다.
SELECT A.STADIUM_NAME, A.STADIUM_ID, A.SEAT_COUNT, A.HOMETEAM_ID, B.TEAM_NAME
FROM STADIUM A LEFT JOIN TEAM B
ON B.TEAM_ID = A.HOMETEAM_ID
ORDER BY A.HOMETEAM_ID;

 

 

RIGHT OUTER JOIN

 

조인 수행 시 LEFT JOIN과 반대로 우측 테이블을 기준으로 좌측 테이블과 OUTER JOIN 한 결과를 생성합니다.

 

TABLE A와 B가 있을 때, A와 B를 비교해서 A 의 조인 칼럼에 조인 조건을 만족하는 값이 있으면 해당 로우의 데이터를 가져오고, 조인 조건을 만족하는 값이 없는 경우 A 테이블에서 가져오는 칼럼들은 NULL 값으로 채웁니다. RIGHT JOIN으로 OUTER 키워드를 생략해서 사용할 수 있습니다.

 

SELECT A.EANME, B.DEPTNO, B.DNAME, B.LOC
FROM EMP A RIGHT OUTER JOIN DEPT B
ON B.DEPTNO = A.DEPTNO;

 

FULL OUTER JOIN

 

조인 수행 시 좌측, 우측 테이블의 모든 데이터를 읽어 조인해 결과를 생성합니다. TABLE A와 B가 있을때, RIGHT OUTER JOIN, LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일합니다. 단 조인에 성공한 행들은 한 번만 표시합니다.

 

-- DEPT 테이블과 DEPT_TEMP 테이블의 FULL OUTER JOIN 사례를 만들기 위해 DEPT_TEMP 의 DEPTNO를 수정한다.
-- 결과적으로 DEPT_TMEP 테이블의 새로운 DEPTNO 컬럼 값은 DEPT 테이블의 기존 DEPTNO 컬럼 값과 서로 2건은 동일하고 2건은 다르다.

UPDTAE DEPT_TEMP
SET DEPTNO = DEPTNO + 20;

-- DEPTNO 를 기준으로 DEPT와 DEPT_TEMP 데이터르 FULL OTER JOIN 으로 출력한다.

SELECT *
FROM DEPT A FULL OUTER JOIN DEPT_TEMP B
ON B.DEPTNO = A.DEPTNO;