SQL 전문가 가이드, JOIN 정리

 

1. INNER JOIN

 - JOIN 조건에 동일한 값이 있는 행만 반환

- INNER JOIN 표시는 그 동안 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시

- USING 조건절이나 ON 조건절을 필수적으로 사용해야 함

 

2. NATURAL JOIN

 - 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행

 - 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없음

 - SQL Server에서 지원하지 않는 기능임

 - SELECT DEPTNO, EMPNO, ENAME, DNAME

   FROM EMP NATURAL JOIN DEPT;

 - 별도의 JOIN 컬럼을 지정하지 않았지만, 두 개의 테이블에서 DEPTNO라는 공통된 칼럼을 자동으로 인식하여 JOIN을 처리한 것임

 - JOIN에 사용된 컬럼들은 같은 데이터 유형이어야 하며, ALIAS나 테이블명과 같은 접두사를 붙일 수 없음 

 - NATURAL JOIN은 JOIN이 되는 테이블의 데이터 성격(도메인)과 칼럼명 등이 동일해야 하는 제약 조건이 있음

 - 간혹 모델링 상의 부주의로 인해 동일한 칼럼명이라도 다른 용도의 데이터를 저정하는 경우도 있으므로 주의 요망

 

3. USING 조건절

 - FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있음

 - SQL Server에서 지원하지 않음

 - USING 조건절을 이용한 EQUI 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);

 

 

4. ON 조건절

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

 - 이름이 다른 칼럼명을 JOIN 조건으로 사용하거나, JOIN 칼럼을 명시하기 위해서는 ON 조건절을 사용한다.

 - ON 조건절에 사용된 괄호는 옵션이다.

 - ON 조건절을 사용한 JOIN의 경우는 ALIAS나 테이블 명과 같은 접두사를 사용하여 SELECT에 사용되는 칼럼을 논리적으로 명확하게 지정해주어야 한다. (USING 조건절을 이용한 JOIN에서는 JOIN 칼럼에 대해서 ALIAS나 테이블명과 같은 접두사를 사용하면 SYNTAX 에러가 발생한다.)

 - FROM 절에 테이블이 많이 사용될 경우 다소 복잡하게 보여 가독성이 떨어질 수 있다.

 

 1) ON 조건절 예제 - 팀과 스타디움 테이블을 스타디움ID로 JOIN하여 팀이름, 스타디움ID, 스타디움 이름을 찾아본다.

SQL> SELECT TEAM_NAME, TAEM.STADIUM_ID, STADIUM_NAME

         FROM TEAM JOIN STADIUM

         ON TEAM.STADIUM_ID = STADIUM.STADIUM_ID

         ORDER BY STADIUM_ID;

 

STADIUM_ID라는 공통된 칼럼이 있기 때문에 아래처럼 USING 조건절로 구현할 수도 있다.

 

SQL> SELECT TEAM_NAME, STADIUM_ID, STADIUM_NAME

         FROM TEAM JOIN STADIUM

         USING (STADIUM_ID)

         ORDER BY STADIUM_ID;

 

INNNER JOIN으로 구현할 수도 있다.

 

SQL> TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME

         FROM TEAM, STADIUM

         WHERE TEAM.STADIUM_ID = STADIUM.STADIUM_ID

         ORDER BY STADIUM_ID;

 

 

 2) 팀과 스타디움 테이블을 팀ID로 JOIN하여 팀이름, 팀ID, 스타디움 이름을 찾아본다. STADIUM에는 팀ID가 HOMETEAM_ID라는 칼럼으로 표시되어 있다.

 

 SQL> SELECT TEAM_NAME, TEAM_ID, STADIUM_NAME

          FROM TEAM JOIN STADIUM

          ON TEAM.TEAM_ID = STADIUM.HOMETEAM_ID

          ORDER BY TEAM_ID;

 

 WHERE절의 INNER JOIN으로 구현 가능

 

 SQL> SELECT TEAM_NAME, TEAM_ID, STADIUM_NAME

          FROM TEAM, STADIUM

          WHERE TEAM.TEAM_ID = STADIUM.HOMETEAM_ID

          ORDER BY TEAM_ID;

 

 3) 다중 테이블 JOIN

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

 SQL> 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);

 

WHERE절의 INNNER JOIN으로 구현 가능

 

 SQL> 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 선수명;

WHERE절의 INNNER 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, 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;

WHERE절의 INNNER JOIN으로 구현 가능

SELECT ST.STADIUM_NAME, SC.STADIUM_ID, SCHE_DATE, 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;

 

 

5. OUTER JOIN

 1)LEFT OUTER JOIN
 - 테이블 A와 B가 있을 때(Table 'A'가 기준이 됨), A와 B를 비교해서 B의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, B의 JOIN 칼럼에서
같은 값이 없는 경우에는 B 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다.
 - OUTER 키워드 생략 가능
 
 - 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는 생략 가능한 키워드 임.

 - 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;

 2)RIGHT OUTER JOIN
 - LEFT OUTER JOIN과 반대로 생각(A의 JOIN 칼럼에서 같은 값이 없는 경우에는 A 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다.)
 - OUTER 키워드 생략 가능

 - SELECT E.ENAME, D.DEPTNO, D.DNAME, D.LOC
   FROM EMP E RIGHT OUTER JOIN DEPT D
   ON E.DEPTNO = D.DEPTNO;

 OUTER는 생략 가능한 키워드 임.

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

 

 3)FULL OUTER JOIN

 - RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과 합집함
 - 단, UNION ALL이 아닌 UNION 기능과 같으므로 중복되는 데이터는 삭제
 - OUTER 키워드 생략 가능

 - DEPTNO 기준으로 DEPT와 DEPT_TEMP 데이터를 FULL OUTER JOIN으로 출력한다.
 
   SELECT *
   FROM DEPT FULL OUTER JOIN DEPT_TEMP
   ON DEPT.DEPTNO = DEPT_TEMP.DEPTNO;

   OUTER는 생략 가능한 키워드 임.

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

   아래와 같이 표현이 가능함.

   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;