SQLD - 과목2. (10) 표준 조인

1. STANDARD SQL 개요

현재 기업형 DBMS 는 객체 지원 기능이 포함된 객체관계형 ( Object Relational ) 데이터베이스를 대부분 사용 중이다.

관계형 데이터베이스를 유일하게 접속 가능한 언어가 SQL 이다.

 

여러 불편사항을, 사용자 입장에서는 ANSI / ISO SQL 의 새로운 기능들을 사용함으로써 더 쉽게 데이터를 추출하고 SQL 튜닝의 효과를 함께 얻을 수 있게 되었다.

 

 

▷ 대표적인 ANSI / ISO 표준 SQL 기능

    (1) STANDARD JOIN 기능 추가 ( CROSS, OUTER JOIN 등 새로운 FROM 절 JOIN 기능들 )

    (2) SCALAR SUBQUERY , TOP-N QUERY 등 새로운 SUBQUERY 기능들

    (3) ROLLUP , CUBE , GROUPING SETS 등 새로운 리포팅 기능들

    (4) WINDOW FUNCTION 등 새로운 개념의 분석 기능들

 

 

 

---

① 일반 집합 연산자

 

E.F.CODD 일반 집합 연산자

 

E.F.Codd 박사의 논문에 현재 사용하는 SQL 의 많은 기능이 언급되어 있다.

8가지 관계형 대수 ( 4개의 일반 집합 연산자 / 순수 관계 연산자 )

 

 

 

< 일반 집합 연산자 >

 

1) UNION 연산은 UNION 기능으로

2) INTERSECTION 연산은 INTERSECT 기능으로

3) DIFFERENCE 연산은 EXCEPT ( Oracle 은 MINUS ) 기능으로

4) PRODUCT 연산은 CROSS JOIN 기능으로

 

구현된다.

 

 

 

 

---

② 순수 관계 연산자

    : 순수 관계 연산자는 관계형 데이터베이스를 구현하기 위해 새롭게 만든 연산자이다.

 

E.F.CODD 순수 관계 연산자

 

 

< 순수 관계 연산자 >

 

5) SELECT 연산은 WHERE 절로 구현되었다.

6) PROJECT 연산은 SELECT 절로 구현되었다.

7) (NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현되었다.

8) DIVIDE 연산은 현재 사용되지 않는다.

 

 

 

 

 

 

 

 

 

 

 

---

2. FROM 절 JOIN 형태

▷ ANSI / ISO SQL 의 FROM 절의 JOIN 형태

   

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

 

ANSI / ISO SQL 에서 규정한 JOIN 문법은,

사용자는 기존 WHERE 절의 검색 조건과 테이블 간의 JOIN 조건을 구분 없이 사용하던 방식을 그대로 사용할 수 있으면서,

추가된 선택 기능으로 테이블 간의 JOIN 조건을 FROM 절에서 명시적으로 정의한다.

 

 

 

 

 

---

3. INNER JOIN

▷ INNER JOIN

   : OUTER JOIN 과 대비하여 내부 JOIN 이다.

     JOIN 조건에서 동일한 값이 있는 행만 반환한다.

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

     ( USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다. )

 

 

 

 

(예제)

 

(1) 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾아본다.

// WHERE 절 JOIN 조건 ( 출력할 칼럼 생략 )
SELECT ___ FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;

// FROM 절 JOIN 조건 ( 출력할 칼럼 생략 )
SELECT ___ FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

 

 

 

 

 

---

4. NATURAL JOIN

▷ NATURAL JOIN

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

     NATURAL JOIN 이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없다.

     ( SQL Server 에서 지원 X )

     JOIN 되는 테이블의 데이터 성격과 칼럼명 등이 동일해야 한다.

 

 

 

(예제)

 

(1) 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾아본다.

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

 

별도의 JOIN 칼럼을 지정하지 않아도, 두 개의 테이블에서 ' DEPTNO ' 라는 공통된 칼럼을 자동으로 인식하여 JOIN 처리한다.

JOIN 에 사용된 칼럼들은 같은 데이터 유형이어야 한다.

ALIAS 나 테이블 명과 같은 접두사를 붙일 수 없다.

 

 

* 오류 코드

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

ERROR : NATURAL JOIN 에 사용된 열은 식별자를 가질 수 없음

 

 

 

 

 

 

(2) 세 개의 칼럼명이 모두 같은 DEPT 와 DEPT_TEMP 테이블을 NATURAL [INNER] JOIN 이용한다.

SELECT * FROM DEPT NATURAL [INNER] JOIN DEPT_TEMP;

 

 

 

 

 

 

 

 

---

5. USING 조건절

▷ USING

   : NATURAL JOIN 은 모든 일치되는 칼럼들에 대해 JOIN 이 된다.

     FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중 선택적으로 EQUI JOIN 을 할 수 있다.

     ( SQL Server 에서 지원 X )

     USING 조건절을 이용한 EQUI JOIN 에서도 NATURAL JOIN 처럼,

     JOIN 칼럼에 대해서 ALIAS 나 테이블명과 같은 접두사를 사용할 수 없다.

 

 

 

 

(예제)

 

(1) 세 개의 칼럼명이 모두 같은 DEPT 와 DEPT_TEMP 테이블을 DEPTNO 칼럼을 이용한 [INNER] JOIN 의 USING 조건절을 이용한다.

SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);

