Oracle_조인

    두 개 이상의 테이블 들을 연결 또는 결합하여 데이터를 출력하는 것을 JOIN이라고 하며, 일반적으로 사용되는 SQL 문장의 상당수가 JOIN이다. JOIN은 관계형 데이터베이스의 가장 큰 장점이면서 대표적인 핵심 기능이라고 할 수 있다. 일반적인 경우 행들은 PRIMARY KEY(PK)나 FOREIGN KEY(FK) 값의 연관에 의해 JOIN이 성립된다. 하지만 어떤 경우에는 이러한 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립 가능하다.

 

□ INNER JOIN

    INNER JOIN은 OUTER(외부) JOIN과 대비하여 내부 JOIN이라고 하며 JOIN 조건에서 동일한 값이 있는 행만 반환한다. INNER JOIN 표시는 그 동안 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다.

    INNER JOIN은 EQUI JOIN(등가 조인)과 Non EQUI JOIN (비등가 조인) 두 가지가 있다.

 

○ EQUI JOIN(NATURAL JOIN, 등가 조인)

    EQUI JOIN은 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용하는 방법. JOIN 조건은 WHERE 절에 기술하게 되는데 "=" 연산자를 사용해서 표현.

SELECT 테이블1.칼럼명, 
       테이블2.칼럼명, ... 
FROM 테이블1, 테이블2 
WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2; -- WHERE 절에 JOIN 조건 삽입

SELECT 테이블1.칼럼명, 
       테이블2.칼럼명, 
       NATURAL INNER JOIN에 사용된 칼럼, -- NATURAL JOIN에 사용된 열은 식별자를 가질 수 없음
       ... 
FROM 테이블1, NATURAL JOIN 테이블2;

SELECT 테이블1.칼럼명, 
       테이블2.칼럼명, 
       NATURAL JOIN에 사용된 칼럼, -- INNER는 JOIN의 티폴트 옵션으로 생략 가능
       ... 
FROM 테이블1, NATURAL JOIN 테이블2;

SELECT 테이블1.칼럼명, 
       테이블2.칼럼명, ... 
FROM 테이블1 INNER JOIN 테이블2 
ON 테이블1.칼럼명1 = 테이블2.칼럼명2; -- ON 절에 JOIN 조건 삽입(ANSI/ISO SQL 표준 방식)

SELECT 테이블1.칼럼명, 
       테이블2.칼럼명, ... 
FROM 테이블1 JOIN 테이블2 -- INNER는 JOIN의 티폴트 옵션으로 생략 가능
ON 테이블1.칼럼명1 = 테이블2.칼럼명2;

    위 SQL을 보면 SELECT 구문에 단순히 칼럼명이 오지 않고 “테이블명.칼럼명”처럼 테이블명과 칼럼명이 같이 나타난다. 이렇게 특정 칼럼에 접근하기 위해 그 칼럼이 어느 테이블에 존재하는 칼럼인지를 명시하는 것은 두 가지 이유가 있다. 먼저 모든 테이블에 칼럼들이 유일한 이름을 가진다면 상관없지만, JOIN에 사용되는 두 개의 테이블에 같은 칼럼명이 존재하는 경우에는 DBMS의 옵티마이저는 어떤 칼럼을 사용해야 할지 모르기 때문에 파싱 단계에서 에러가 발생된다.

    두 번째는 개발자나 사용자가 조회할 데이터가 어느 테이블에 있는 칼럼을 말하는 것인지 쉽게 알 수 있게 하므로 SQL에 대한 가독성이나 유지보수성을 높이는 효과가 있다. 하나의 SQL 문장 내에서 유일하게 사용하는 칼럼명이라면 칼럼명 앞에 테이블 명을 붙이지 않아도 되지만, 현재 두 집합에서 유일하다고 하여 미래에도 두 집합에서 유일하다는 보장은 없기 때문에 향후 발생할 오류를 방지하고 일관성을 위해 유일한 칼럼도 출력할 칼럼명 앞에 테이블명을 붙여서 사용하는 습관을 기르는 것을 권장한다.

    조인 조건에 맞는 데이터만 출력하는 INNER JOIN에 참여하는 대상 테이블이 N개라고 했을 때, N개의 테이블로부터 필요한 데이터를 조회하기 위해 필요한 JOIN 조건은 대상 테이블의 개수에서 하나를 뺀 N-1개 이상이 필요하다. 즉 FROM 절에 테이블이 3개가 표시되어 있다면 JOIN 조건은 3-1=2개 이상이 필요하며, 테이블이 4개가 표시되어 있다면 JOIN 조건은 4-1=3개 이상이 필요하다.

 

