Day 4 - 조인 (EQUI·Non-EQUI·(+))

두 테이블을 잇는다는 것

EMP 에는 부서 번호(DEPTNO)만 있고 부서 이름은 DEPT 에 있다. "사원 이름과 부서 이름"을 함께 보려면 두 테이블의 행을 DEPTNO 가 같은 것끼리 짝지어야 한다. 이것이 조인이다.

EMP                                DEPT
ENAME   DEPTNO                     DEPTNO  DNAME
CLARK   10   ───────────────────▶  10      ACCOUNTING
KING    10   ───────────────────▶  10      ACCOUNTING
SMITH   20   ───────────────────▶  20      RESEARCH
...                                30      SALES
                                   40      OPERATIONS   ← 짝이 없다

Oracle 의 전통 표기는 FROM 에 테이블을 쉼표로 나열하고, 짝짓는 조건을 WHERE 에 쓴다.

EQUI 조인

SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
  AND E.DEPTNO = 10
ORDER BY E.ENAME;

ENAME          DEPTNO DNAME
---------- ---------- --------------
CLARK              10 ACCOUNTING
KING               10 ACCOUNTING
MILLER             10 ACCOUNTING

• E, D는 테이블 별칭이다. FROM 에서 테이블 이름 뒤에 한 칸 띄우고 쓴다(AS는 쓰지 않는다).
• WHERE 에 조인 조건(E.DEPTNO = D.DEPTNO)과 일반 조건(E.DEPTNO = 10)이 AND로 함께 있다.

조인 결과 전체의 행 수를 세어 본다.

SELECT COUNT(*) FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

  COUNT(*)
----------
        14

• 사원 14명이 모두 짝을 찾아 14행이다. 짝이 없는 DEPT 40 은 결과에 나오지 않는다. EQUI 조인은 양쪽에 모두 짝이 있는 행만 남긴다.

열 이름이 겹칠 때 — ORA-00918

DEPTNO 는 EMP 와 DEPT 양쪽에 있다. 어느 테이블 것인지 밝히지 않으면 Oracle 이 고를 수 없다.

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

SELECT ENAME, DEPTNO, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
              *
ERROR at line 1:
ORA-00918: DEPTNO: column ambiguously specified - appears in DEPT and EMP

• ENAME 과 DNAME 은 한 테이블에만 있어 접두사 없이도 된다.
• 한쪽에만 있는 열이라도 접두사를 붙이면 읽기 쉽고, 나중에 같은 이름 열이 생겨도 깨지지 않는다.

별칭을 정했으면 별칭으로

FROM 에서 별칭을 주면 그 쿼리 안에서 원래 테이블 이름은 접두사로 쓸 수 없다.

SELECT EMP.ENAME FROM EMP E WHERE E.EMPNO = 7839;

SELECT EMP.ENAME FROM EMP E WHERE E.EMPNO = 7839
       *
ERROR at line 1:
ORA-00904: "EMP"."ENAME": invalid identifier

• 별칭 E가 EMP 를 대신하므로 EMP.ENAME이라는 열은 없는 것으로 본다.

카테시안 곱 — 조건을 빠뜨리면

조인 조건 없이 테이블만 나열하면 한쪽의 모든 행이 다른 쪽의 모든 행과 짝지어진다.

SELECT COUNT(*) FROM EMP, DEPT;

  COUNT(*)
----------
        56

SELECT COUNT(*) FROM EMP, SALGRADE;

  COUNT(*)
----------
        70

• EMP 14 × DEPT 4 = 56, EMP 14 × SALGRADE 5 = 70 이다.
• 의도하지 않은 카테시안 곱은 행 수를 크게 부풀린다. 결과가 이상하게 많으면 조인 조건부터 센다.

테이블이 셋인데 조건이 하나뿐이어도 남은 테이블과는 곱이 생긴다.

SELECT COUNT(*) FROM EMP E, DEPT D, SALGRADE S WHERE E.DEPTNO = D.DEPTNO;

  COUNT(*)
