[SQLD : Ⅳ. SQL 활용] 1-2. 표준조인

* ON 조건절

 - JOIN 서술부(ON 조건절)와 비 JOIN 서술부(WHERE 조건절)를 분리하여 이해

 - 컬럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점이 있음

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

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

 - NATURAL JOIN의 JOIN 조건은 기본적으로 같은 이름을 가진 모든 컬럼들에 대한 동등 조건

 - 임의의 JOIN 조건 지정, 이름이 다른 컬럼명을 JOIN 조건으로 사용, JOIN 컬럼을 명시하기 위해 ON 조건절 사용

 - ON 조건절을 사용한 JOIN의 경우는 ALIAS나 테이블 명과 같은 접두사를 사용하여

   SELECT에 사용되는 칼럼을 논리적으로 명확하게 지정해야 함

 

 - ON 조건절 + 데이터 검증 조건 추가

  검색 조건 목적인 경우는 WHERE 절을 사용할 것을 권고

  아우터 조인에서 조인의 대상을 제한하기 위한 목적으로 사용되는 추가 조건의 경우는 ON 절에 표기되어야 함

   [예제] 매니저 사원번호가 7698번인 사원들의 이름 및 소속 부서 코드, 부서 이름을 찾아본다.

SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME 
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO AND E.MGR = 7698);

위 SQL과 아래 SQL은 같은 결과를 얻을 수 있다. 
SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME 
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) 
WHERE E.MGR = 7698;

 

 - ON 조건절 예제

  [예제] 팀과 스타디움 테이블을 스타디움ID로 JOIN하여 팀이름, 스타디움ID, 스타디움 이름을 찾아본다.

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

위 SQL은 STADIUM_ID라는 공통된 칼럼이 있기 때문에 아래처럼 USING 조건절로 구현할 수도 있다. 
SELECT TEAM_NAME, STADIUM_ID, STADIUM_NAME 
FROM TEAM JOIN STADIUM USING (STADIUM_ID) 
ORDER BY STADIUM_ID; 

위 SQL은 고전적인 방식인 WHERE 절의 INNER JOIN으로 구현할 수도 있다. 
SELECT TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME 
FROM TEAM, STADIUM 
WHERE TEAM.STADIUM_ID = STADIUM.STADIUM_ID 
ORDER BY STADIUM_ID

   [예제] 팀과 스타디움 테이블을 팀ID로 JOIN하여 팀이름, 팀ID, 스타디움 이름을 찾아본다.

   STADIUM에는 팀ID가 HOMETEAM_ID라는 칼럼으로 표시되어 있다.

SELECT TEAM_NAME, TEAM_ID, STADIUM_NAME 
FROM TEAM JOIN STADIUM ON TEAM.TEAM_ID = STADIUM.HOMETEAM_ID
ORDER BY TEAM_ID; 

위 SQL은 고전적인 방식인 WHERE 절의 INNER JOIN으로 구현할 수도 있다. 
SELECT TEAM_NAME, TEAM_ID, STADIUM_NAME 
FROM TEAM, STADIUM WHERE TEAM.TEAM_ID = STADIUM.HOMETEAM_ID 
ORDER BY TEAM_ID; 

위 SQL은 TEAM_ID와 HOMETEAM_ID라는 다른 이름의 칼럼을 사용하기 때문에 
USING 조건절을 사용할 수는 없다.

 

 - 다중 테이블 JOIN

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

SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME New_DNAME 
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) 
           JOIN DEPT_TEMP T ON (E.DEPTNO = T.DEPTNO); 
           
위 SQL은 고전적인 방식인 WHERE 절의 INNER JOIN으로 구현할 수도 있다.
SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME New_DNAME 
FROM EMP E, DEPT D, DEPT_TEMP T 
WHERE E.DEPTNO = D.DEPTNO AND E.DEPTNO = T.DEPTNO;

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

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

위 SQL은 고전적인 방식인 WHERE 절의 INNER 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 
      AND P.POSITION = 'GK' ORDER BY 선수명;

  [예제] 홈팀이 3점 이상 차이로 승리한 경기의 경기장 이름, 경기 일정, 홈팀 이름과 원정팀 이름 정보를 출력한다.

SELECT ST.STADIUM_NAME, SC.STADIUM_ID, SCHE_DATE, 
       HT.TEAM_NAME, AT.TEAM_NAME, HOME_SCORE, AWAY_SCORE 
FROM SCHEDULE SC JOIN STADIUM ST ON SC.STADIUM_ID = ST.STADIUM_ID 
                 JOIN TEAM HT ON SC.HOMETEAM_ID = HT.TEAM_ID 
                 JOIN TEAM AT ON SC.AWAYTEAM_ID = AT.TEAM_ID 
WHERE HOME_SCORE > = AWAY_SCORE +3; 

위 SQL은 고전적인 방식인 WHERE 절의 INNER JOIN으로 구현할 수도 있다. 
SELECT ST.STADIUM_NAME, SC.STADIUM_ID, SCHE_DATE, 
       HT.TEAM_NAME, AT.TEAM_NAME, HOME_SCORE, AWAY_SCORE 
FROM SCHEDULE SC, STADIUM ST, TEAM HT, TEAM AT 
WHERE HOME_SCORE> = AWAY_SCORE +3 AND SC.STADIUM_ID = ST.STADIUM_ID 
      AND SC.HOMETEAM_ID = HT.TEAM_ID 
      AND SC.AWAYTEAM_ID = AT.TEAM_ID; 