[예제] 선수 테이블과 팀 테이블에서 선수 이름과 소속된 팀의 이름을 출력하시오.

SELECT PLAYER.PLAYER_NAME AS 선수명, 
       TEAM.TEAM_NAME     AS 팀명
FROM PLAYER, TEAM
WHERE TEAM.TEAM_ID = PLAYER.TEAM_ID;

SELECT PLAYER.PLAYER_NAME AS 선수명, 
       TEAM.TEAM_NAME     AS 팀명
FROM PLAYER NATURAL JOIN TEAM;

-- ANSI/ISO SQL 표준 방식
SELECT PLAYER.PLAYER_NAME AS 선수명, 
       TEAM.TEAM_NAME     AS 팀명
FROM PLAYER INNER JOIN TEAM
ON TEAM.TEAM_ID = PLAYER.TEAM_ID;

-- ANSI/ISO SQL 표준 방식
SELECT PLAYER.PLAYER_NAME AS 선수명, 
       TEAM.TEAM_NAME     AS 팀명
FROM PLAYER JOIN TEAM -- INNER는 JOIN의 티폴트 옵션으로 생략 가능
ON TEAM.TEAM_ID = PLAYER.TEAM_ID;

 

[예제] 포지션이 ‘GK’인 선수들에 대한 데이터만을 'BACK_NO'순으로 출력하는 SQL문을 만들어 본다.

SELECT PLAYER.PLAYER_NAME AS 선수명,
       TEAM.TEAM_NAME     AS 팀명,
       PLAYER.TEAM_ID     AS 팀ID,
       PLAYER.BACK_NO     AS 등번호
FROM PLAYER, TEAM
WHERE PLAYER.POSITION = 'GK' 
AND PLAYER.TEAM_ID = TEAM.TEAM_ID
ORDER BY PLAYER.BACK_NO;

 

[예제] 아래 '*' 와일드카드처럼 별도의 칼럼 순서를 지정하지 않으면 NATURAL JOIN의 기준이 되는 칼럼 들이 다른 칼럼보다 먼저 출력된다. (ex: DEPTNO가 첫 번째 칼럼이 된다.) 이때 NATURAL JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리한다.

SELECT * FROM EMP NATURAL JOIN DEPT;

 

[예제] 반면, INNER JOIN의 경우 첫 번째 테이블, 두 번째 테이블의 칼럼 순서대로 데이터가 출력된다. 이때 NATURAL JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리하지만, INNER JOIN은 별개의 칼럼으로 표시한다.

SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

 

 

○ Non EQUI JOIN (비등가 조인)

    두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용하고 “=” 연산자가 아닌 다른(Between, >, >=, <, <= 등) 연산자들을 사용하여 JOIN을 수행한다. 예를 들기 위해 급여등급 테이블을 하나 생성하자.

CREATE TABLE SALGRADE
(
    GRADE NUMBER(5) PRIMARY KEY,
    LOSAL NUMBER(5),
    HISAL NUMBER(5)
);

INSERT INTO SALGRADE VALUES ('1','700','1200'); 
INSERT INTO SALGRADE VALUES ('2','1201','1400'); 
INSERT INTO SALGRADE VALUES ('3','1401','2000'); 
INSERT INTO SALGRADE VALUES ('4','2001','3000'); 
INSERT INTO SALGRADE VALUES ('5','3001','9999');

SELECT * FROM SALGRADE;

 

[예제] 사원 14명 모두에 대해 아래 SQL로 급여와 급여등급을 알아본다.

SELECT EMP.ENAME      AS 사원명, 
       EMP.SAL        AS 급여, 
       SALGRADE.GRADE AS 급여등급 
FROM EMP, SALGRADE
WHERE EMP.SAL BETWEEN SALGRADE.LOSAL AND SALGRADE.HISAL;

 

 

□ 3개 이상 TABLE JOIN

[예제] 선수 테이블의 소속팀코드(TEAM_ID)가 팀 테이블의 팀코드(TEAM_ID)와 PK-FK의 관계이고, 운동장 테이블의 운동장코드(STADIUM_ID)와 팀 테이블의 전용구장코드(STADIUM_ID)가 PK-FK 관계인 것을 생각하며 다음 SQL을 작성한다. 세 개의 테이블에 대한 JOIN이므로 WHERE 절에 2개 이상의 JOIN 조건이 필요하다.