----------
        70

• EMP-DEPT 는 이어져 14행이 되지만, SALGRADE 와는 조건이 없어 14 × 5 = 70 이다.
• 테이블 N 개를 모두 이으려면 조인 조건이 N-1 개 이상 필요하다.

Non-EQUI 조인

조인 조건이 반드시 = 일 필요는 없다. SALGRADE 는 급여 구간(LOSAL ~ HISAL)마다 등급을 매긴 표다.

GRADE  LOSAL  HISAL
    1    700   1200
    2   1201   1400
    3   1401   2000
    4   2001   3000
    5   3001   9999

사원의 급여가 어느 구간에 드는지로 짝지으면 등급을 붙일 수 있다.

SELECT E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
  AND E.DEPTNO = 30
ORDER BY S.GRADE, E.ENAME;

ENAME             SAL      GRADE
---------- ---------- ----------
JAMES             950          1
MARTIN           1250          2
WARD             1250          2
ALLEN            1600          3
TURNER           1500          3
BLAKE            2850          4

• EMP 와 SALGRADE 에는 같은 값을 가진 열이 없다. BETWEEN으로 범위에 드는 행을 짝지었다.
• 구간이 서로 겹치지 않아 사원마다 정확히 한 등급과 짝지어진다.

등급별 인원을 세면 Non-EQUI 조인과 GROUP BY 를 함께 쓰게 된다.

SELECT S.GRADE, COUNT(*)
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
GROUP BY S.GRADE
ORDER BY S.GRADE;

     GRADE   COUNT(*)
---------- ----------
         1          3
         2          3
         3          2
         4          5
         5          1

• 합계는 3 + 3 + 2 + 5 + 1 = 14 로 사원 수와 같다.

<>나 >도 조인 조건이 될 수 있다. 이런 조건은 한 행이 여러 행과 짝지어져 결과가 커지기 쉽다.

SELECT COUNT(*) FROM EMP E, DEPT D WHERE E.DEPTNO <> D.DEPTNO;

  COUNT(*)
----------
        42

• 사원마다 자기 부서가 아닌 3개 부서와 짝지어져 14 × 3 = 42 다.

세 테이블 조인

조건을 하나씩 늘리면 테이블을 계속 이을 수 있다. 관리자(MANAGER)의 이름, 부서 이름, 급여 등급을 한 번에 본다.

SELECT E.ENAME, D.DNAME, S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO
  AND E.SAL BETWEEN S.LOSAL AND S.HISAL
  AND E.JOB = 'MANAGER'
ORDER BY E.ENAME;

ENAME      DNAME               GRADE
---------- -------------- ----------
BLAKE      SALES                   4
CLARK      ACCOUNTING              4
JONES      RESEARCH                4

• 테이블 3개, 조인 조건 2개(EQUI 하나, Non-EQUI 하나)로 N-1 규칙을 채웠다.
• E.JOB = 'MANAGER'는 조인 조건이 아니라 행을 거르는 일반 조건이다.

Oracle 외부 조인 (+)

EQUI 조인은 짝이 없는 DEPT 40 을 버린다. 짝이 없어도 한쪽 테이블의 행을 전부 남기려면 외부 조인을 쓴다. Oracle 표기는 짝이 없어 NULL 로 채워질 쪽 열에 (+) 를 붙인다.

SELECT D.DEPTNO, D.DNAME, E.ENAME
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
  AND D.DEPTNO IN (10, 40)
ORDER BY D.DEPTNO, E.ENAME;

    DEPTNO DNAME          ENAME
---------- -------------- ----------
        10 ACCOUNTING     CLARK
        10 ACCOUNTING     KING
        10 ACCOUNTING     MILLER
        40 OPERATIONS

• (+)가 EMP 쪽에 붙었으므로 DEPT 의 모든 행이 남는다. 40번은 짝지을 사원이 없어 ENAME 이 NULL 이다.
• 기억하는 방법: (+)는 "모자란 쪽에 NULL 행을 더해 준다"는 표시다. 남기고 싶은 테이블의 반대편에 붙인다.