FROM 절에 4개의 테이블이 JOIN에 참여하였으며, 
HOME TEAM과 AWAY TEAM의 팀 이름을 구하기 위해 TEAM 테이블을 HT와 AT 두 개의 ALIAS로 구분하였다.

 

* CROSS JOIN

 - 일반 집합 연산자의 PRODUCT 개념으로 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터 조합

 - 두 개의 테이블에 대한 CARTESIAN 또는 CROSS PRODUCT와 같은 표현

 - 결과는 양쪽 집합의 M+N 건의 데이터 조합 발생

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

SELECT ENAME, DNAME 
FROM EMP CROSS JOIN DEPT ORDER BY ENAME;

  [예제] NATURAL JOIN의 경우 WHERE 절에서 JOIN 조건을 추가할 수 없지만,

  CROSS JOIN의 경우 WHERE 절에 JOIN 조건을 추가할 수 있다.

  그러나, 이 경우는 CROSS JOIN이 아니라 INNER JOIN과 같은 결과를 얻기 때문에

  CROSS JOIN을 사용하는 의미가 없어지므로 권고하지 않는다.

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

위 SQL과 아래 SQL은 같은 결과를 얻을 수 있다. 
SELECT ENAME, DNAME 
FROM EMP INNER JOIN DEPT 
WHERE EMP.DEPTNO = DEPT.DEPTNO;

 - 간혹 튜닝이나 리포트를 작성하기 위해 고의적으로 사용하는 경우가 있을 수 있음

 - 데이터웨어하우스의 개별 차원을 FACT 칼럼과 JOIN하기 전에 모든 차원의 CROSS PRODUCT를 먼저 구할 때 사용

 

* OUTER JOIN

 - INNER JOIN과 대비하여 OUTER JOIN이라고 불리며, JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용

 TAB1 테이블이 TAB2 테이블을 JOIN 하되, TAB2의 JOIN 데이터가 있는 경우는 TAB2의 데이터를 함께 출력하고,

TAB2의 JOIN 데이터가 없는 경우에도 TAB1의 모든 데이터를 표시하고 싶은 경우

 

 - 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_NAME, STADIUM.STADIUM_ID, SEAT_COUNT, HOMETEAM_ID, TEAM_NAME 
FROM STADIUM LEFT OUTER JOIN TEAM ON STADIUM.HOMETEAM_ID = TEAM.TEAM_ID
ORDER BY HOMETEAM_ID; 

OUTER는 생략 가능한 키워드이므로 아래 SQL은 같은 결과를 얻을 수 있다. 
SELECT STADIUM_NAME, STADIUM.STADIUM_ID, SEAT_COUNT, HOMETEAM_ID, TEAM_NAME 
FROM STADIUM LEFT JOIN TEAM ON STADIUM.HOMETEAM_ID = TEAM.TEAM_ID 
ORDER BY HOMETEAM_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 E.ENAME, D.DEPTNO, D.DNAME 
FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO; 

OUTER는 생략 가능한 키워드이므로 아래 SQL은 같은 결과를 얻을 수 있다. 
SELECT E.ENAME, D.DEPTNO, D.DNAME, D.LOC 
FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

 

 - FULL OUTER JOIN

  조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성

  즉, TABLE A와 B가 있을 때(TABLE 'A', 'B' 모두 기준이 됨),

  RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일하다.

  단, UNION ALL이 아닌 UNION 기능과 같으므로 중복되는 데이터는 삭제한다.

  (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으로 출력한다.

  예제에 사용된 UNION(중복 데이터는 제거됨)은 다음 절에서 설명하도록 한다.

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

OUTER는 생략 가능한 키워드이므로 아래 SQL은 같은 결과를 얻을 수 있다. 
SELECT * 
FROM DEPT FULL JOIN DEPT_TEMP ON DEPT.DEPTNO = DEPT_TEMP.DEPTNO; 

위 SQL과 아래 SQL은 같은 결과를 얻을 수 있다. 
SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC 
FROM DEPT L 
LEFT OUTER JOIN DEPT_TEMP R ON L.DEPTNO = R.DEPTNO 
UNION SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC 
FROM DEPT L 
RIGHT OUTER JOIN DEPT_TEMP R ON L.DEPTNO = R.DEPTNO;

 

*  INNER vs OUTER vs CROSS JOIN 비교

 - INNER JOIN의 경우 양쪽 테이블에 모두 존재하는 키 값이 B-B, C-C인 2건이 출력

 - LEFT OUTER JOIN의 결과는 TAB1을 기준으로 키 값 조합이 B-B, C-C, D-NULL, E-NULL 인 4건 출력

 - RIGHT OUTER JOIN의 결과는 TAB2를 기준으로 키 값 조합이 NULL-A, B-B, C-C인 3건 출력

 - FULL OUTER JOIN은 양쪽 테이블을 기준으로 키 값 조합이 NULL-A, B-B, C-C, D-NULL, E-NULL인 5건 출력

 - CROSS JOIN은 JOIN 가능한 모든 경우의 수를 표시하지만 OUTER JOIN은 제외

   양쪽 테이블의 데이터를 곱한 개수인 12건 추출 

   키 값 조합은 B-A, B-B, B-C, C-B, C-C, D-A, D-B, D-C, E-A, E-B, E-C