SELECT 
 P.PLAYER_NAME 선수명, 
 P.POSITION 포지션, 
 T.REGION_NAME 연고지, 
 T.TEAM_NAME 팀명, 
 S.STADIUM_NAME 구장명 
 FROM PLAYER P, TEAM T, STADIUM S 
 WHERE P.TEAM_ID = T.TEAM_ID 
 AND T.STADIUM_ID = S.STADIUM_ID 
 ORDER BY 선수명;

 SELECT 
 P.PLAYER_NAME 선수명, 
 P.POSITION 포지션, 
 T.REGION_NAME 연고지, 
 T.TEAM_NAME 팀명, 
 S.STADIUM_NAME 구장명 
 FROM PLAYER P 
 INNER JOIN TEAM T    ON P.TEAM_ID = T.TEAM_ID 
 INNER JOIN STADIUM S ON T.STADIUM_ID = S.STADIUM_ID 
 ORDER BY 선수명;

 

 

□ USING 조건절

    NATURAL JOIN에서는 모든 일치되는 칼럼들에 대해 JOIN이 이루어지지만, FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수가 있다. 예제를 위해 임시 테이블을 생성하자.

CREATE TABLE DEPT_TEMP AS SELECT * FROM DEPT;

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

SELECT * FROM DEPT_TEMP;

 

[예제] 세 개의 칼럼명이 모두 같은 DEPT와 DEPT_TEMP 테이블을 DEPTNO 칼럼을 이용한 [INNER] JOIN의 USING 조건절로 수행한다.

SELECT *
FROM DEPT JOIN DEPT_TEMP
USING(DEPTNO);

    위 SQL의 '*' 와일드카드처럼 별도의 칼럼 순서를 지정하지 않으면 USING 조건절의 기준이 되는 칼럼이 다른 칼럼보다 먼저 출력된다. (ex: DEPTNO가 첫 번째 칼럼이 된다.) 이때 USING JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리한다.

 

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

-- 잘못된 사례
SELECT DEPT.DEPTNO, 
       DEPT.DNAME, 
       DEPT.LOC, 
       DEPT_TEMP.DNAME, 
       DEPT_TEMP.LOC 
FROM DEPT JOIN DEPT_TEMP 
USING (DEPTNO);

-- 바른 사례: 
SELECT DEPTNO, 
       DEPT.DNAME, 
       DEPT.LOC, 
       DEPT_TEMP.DNAME, 
       DEPT_TEMP.LOC 
FROM DEPT JOIN DEPT_TEMP 
USING (DEPTNO);

 

[예제] 이번에는 DEPT와 DEPT_TEMP 테이블의 일부 데이터 내용이 변경되었던 DNAME 칼럼을 조인 조건으로 [INNER] JOIN의 USING 조건절을 수행한다.

SELECT * 
FROM DEPT JOIN DEPT_TEMP 
USING(DNAME);

 

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

 

[예제] 이번에는 세 개의 칼럼명이 모두 같은 DEPT와 DEPT_TEMP 테이블을 LOC와 DEPTNO 2개 칼럼을 이용한 [INNER] JOIN의 USING 조건절로 수행한다.

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

 

    USING에 사용된 LOC, DEPTNO가 첫 번째, 두 번째 칼럼으로 출력되고, JOIN 조건에 참여하지 않은 DNAME 칼럼은 2개의 칼럼으로 표시된 것을 알 수 있다.

 

[예제] 이번에는 DEPTNO, DNAME 2개의 칼럼을 이용한 [INNER] JOIN의 USING 조건절로 수행한다.

SELECT * 
FROM DEPT JOIN DEPT_TEMP 
USING (DEPTNO, DNAME);

 

    위 SQL의 경우 DNAME의 내용이 바뀐 부서번호 20, 30의 경우는 결과에서 제외된 것을 알 수 있다. 그리고 USING에 사용된 DEPTNO, DNAME이 첫 번째, 두 번째 칼럼으로 출력된 것과 함께, JOIN 조건에 참여하지 않은 LOC가 2개의 칼럼으로 표시된 것을 알 수 있다

 

□ ON 조건절

    JOIN 서술부(ON 조건절)와 비 JOIN 서술부(WHERE 조건절)를 분리하여 이해가 쉬우며, 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점이 있다.

 

