[SQLD 시험 대비] 2과목. SQL 기본 및 활용 : 2장. SQL 활용 - 1. 표준 조인 (Standard Join)

표준 조인 (Standard Join)

STANDARD SQL 개요

ANSI/ISO 표준 SQL의 기능

• STANDARD JOIN 기능 추가 (CROSS, OUTER JOIN 등 새로운 FROM 절 JOIN 기능들)
• SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 SUBQUERY 기능들
• ROLLUP, CUBE, GROUPING SETS 등의 새로운 리포팅 기능
• WINDOW FUNCTION 같은 새로운 개념의 분석 기능들

일반 집합 연산자

• UNION 연산은 수학적 합집합을 제공하기 위해, 공통 교집합의 중복을 없애기 위한 사전 작업으로 시스템에 부하를 주는 정렬 작업이 발생한다.
• INTERSECTION은 수학의 교집합으로써 두 집합의 공통집합을 추출한다.
• DIFFERENCE는 수학의 차집합으로써 첫 번째 집합에서 두 번째 집합과의 공통 집합을 제외한 부분이다.
  • 대다수 벤더는 EXCEPT를, Oracle은 MINUS 용어를 사용한다.
• PRODUCT의 경우는 CROSS(ANIS/ISO 표준) PRODUCT라고 불리는 곱집합으로, JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다.
  • 양쪽 집합의 M*N 건의 데이터 조합이 발생하며, CARTESIAN PRODUCT라고도 표현한다.

순수 관계 연산자

• SELECT 연산은 SQL 문장에서는 WHERE 절의 조건절 기능으로 구현이 되었다.
  • SELECT 연산과 SELECT 절의 의미가 다름을 유의하자.
• PROJECT 연산은 SQL 문장에서는 SELECT 절의 칼럼 선택 기능으로 구현이 되었다.
• JOIN 연산은 WHERE 절의 INNER JOIN 조건과 함께 FROM 절의 NATURAL JOIN, INNER JOIN, OUTER JOIN, USING 조건절, ON 조건절 등으로 가장 다양하게 발전하였다.
• DIVIDE 연산은 나눗셈과 비슷한 개념
  • 왼쪽의 집합을 ‘XZ’로 나누었을 때, 즉 ‘XZ’를 모두 가지고 있는 ‘A’가 답이 되는 기능으로 현재는 사용되지 않는다.

FROM 절 JOIN 형태

• ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태는 다음과 같다.
  • INNER JOIN
  • NATURAL JOIN
  • USING 조건절
  • ON 조건절
  • CROSS JOIN
  • OUTER JOIN
• 사용자는 기존 WHERE 절의 검색 조건과 테이블 간의 JOIN 조건을 구분 없이 사용하던 방식을 그대로 사용할 수 있으면서, 추가된 선택 기능으로 테이블 간의 JOIN 조건을 FROM 절에서 명시적으로 정의할 수 있다.

INNER JOIN

• 내부 JOIN이라고 하며 JOIN 조건에서 동일한 값이 있는 행만 반환한다.
• INNER JOIN 표시는 그동안 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필 수적으로 사용해야 한다.
• INNER는 JOIN의 DEFAULT 옵션이므로 생략이 가능하지만, CROSS JOIN, OUTER JOIN과는 같이 사용할 수 없다.

예제

• 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾아본다.
• WHERE 절 JOIN 조건

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

• FROM 절 JOIN 조건

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

• INNER는 JOIN의 디폴트 옵션으로 아래 SQL문과 같이 생략 가능하다.

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

NATURAL JOIN

• 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행
• NATURAL JOIN이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없다.
• SQL Server에서는 지원하지 않는 기능이다.
• * 와일드카드처럼 별도의 칼럼 순서를 지정하지 않으면 NATURAL JOIN의 기준이 되는 칼럼들이 다른 칼럼보다 먼저 출력된다.
  • 반면, INNER JOIN의 경우 첫 번째 테이블, 두 번째 테이블의 칼럼 순서대로 데이터가 출력된다.
  • 이때 NATURAL JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리하지만, INNER JOIN은 별개의 칼럼으로 표시한다.

예제

• 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾아보자.

SELECT DEPTNO, EMPNO, ENAME, DNAME 
FROM EMP NATURAL JOIN DEPT;

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

USING 조건절

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

예제

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

SELECT *
FROM DEPT JOIN DEPT_TEMP 
USING (DEPTNO);

• 위 SQL의 * 와일드카드처럼 별도의 칼럼 순서를 지정하지 않으면 USING 조건절의 기준 이 되는 칼럼이 다른 칼럼보다 먼저 출력된다.
  • USING JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리
• JOIN 칼럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.

// 잘못된 사례
SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, DEPT_TEMP.DNAME, DEPT_TEMP.LOC
FROM DEPT JOIN DEPT_TEMP 
USING (DEPTNO);
// ERROR : USING 절의 열 부분은 식별자를 가질 수 없음

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

ON 조건절

• 임의의 JOIN 조건을 지정하거나, 이름이 다른 칼럼명을 JOIN 조건으로 사용하거나, JOIN 칼럼을 명시하기 위해서는 ON 조건절을 사용한다.
  • ON 조건절에 사용된 괄호는 옵션 사항이다.
• ON 조건절을 사용한 JOIN의 경우는 ALIAS나 테이블 명과 같은 접두사를 사용하여 SELECT에 사용되는 칼럼을 논리적으로 명확하게 지정해주어야 한다.
• ON 조건절은 WHERE 절의 JOIN 조건과 같은 기능을 하면서도, 명시적으로 JOIN의 조건을 구분할 수 있으므로 가장 많이 사용될 것으로 예상된다.
  • 다만, FROM 절에 테이블이 많이 사용될 경우 다소 복잡하게 보여 가독성이 떨어지는 단점이 있다.

WHERE 절과의 혼용

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

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

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

• ON 조건절에 JOIN 조건 외에도 데이터 검색 조건을 추가할 수는 있으나, 검색 조건 목적인 경우는 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);

// 같은 결과
SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) 
WHERE E.MGR = 7698;

다중 테이블 JOIN 예제

예제 1

• 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 절의 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 선수명;

예제 2

• 홈팀이 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;

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

CROSS JOIN

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

예제

• 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾아보자.

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

OUTER JOIN

• INNER(내부) JOIN과 대비하여 OUTER(외부) JOIN이라고 불리며, JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용할 수 있다.
  
• JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다.
• LEFT/RIGHT 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_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;

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는 생략 가능하다.
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 기능과 같으므로 중복되는 데이터는 삭제한다.
• 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;

// OUTER는 생략 가능하다.
SELECT *
FROM DEPT FULL JOIN DEPT_TEMP
ON DEPT.DEPTNO = DEPT_TEMP.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은 제외한다.
  • 양쪽 테이블 TAB1과 TAB2의 데이터를 곱한 개수인 4 * 3 = 12건이 추출됨
  • 키 값 조합이 B-A, B-B, B-C, C-A, C-B, C-C, D-A, D-B, D-C, E-A, E-B, E-C 인 12건이 출력된다.