[SQLD] 21. 표준 조인(STANDARD JOIN)

제 1절 표준 조인(STANDARD JOIN)

1. STANDARD SQL 개요

  대표적인 ANSI / ISO 표준 SQL의 기능은 다음 내용을 포함한다

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

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

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

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

가. 일반 집합 연산자

  현재 사용하는 SQL의 많은 기능이 관계형 데이터베이스의 이론을 수립한 E.F.Codd 박사의 논문에 언급이 되어 있다. 논문에 언급된 8가지 관계형 대수는 다시 각각4개의 일반 집합 연산자와 순수 관계 연산자로 나눌 수 있으며, 관계형 데이터베이스 엔진 및 SQL의 기반 이론이 되었다. 일반 집합 연산자를 현재의 SQL과 비교 하자면,

1. UNION 연산 - UNION 기능

2. INTERSECTION 연산 - INTERSECT 기능

3. DIFFERENCE 연산 - EXCEPT 기능 (Oracle은 MINUS)

4. PRODUCT 연산 - CROSS JOIN 기능

  첫번째 합집합 연산은 공통 교집합의 중복을 없애기 위한 사전 작업으로 시스템의 부하를 줄 수 있는 정렬 작업이 발생한다. 이후 UNION ALL 기능이 추가되었는데, 특별한 요구 사항이 없다면 공통집합을 중복해서 그대로 보여주기 때문에 정렬 작업이 일어나지 않는 장점을 가진다. 만일 UNION과 UNION ALL의 출력 결과가 같다면, 효율화 측면에서 데이터 정렬이 발생하지 않는 UNION ALL을 권고한다.

  두번째, PRODUCT의 경우는 CROSS PRODUCT 라고 불리는 곱집합으로, JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다. 양쪽 집합의 M * N 건의 데이터 조합이 발생하며, CARTESIAN PRODUCT라고도 한다.

나. 순수 관계 연산자

  순수 관계 연산자는 관계형 데이터베이스를 구현하기 위해 새롭게 만들어진 연산자이다. 순수 관계 연산자를 현재의 SQL 문장과 비교하면 다음과 같다. 

5. SELECT 연산 - WHERE 절

6. PROJECT 연산 - SELECT 절

7. (NATURAL) JOIN 연산 - 다양한 JOIN 기능들

8. DIVIDE 연산 - 사용되지 않음

  JOIN 연산은 WHERE 절의 INNER JOIN 조건과 함께 FROM 절의 NATURAL JOIN, INNER JOIN, OUTER JOIN, USING 조건절, ON 조건절 등으로 가장 다양하게 발전하였다. 

  관계형 데이터베이스의 경우 요구사항 분석, 개념적 데이터 모델링, 논리적 데이터 모델링, 물리적 데이터 모델링 단계를 거치게 되는데, 이 단계에서 엔티티 확정 및 정규화 과정, 그리고 M:M (다대다) 관계를 분해하는 절차를 거치게 된다.

  특히 정규화 과정의 경우 데이터 정합성과 데이터 저장 공간의 절약을 위해 엔티티를 최대한 분리하는 작업으로, 일반적으로 3차 정규형이나 보이스코드 정규형까지 진행하게 된다. 이런 정규화를 거치면 하나의 주제에 관련 있는 엔티티가 여러 개로 나누어지게 되고, 이 엔티티들이 주로 테이블이 되는데 이렇게 흩어진 엔티티를 연결해서 원하는 데이터를 가져오는 작업이 바로 JOIN이다. 

2. FROM 절 JOIN 형태

- INNER JOIN
- NATURAL JOIN
- USING 조건절

- ON 조건절

- CROSS JOIN

- OUTER JOIN

  ANSI / ISO SQL에서 규정한 JOIN 문법은 WHERE 절을 사용하던 기존 JOIN 방식과 차이가 있다. 사용자는 기존 WHERE 절의 검색 조건과 테이블간의 JOIN 조건을 구분 없이 사용하던 방식을 그대로 사용할 수 있으면서, 추가된 선택 기능으로 테이블 간의 JOIN 조건을 FROM 절에서 명시적으로 정의할 수 있게 됬다. 

  INNER JOIN은 WHERE 절에서부터 사용하던 JOIN의 DEFAULT 옵션으로 JOIN 조건에서 동일한 값이 있는 행만 반환한다. 디폴트 옵션으로 생략이 가능하지만, CROSS JOIN, OUTER JOIN과는 같이 사용할 수 없다. 

  NATURAL JOIN은 INNER JOIN의 하위 개념으로 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행한다. NATURAL INNER JOIN이라고도 표시할 수 있으며, 결과는 NATURAL JOIN과 같다. 

  새로운 SQL JOIN 문장 중에서 가장 중요하게 기억해야 하는 문장은 ON 조건절을 사용하는 경우이다. 과거 WHERE 절에서 JOIN 조건과 데이터 검증 조건이 같이 사용되어 용도가 불분명한 경우가 발생할 수 있었는데, WHERE 절의 JOIN 조건을 FROM 절의 ON 조건절로 분리하여 표시함으로써 사용자가 이해하기 쉽도록 한다. 

  ON 조건절의 경우 자연조인처럼 조인 조건이 숨어있지 않고, 명시적으로 조인 조건을 구분할 수 있고, 자연조인이나 USING 조건절 처럼 칼럼명이 똑같아야 된다는 제약 없이 칼럼명이 상호 다르더라도 JOIN 조건으로 사용할 수 있으므로 제일 많이 사용될 것이다. 다만 가독성이 떨어지는 단점이 있다.

  SQL Server의 경우 ON 조건절만 지원하고 NATURAL JOIN과 USING 조건절을 지원하지 않고 있는 것으로 보인다. 