[예제] 사원 테이블과 부서 테이블에서 사원 번호와 사원 이름, 소속부서 코드, 소속부서 이름을 출력한다.

SELECT EMP.EMPNO,
       EMP.ENAME,
       EMP.DEPTNO,
       DEPT.DNAME
FROM EMP JOIN DEPT
ON (DEPT.DEPTNO = EMP.DEPTNO);

    NATURAL JOIN의 JOIN 조건은 기본적으로 같은 이름을 가진 모든 칼럼들에 대한 동등 조건이지만, 임의의 JOIN 조건을 지정하거나, 이름이 다른 칼럼명을 JOIN 조건으로 사용하거나, JOIN 칼럼을 명시하기 위해서는 ON 조건절을 사용한다. ON 조건절에 사용된 괄호는 옵션 사항이다.

    USING 조건절을 이용한 JOIN에서는 JOIN 칼럼에 대해서 ALIAS나 테이블 명과 같은 접두사를 사용하면 SYNTAX 에러가 발생하지만, 반대로 ON 조건절을 사용한 JOIN의 경우는 ALIAS나 테이블 명과 같은 접두사를 사용하여 SELECT에 사용되는 칼럼을 논리적으로 명확하게 지정해주어야 한다. (DEPTNO → E.DEPTNO) ON 조건절은 WHERE 절의 JOIN 조건과 같은 기능을 하면서도, 명시적으로 JOIN의 조건을 구분할 수 있으므로 가장 많이 사용될 것으로 예상된다. 다만, FROM 절에 테이블이 많이 사용될 경우 다소 복잡하게 보여 가독성이 떨어지는 단점이 있다.

 

○ ON 절과 WHERE 절의 혼용

[예제] ON 조건절과 WHERE 검색 조건은 충돌 없이 사용할 수 있다. 부서코드 30인 부서의 소속 사원 이름 및 소속 부서 코드, 부서 코드, 부서 이름을 찾아본다.

SELECT EMP.ENAME,
       EMP.DEPTNO  AS EMP_DEPTNO,
       DEPT.DEPTNO AS DEPT_DEPTNO,
       DEPT.DNAME
FROM EMP
JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
WHERE DEPT.DEPTNO = 30;

 

 

□ 다중 테이블 조인

[예제] 사원과 DEPT 테이블의 소속 부서명, DEPT_TEMP 테이블의 바뀐 부서명 정보를 출력한다.

SELECT EMP.ENAME       AS 사원,
       DEPT.DNAME      AS 부서명,
       DEPT_TEMP.DNAME AS 바뀐부서명
FROM EMP
JOIN DEPT
ON DEPT.DEPTNO = EMP.DEPTNO
JOIN DEPT_TEMP
ON DEPT.DEPTNO = DEPT_TEMP.DEPTNO;

SELECT EMP.ENAME       AS 사원,
       DEPT.DNAME      AS 부서명,
       DEPT_TEMP.DNAME AS 바뀐부서명
FROM EMP, DEPT, DEPT_TEMP
WHERE DEPT.DEPTNO = DEPT_TEMP.DEPTNO
AND EMP.DEPTNO = DEPT.DEPTNO;

 

[예제] GK 포지션의 선수별 연고지명, 팀명, 구장명을 출력한다.

SELECT PLAYER.PLAYER_NAME   AS 선수명,
       TEAM.REGION_NAME     AS 연고지명,
       PLAYER.POSITION      AS 포지션,
       TEAM.TEAM_NAME       AS 팀명,
       STADIUM.STADIUM_NAME AS 구장명
FROM PLAYER
JOIN TEAM
ON PLAYER.TEAM_ID = TEAM.TEAM_ID
JOIN STADIUM
ON TEAM.STADIUM_ID = STADIUM.STADIUM_ID
WHERE PLAYER.POSITION = 'GK'
ORDER BY 선수명;

SELECT PLAYER.PLAYER_NAME   AS 선수명,
       TEAM.REGION_NAME     AS 연고지명,
       PLAYER.POSITION      AS 포지션,
       TEAM.TEAM_NAME       AS 팀명,
       STADIUM.STADIUM_NAME AS 구장명
FROM PLAYER, TEAM, STADIUM
WHERE PLAYER.TEAM_ID = TEAM.TEAM_ID
AND TEAM.STADIUM_ID = STADIUM.STADIUM_ID
AND PLAYER.POSITION = 'GK'
ORDER BY 선수명;

 

 

