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

* ANSI/IOS 표준 SQL 기능

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

 - SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 SUBQUERY 기능들

 - ROLLUP, CUBE, GROUPING SETS 등의 새로운 리포팅 기능

 - WINDOW FUNCTION 같은 새로운 개념의 분석 기능들

 

* 일반 집합 연산자

 1) UNION

  - 수학적 합집합을 제공하기 위해, 공통 교집합의 중복을 업애기 위한 사전 작업

  - 시스템에 부하를 주는 정렬 작업이 발생

  - UNION ALL은 공통집합을 중복해서 그대로 보여주기 때문에 정렬 작업이 일어나지 않음

  - UNION과 UNION ALL의 출력결과가 같으면 UNION ALL 을 사용하는 것이 더 좋음

 2) INTERSECTION

  - 수학의 교집합으로써 두 집합의 공통 집합을 추출

 3) DIFFERENCE 

  - 수학의 차집합, 1)에서 2)를 제외한 부분

  - 대다수 벤더는 EXCEPT를, 오라클에서는 MINUS를 사용

 4) PRODUCT

  - CROSS PRODUCT라고 불리는 곱집합, JOIN조건이 없는 경우 생길 수 있는 모든 데이터 조합

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

  - CARTESIAN PRODUCT라고도 함

 

* 순수 관계 연산자

 5) SELECT

  - SQL 문장에서는 WHERE 절의 조건절 기능으로 구현

  - SELECT 연산과 SELECT 절은 다름

 6) PROJECT

  - SQL 문장에서 SELECT 절의 컬럼 선택 기능으로 구현

 7) JOIN

  - WHERE절의 INNER JOIN 조건과 함께 FROM 절의 NATURAL JOIN, INNER JOIN, OUTER JOIN, USING, ON 으로 발전

 8) DIVIDE

  - 왼쪽 집합을 XZ로 나눌때 XZ를 모두 갖는 A가 답이 되는 기능

  - 현재 사용되지 않음

  - 관계형 데이터베이스 모델링 시 엔티티 확정 및 정규화 과정, M:M 관계 분해 절차를 거치는데

    정규화 과정의 경우 데이터 정합성과 데이터 저장 공간의 절약을 위해 엔티티를 최대한 분리하는 작업

  - 일반적으로 3NF나 BCNF까지 진행

  - 정규화 후 하나의 주제에 관련있는 엔티티가 여러 개로 나누어 지고, 이 엔티티 들이 주로 테이블이 됨

  - 이 흩어진 데이터를 연결해서 원하는 데이터를 가져오는 작업이 JOIN

 

* FROM 절 JOIN 형태

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

 

* INNER JOIN

 - OUTER JOIN과 대비하여 내부 JOIN이라고 하며 JOIN 조건에서 동일한 값이 있는 행만 반환

 - WHERE에서 사용하던 JOIN을 FROM에서 정의, USING 조건절이나 ON 조건절을 필수적으로 사용해야 함

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

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

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을 수행

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

 - SQL server에서는 지원하지 않는 기능

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

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

SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME 
FROM EMP NATURAL JOIN DEPT; 
ERROR: NATURAL JOIN에 사용된 열은 식별자를 가질 수 없음

 - JOIN이 되는 테이블의 데이터 성격과 컬럼명이 동일해야 하는 제약 조건이 있음

 - 모델링상 부주의로 인해 동일한 컬럼명이라도 다른 용도의 데이터를 저장하는 경우도 있어서 주의해야 함

[예제] 아래 '*' 와일드카드처럼 별도의 칼럼 순서를 지정하지 않으면

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;

[예제] NATURAL JOIN과 INNER JOIN의 차이를 자세히 설명하기 위해 DEPT_TEMP 테이블을 임시로 만든다.

Oracle CREATE TABLE DEPT_TEMP AS SELECT * FROM DEPT;

SQL Server SELECT * INTO DEPT_TEMP FROM DEPT;

UPDATE DEPT_TEMP SET DNAME = 'R&D' 
WHERE DNAME = 'RESEARCH'; 

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

SELECT * 
FROM DEPT_TEMP;

[예제] 세 개의 칼럼명이 모두 같은 DEPT와 DEPT_TEMP 테이블을 NATURAL [INNER] JOIN으로 수행한다.

SELECT * 
FROM DEPT NATURAL INNER JOIN DEPT_TEMP; 

INNER는 DEFAULT 옵션으로 아래와 같이 생략할 수 있다. 
SELECT * 
FROM DEPT NATURAL JOIN DEPT_TEMP;

[예제] 다음에는 같은 조건이지만 출력 칼럼에서 차이가 나는 일반적인 INNER JOIN을 수행한다.

SELECT * 
FROM DEPT JOIN DEPT_TEMP ON DEPT.DEPTNO = DEPT_TEMP.DEPTNO 
                         AND DEPT.DNAME = DEPT_TEMP.DNAME 
                         AND DEPT.LOC = DEPT_TEMP.LOC; 
                         
위 SQL과 아래 SQL은 같은 결과를 얻을 수 있다. 
SELECT * 
FROM DEPT, DEPT_TEMP 
WHERE DEPT.DEPTNO = DEPT_TEMP.DEPTNO
      AND DEPT.DNAME = DEPT_TEMP.DNAME 
      AND DEPT.LOC = DEPT_TEMP.LOC;

 

* USING 조건절

 - NATURAL JOIN에서 모든 일치되는 컬럼에 대해 JOIN이 이루어지지만

   FROM 절의 USING 조건절을 이용하면 원하는 컬럼에 대해 선택적으로 EQUI JOIN 할 수 있다

 - SQL server 에서는 지원하지 않음

[예제] 세 개의 칼럼명이 모두 같은 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)

[예제] DEPT와 DEPT_TEMP 테이블의 일부 데이터 내용이 변경되었던 DNAME 칼럼을

조인 조건으로 [INNER] JOIN의 USING 조건절을 수행한다.

SELECT * 
FROM DEPT JOIN DEPT_TEMP USING (DNAME);

[예제] 세 개의 칼럼명이 모두 같은 DEPT와 DEPT_TEMP 테이블을

LOC와 DEPTNO 2개 칼럼을 이용한 [INNER] JOIN의 USING 조건절로 수행한다.

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

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

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