SELECT COUNT(*) FROM EMP E, DEPT D WHERE E.DEPTNO(+) = D.DEPTNO;

  COUNT(*)
----------
        15

SELECT COUNT(*) FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO(+);

  COUNT(*)
----------
        14

• EMP 를 남기는 쪽은 14행 그대로다. 부서가 없는 사원이 없어 NULL 로 채울 행이 생기지 않는다.

외부 조인 결과를 부서별로 셀 때는 무엇을 세는지가 중요하다.

SELECT D.DEPTNO, COUNT(E.EMPNO) AS CNT, COUNT(*) AS ROWS_
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
GROUP BY D.DEPTNO
ORDER BY D.DEPTNO;

    DEPTNO        CNT      ROWS_
---------- ---------- ----------
        10          3          3
        20          5          5
        30          6          6
        40          0          1

• 40번은 NULL 로 채운 행이 1개 있다. COUNT(*)는 그 행을 세어 1, COUNT(E.EMPNO)는 NULL 을 빼 0 이다. 사원 수는 COUNT(E.EMPNO)가 맞다.

다른 조건에도 (+)를 붙여야 한다

외부 조인 쪽 테이블에 거는 일반 조건에 (+)를 빠뜨리면 외부 조인이 풀린다.

SELECT D.DNAME, E.ENAME
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
  AND E.SAL(+) > 2900
ORDER BY D.DNAME, E.ENAME;

DNAME          ENAME
-------------- ----------
ACCOUNTING     KING
OPERATIONS
RESEARCH       FORD
RESEARCH       JONES
RESEARCH       SCOTT
SALES

SELECT D.DNAME, E.ENAME
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
  AND E.SAL > 2900
ORDER BY D.DNAME, E.ENAME;

DNAME          ENAME
-------------- ----------
ACCOUNTING     KING
RESEARCH       FORD
RESEARCH       JONES
RESEARCH       SCOTT

• E.SAL(+) > 2900: 급여 조건을 짝짓는 단계에 넣는다. 조건에 맞는 사원이 없는 SALES·OPERATIONS 도 NULL 과 함께 남는다.
• E.SAL > 2900: 외부 조인으로 만든 NULL 행에 이 조건을 적용하면 NULL > 2900은 참이 아니라 떨어진다. 결과는 EQUI 조인과 같아진다.

⚠️ 함정: (+)는 한쪽에만 붙일 수 있다. E.DEPTNO(+) = D.DEPTNO(+)처럼 양쪽에 붙이면 ORA-01468(a predicate may reference only one outer-joined table) 오류다. 양쪽을 모두 남기는 FULL OUTER JOIN 은 내일 볼 ANSI 표기로만 쓸 수 있다.

내일은 같은 조인을 JOIN ... ON 같은 ANSI 표준 표기로 쓰고, ON 과 WHERE 의 차이를 본다.

📖 용어

• 조인 : 두 테이블 이상의 행을 조건에 따라 짝지어 한 결과로 만드는 연산.
• EQUI 조인 : = 조건으로 짝짓는 조인. 양쪽에 짝이 있는 행만 남는다.
• Non-EQUI 조인 : BETWEEN·>·<>처럼 =가 아닌 조건으로 짝짓는 조인.
• 카테시안 곱 : 조인 조건 없이 모든 행끼리 짝지은 결과. 행 수는 두 테이블 행 수의 곱이다.
• 테이블 별칭 : FROM 에서 테이블에 붙이는 짧은 이름. 붙이면 그 쿼리에서는 별칭으로만 부른다.
• 외부 조인 : 짝이 없는 행도 버리지 않고 반대편을 NULL 로 채워 남기는 조인.
• (+) : Oracle 외부 조인 표기. NULL 로 채워질 쪽 열에 붙인다.
• ORA-00918 : 여러 테이블에 있는 같은 이름의 열을 접두사 없이 썼을 때의 오류.