□ CROSS JOIN

    CROSS JOIN은 E.F.CODD 박사가 언급한 일반 집합 연산자의 PRODUCT의 개념으로 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다.

    두 개의 테이블에 대한 CARTESIAN PRODUCT 또는 CROSS PRODUCT와 같은 표현으로, 결과는 양쪽 집합의 M*N 건의 데이터 조합이 발생한다. (아래 56건의 데이터는 EMP 14건 * DEPT 4건의 데이터 조합 건수이다.)

 

[예제] 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾아본다.

SELECT EMP.ENAME,
       DEPT.DNAME
FROM EMP CROSS JOIN DEPT;

 

    정상적인 데이터 모델이라면 CROSS PRODUCT가 필요한 경우는 많지 않지만, 간혹 튜닝이나 리포트를 작성하기 위해 고의적으로 사용하는 경우가 있을 수 있다.

    그리고 데이터웨어하우스의 개별 DIMENSION(차원)을 FACT(사실) 칼럼과 JOIN하기 전에 모든 DIMENSION의 CROSS PRODUCT를 먼저 구할 때 유용하게 사용할 수 있다.

 

□ OUTER JOIN

    앞서 다루었던 EQUI JOIN, Non EQUI JOIN은 모두 조인 조건의 결과가 참(TRUE)인 행들만 반환하는 INNER JOIN이다. OUTER JOIN은 조인 조건을 만족하지 않는 행들도 함께 반환할 때 사용한다. 

 

○ LEFT OUTER JOIN

    조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다. 즉, Table A와 B가 있을 때(Table 'A'가 기준이 됨), A와 B를 비교해서 B의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, B의 JOIN 칼럼에서 같은 값이 없는 경우에는 B 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다. 그리고 LEFT JOIN으로 OUTER 키워드를 생략해서 사용할 수 있다.

 

[예제] STADIUM에 등록된 운동장 중에는 홈팀이 없는 경기장도 있다. STADIUM과 TEAM을 JOIN 하되 홈팀이 없는 경기장의 정보도 같이 출력하도록 한다.

SELECT STADIUM.STADIUM_NAME,
       STADIUM.STADIUM_ID,
       STADIUM.SEAT_COUNT,
       STADIUM.HOMETEAM_ID,
       TEAM.TEAM_NAME
FROM STADIUM 
LEFT JOIN TEAM -- OUTER는 생략 가능한 키워드(LEFT OUTER JOIN -> LEFT JOIN)
ON STADIUM.HOMETEAM_ID= TEAM.TEAM_ID;

 

 

○ RIGHT OUTER JOIN

   조인 수행시 LEFT JOIN과 반대로 우측 테이블이 기준이 되어 결과를 생성한다. 즉, TABLE A와 B가 있을 때(TABLE 'B'가 기준이 됨), A와 B를 비교해서 A의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, A의 JOIN 칼럼에서 같은 값이 없는 경우에는 A 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다. 그리고 RIGHT JOIN으로 OUTER 키워드를 생략해서 사용할 수 있다.

 

[예제] DEPT에 등록된 부서 중에는 사원이 없는 부서도 있다. DEPT와 EMP를 조인하되 사원이 없는 부서 정보도 같이 출력하도록 한다.

SELECT EMP.ENAME,
       DEPT.DEPTNO,
       DEPT.DNAME,
       DEPT.LOC
FROM EMP 
RIGHT JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;

 

 

○ FULL OUTER JOIN

    조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다. 즉, TABLE A와 B가 있을 때(TABLE 'A', 'B' 모두 기준이 됨), RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일하다. 단, UNION ALL이 아닌 UNION 기능과 같으므로 중복되는 데이터는 삭제한다. 그리고 FULL JOIN으로 OUTER 키워드를 생략해서 사용할 수 있다.

 

[예제] DEPT 테이블과 DEPT_TEMP 테이블의 FULL OUTER JOIN 사례를 만들기 위해 DEPT_TEMP의 DEPTNO를 수정한다. 결과적으로 DEPT_TEMP 테이블의 새로운 DEPTNO 데이터는 DETP 테이블의 DEPTNO와 2건은 동일하고 2건은 새로운 DEPTNO가 생성된다.

UPDATE DEPT_TEMP
SET DEPTNO = DEPTNO + 20;

SELECT * FROM DEPT_TEMP;

 

[예제] DEPTNO 기준으로 DEPT와 DEPT_TEMP 데이터를 FULL OUTER JOIN으로 출력한다.

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

 

 

출처 : SQL 전문가 가이드