3. INNER JOIN

 

  내부 조인은 외부조인과 대비하여 내부 JOIN 이라고 하며, JOIN 조건에서 동일한 값이 있는 행만 반환한다. INNER JOIN 표시는 그 동안 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시라서, USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다. 

예제) WHERE 절 JOIN 조건

SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO;

위 SQL과 아래 SQL은 같은 결과를 얻을 수 있다.

SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO;

생략하여 JOIN으로 쓸 수 있다.

SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME

FROM EMP JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO;

4. NATURAL JOIN 

  자연조인은 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 동등조인을 수행한다. 자연조인이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없다. 그리고 SQL Server에서는 지원하지 않는 기능이다. 

SELECT DEPTNO, EMPNO, ENAME, DNAME

FROM EMP NATURAL JOIN DEPT;

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

SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME

FROM EMP NATURAL JOIN DEPT;

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

알아서 자동으로 조인해주기 때문에, 굳이 명시적으로 표시할 필요가 없는 것이다. 그리고 예를 들어 와일드카드(*) 처럼 칼럼 순서를 지정하지 않으면 자연조인의 기준이 되는 칼럼들이 다른 칼럼보다 먼저 출력 된다.(DEPTNO) 이 때 자연조인은 조인에 사용된 같은 이름의 칼럼을 하나만 표시한다.

SELECT * FROM EMP NATURAL JOIN DEPT;

DEPTNO    EMPNO    ENAME    ....

------    ------    ------    ----- ...

20    ... 

반면 INNER JOIN의 경우 테이블의 원래 칼럼 순서대로 데이터가 출력된다. 이 때 자연조인은 조인에 사용된 같은 이름의 칼럼을 하나로 처리하지만, INNER JOIN은 별개의 칼럼으로 표시한다.

5. USING 조건절

  자연조인에서 모든 일치되는 칼럼들에 대해 JOIN이 이루어지지만, FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 동등조인을 할 수 있다. 이 기능은 SQL Server에서는 지원하지 않는다.

INNER JOIN의 USING 조건절로 수행

SELECT *

FROM DEPT JOIN DEPT_TEMP

USING (DEPTNO); 

- '*' 와일드 카드 사용 시 USING 조건절의 기준이 되는 칼럼이 다른 칼럼보다 먼저 출력

- USING JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리

- USING 조건절을 이용한 동등조인에서도 자연조인과 마찬가지로 별명, 테이블이름 사용 불가

6. 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);

  자연 조인의 조인 조건은 기본적으로 같은 이름을 가진 모든 칼럼들에 대한 동등 조건이지만, 임의의 JOIN 조건을 지정하거나, 이름이 다른 칼럼명을 JOIN 조건으로 사용하거나, JOIN 칼럼을 명시하기 위해서는 ON 조건절을 사용한다. ON 조건절에 사용된 괄호는 옵션사항이다. 

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

가. 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 절을 사용할 것을 권고한다. 

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 조건절 예제

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 조건절로 구현할 수도 있다.

참고로, 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;

라. 다중 테이블 JOIN

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;

7. CROSS JOIN

  CROSS JOIN은 E.F.CODD 박사가 언급한 일반 집합 연산자의 PRODUCT의 개념으로 테이블간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다. CARTESIAN PRODUCT 또는 CROSS PRODUCT와 같은 표현으로, 결과는 집합의 M*N 건의 데이터 조합이 발생한다.

SELECT ENAME, DNAME

FROM EMP CROSS JOIN DEPT

WHERE EMP.DEPTNO = DEPTNO;

8. OUTER JOIN

  JOIN 조건에서 동일한 값이 없는 행도 반환 할 때 사용할 수 있다. TAB1 테이블이 TAB2 테이블을 JOIN 하되, TAB2의 JOIN 데이터가 있는경우는 TAB2 데이터를 함께 출력하고, 없는 경우에도 TAB1의 모든 데이터를 표시하고 싶은 경우이다. 즉, TAB1의 모든 값에 대해 TAB2의 데이터가 반드시 존재한다는 보장이 없는 경우, OUTER JOIN을 사용하여 해결이 가능하다. 보통 칼럼 뒤에 (+)를 표시한다. 

  STANDARD JOIN을 사용함으로써 OUTER JOIN의 많은 문제점을 해결할 수 있고, 대부분의 관계형 DBMS 간에 호환성을 확보할 수 있으므로 명시적인 OUTER JOIN을 사용할 것을 적극적으로 권장한다고 한다.

가. LEFT OUTER JOIN
  조인 수행 시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어온다. 테이블 A와 B가 있을 떄, A와 B를 비교해서 B 조인 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, B 의 JOIN 칼럼에서 같은 값이 없는 경우에는 NULL 값으로 채운다. LEFT JOIN으로 OUTER를 생략하여 표현할 수 있다. 

나. RIGHT OUTER JOIN

  LEFT OUTER JOIN과 반대로 왼쪽 테이블을 NULL 값으로 채우는 것이다. 

다. FULL OUTER JOIN 

  조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다. 단, UNION ALL이 아닌 UNION 기능과 같으므로 중복되는 데이터는 삭제하게 된다. 즉, 양 측에 NULL 값이 만들어질 수 있다.

9. INNER vs OUTER vs CROSS JOIN 비교

1. INNER JOIN의 결과

 B-B, C-C (2건)

2. LEFT OUTER JOIN의 결과

  B-B, C-C, D-NULL, E-NULL (4건)

3. RIGHT OUTER JOIN의 결과

  NULL-A, B-B, C-C (3건)

4. FULL OUTER JOIN의 결과

  NULL-A, B-B, C-C, D-NULL, E-NULL (5건)

5. CROSS JOIN의 결과

  4 * 3 = 12개의 결과

  B-A, B-B, B-C, C-A, C-B, ...