( * ) 와일드카드를 사용하여 별도의 칼럼 순서를 지정하지 않으면 USING 조건절의 기준이 되는 칼럼이 다른 칼럼보다 먼저 출력된다.

' DEPTNO ' 가 출력의 첫 번째 칼럼이 된다.

 

 

 

 

 

 

 

 

 

---

6. ON 조건절

▷ ON

   : JOIN 서술부 ( ON 조건절 ) 와 비 JOIN 서술부 ( WHERE 조건절 ) 을 분리한다.

    칼럼명이 다르더라도 JOIN 조건절을 사용 가능하다.

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

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

 

    ON 조건절을 사용한 JOIN 은 ALIAS 나 테이블명과 같은 접두사를 사용하여 SELECT 에 사용되는 칼럼을 논리적으로 명확하게 한다.

    ( USING 조건절을 이용한 JOIN 에서는 ERROR 사항 )

 

 

 

 

 

(예제)

 

(1) 사원 테이블과 부서 테이블에서 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 출력한다.

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

 

 

---

① WHERE 절과의 혼용

 

ON 조건절과 WHERE 검색 조건은 충돌 없이 사용 가능하다.

 

 

 

 

(예제)

 

(1) 부서코드 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 절 사용을 권고 )

OUTER JOIN 에서 JOIN 의 대상을 제한하기 위해서는 추가 조건의 경우 ON 절에 표기 되어야 한다.

 

 

 

 

(예제)

 

(1) 매니저 사원번호가 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;

 

 

---

③ ON 조건절 예제

 

 

(예제)

 

(1) TEAM 과 STADIUM 테이블을 STADIUM 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;

SELECT TEAM_NAME, STADIUM_ID, STADIUM_NAME FROM TEAM
JOIN STADIUM USING(STADIUM_ID)
ORDER BY STADIUM_ID;

 

 

 

---

④ 다중 테이블 JOIN

 

 

(예제)

 

(1) 사원과 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);

 

 

 

 

 

 

 

 

---

7. CROSS JOIN

▷ CROSS JOIN

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

     두 개의 테이블에 대한 CARTESIAN PRODUCT 또는 CROSS PRODUCT 표현으로,

     결과는 양쪽 집합의 M * N 건의 데이터 조합이 발생한다.

 

 

 

(예제)

 

(1) 사원 이름, 소속부서 이름을 출력한다.

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

 

정상적인 데이터 모델에서는 CROSS PRODUCT 가 필요한 경우가 많지 않다.

튜닝이나 리포트 작성을 위해 고의적 사용이 있다.

 

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

 

 

 

 

 

 

 

 

 

---

8. OUTER JOIN

▷ OUTER JOIN

   : INNER JOIN 과 대비한다. JOIN 조건에서 동일한 값이 없는 행을 반환할 때 사용한다.

 

 

 

 

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

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

OUTER JOIN 설명

 

 

 

---

① LEFT OUTER JOIN

 

JOIN 수행시 좌측 테이블의 데이터를 먼저 읽은 후, 우측 테이블의 JOIN 대상 데이터를 읽는다.

TABLE A 와 B 에서 TABLE 'A' 가 기준이 된다.

 

A 와 B 를 비교해서 B 의 JOIN 칼럼에서 같은 값이 있을 때 해당 데이터를 가져온다.

B 의 JOIN 칼럼에서 같은 값이 없는 경우엔, B 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다.

 

LEFT JOIN 으로 OUTER 키워드를 생략할 수 있다.

LEFT [OUTER] JOIN

 

 

---

② RIGHT OUTER JOIN

 

JOIN 수행시 우측 테이블의 데이터를 먼저 읽은 후, 좌측 테이블의 JOIN 대상 데이터를 읽는다.

TABLE A 와 B 에서 TABLE 'B' 가 기준이 된다.

 

A 와 B 를 비교해서 A 의 JOIN 칼럼에서 같은 값이 있을 때 해당 데이터를 가져온다.

A 의 JOIN 칼럼에서 같은 값이 없는 경우엔, A 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다.

 

LEFT JOIN 으로 OUTER 키워드를 생략할 수 있다.

RIGHT [OUTER] JOIN

 

 

---

③ FULL OUTER JOIN

 

JOIN 수행시 좌측, 우측 테이블 모두의 데이터를 읽어 JOIN 한다.

TABLE A 와 B 에서 TABLE 'A' 와 'B' 둘다 기준이 된다.

 

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

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

 

 

 

 

 

 

 

---

9. INNER vs OUTER vs CROSS JOIN 비교

▷ OUTER JOIN

   : INNER JOIN 과 대비한다. JOIN 조건에서 동일한 값이 없는 행을 반환할 때 사용한다.

 

 

INNER vs OUTER vs CROSS JOIN 비교

 

 

 

< 결과 >

 

* INNER JOIN : B-B / C-C ( 2건 )

* LEFT OUTER JOIN : B-B / C-C / D-NULL / E-NULL ( 4건 )

* RIGHT OUTER JOIN : NULL-A / B-B / C-C ( 3건 )

* FULL OUTER JOIN : NULL-A / B-B / C-C / D-NULL / E-NULL ( 5건 )

* CROSS JOIN : ( 4 * 3 ) 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건 )

   ( CROSS JOIN 은 OUTER JOIN 은 제외한다 )

 

 

 

 

 

 

 

---