08. 여러 테이블을 하나의 테이블처럼 사용하는 조인

08 - 1 조인

집합 연산자와 조인의 차이점

• 조인은 두 개 이상의 테이블 데이터를 가로로 연결하여 하나의 테이블처럼 출력할 때 사용하는 방식이다.
• 집합 연산자를 사용한 결과는 두 개 이상의 SELECT문의 결과 값을 세로로 연결한 것이다.

여러 테이블을 사용할 때의 FROM절

• 꼭 테이블이 아니더라도 테이블 형태, 즉 열과 행으로 구성된 데이터 집합이면 모두 FROM절에 지정 가능하다. SELECT절의 여러 열을 구분할 때와 마찬가지로 FROM절에 여러 테이블을 명시할 때 쉼표(,)를 구분자로 사용하여 지정한다. 그리고 WHERE, GROUP BY, ORDER BY절 등 다른 절도 그대로 사용할 수 있다.

SELECT
FROM 테이블1, 테이블2, ..., 테이블N

• FROM절에 여러 테이블 선언하기

SELECT *
FROM EMP, DEPT
ORDER BY EMPNO;

이렇게 EMP, DEPT 테이블을 FROM절에 함께 명시하여 출력하면 생각보다 많은 양의 데이터가 FROM절에 명시학 각 테이블을 구성하는 행이 모든 경우의 수로 조합되어 출력되기 때문이다.

위 이미지와 같이 각 집합을 이루는 모든 원소의 순서쌍을 데카르트 곱이라고 한다. 조인 이름으로는 크로스 조인 또는 교차 조인이라고도 한다.

조인 조건이 없을 때의 문제점

• EMP 테이블 SMITH 사원이 있는 행을 보면 DEPTNO가 20이고 DEPT 테이블의 DEPTNO 열 값이 20인 행이 된다. 하지만 조건 없이 결과를 출력하면 사원 데이터와 부서 데이터가 정확히 맞아떨어지지 않는 데이터도 함께 출력된다.

• 그러므로 출력 행을 선정하는 조건식을 명시하는 WHERE절이 중요한 역할을 한다. 따라서 서로 다른 테이블인 EMP 테이블과 DEPT 테이블에 같은 이름의 DEPTNO 열을 구별하는 방법이 필요하다.
• 이 때 사용하는 것이 바로 열 앞에 테이블 이름을 명시하여 특정 열이 어느 테이블에 속한 열인지를 구별하는 방식이다.

🍟 테이블 이름.열 이름

---

SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
ORDER BY EMPNO;

FROM절에 WHERE절을 사용해 출력하려는 조건을 걸어 두 테이블의 DEPTNO 열 값이 같은 14개 행만 출력된다. 그리고 각 행별 부서 정보도 정확하게 연결되었음을 확인할 수 있다.

테이블 별칭 설정

• FROM절에 지정한 테이블에는 SELECT절의 열에 사용한 것처럼 별칭을 지정할 수 있다.

FROM 테이블 이름1 별칭1, 테이블 이름2 별칭2 ...

지정한 별칭은 테이블의 열을 지칭하는 데 사용할 수 있다.

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY EMPNO;

---

08 - 2 조인 종류

• 두 개 이상의 테이블을 하나의 테이블처럼 가로로 늘어뜨려 출력하기 위해 사용하는 조인은 대상 데이터를 어떻게 연결하느냐에 따라 등가 조인, 비등가 조인, 자체 조인, 외부 조인 등으로 구분한다.

등가 조인

• 등가 조인은 테이블을 연결한 후에 출력 행을 각 테이블의 특정 열에 일치한 데이터를 기준으로 선정하는 방식이다. 등가 조인은 내부 조인 또는 단순 조인으로 부르기도 한다.
• 등가 조인은 일반적으로 가장 많이 사용되는 조인 방식이다.

• 여러 테이블의 열 이름이 같을 때 유의점

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

DEPTNO처럼 등가 조인을 사용할 때 조인 조건이 되는 각 테이블의 열 이름이 같을 경우에 해당 열 이름을 테이블 구분 없이 명시하면 오류가 발생하여 실행되지 못합니다.

• 열 이름에 각각의 테이블 이름도 함께 명시할 때

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME, D.LOC // D.DEPTNO -> E.DEPTNO 가능
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY D.DEPTNO, E.EMPNO;

• 조인 테이블 개수와 조건식 개수의 관계

기본적으로 데카르트 곱 현상이 일어나지 않게 하는 데 필요한 조건식의 최소 개수는 조인 테이블 개수에서 하나를 뺀 값이다.

비등가 조인

사원 정보와 더불어 사원의 급여 등급 정보를 함께 출력하고자 한다면 EMP 테이블과 SALGRADE 테이블을 조인해야 한다. 하지만 사용하는 열의 일치 여부를 기준으로 테이블을 조인하는 등가 조인 방식은 이 두 테이블의 연결에 적합하지 않다. 왜냐하면 급여 등급을 맞춰 주려면 사원의 급여 금액이 일치하는 것이 아니라 최소 급여(LOSAL)와 최대 급여(HISAL) 사이에 있어야 하기 때문이다.

SELECT *
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

• 비등가 조인 방식은 등가 조인 방식에 비해 그리 자주 사용하는 방식은 아니다.

---

자체 조인

• 자체 조인은 하나의 테이블을 여러 개의 테이블처럼 활용하여 조인하는 방식이고 FROM절에 같은 테이블을 여러 번 명시하되 테이블의 별칭만 다르게 지정하는 방식으로 사용한다.

• EMP테이블을 자체 조인을 사용하여 E1.MGR = E2.EMPNO를 나란히 출력한다. 하나의 테이블 이지만 SELECT문 내부에서 별칭을 각각 달리주어 논리적으로 다른 테이블인 것처럼 명시하여 두 테이블을 조인하는 방식이다.

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO;

외부 조인 설명좀

• 조인 조건 데이터 중 어느 한쪽이 NULL임에도 결과를 출력할 때 포함시켜야 하는 경우가 종종 있다. 두 테이블ㄱ간 조인 수행에서 조인 기준 열의 어느 한쪽이 NULL이어도 강제로 출력하는 방식을 외부 조인이라고 한다.
• 외부 조인은 좌우를 따로 나누어 지정하는데 WHERE절에 조인 기준 열 중 한쪽에(+) 기호를 붙여 준다.

왼쪽 외부 조인(Left Outer Join)	WHERE TABLE1.COL1 = TABLE2.COL1(+)
오른쪽 외부 조인(Right Outer Join)	WHERE TABLE1.CON1(+) = TABLE2.COL1

• 왼쪽 외부 조인 사용하기

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO(+)
ORDER BY E1.EMPNO;

• 오른쪽 외부 조인 사용하기

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR(+) = E2.EMPNO
ORDER BY E1.EMPNO;

• 왼쪽 외부 조인은 간단히 말해서 왼쪽 열을 기준으로 오른쪽 열의 데이터 존재 여부에 상관없이 출력하라는 뜻이다.
• 오른쪽 외부 조인은 오른쪽 열을 기준으로 왼쪽 열 데이터의 존재와 상관없이 데이터를 출력하라는 뜻이다.

---

08 - 3 SQL-99 표준 문법으로 배우는 조인

• 오라클은 9i 버전부터 SQL-99 방식의 문법을 지원한다.
• 앞에서 배운 조인 방식과 기능은 같지만 조인을 사용하는 문법에서 다소 차이가 난다. 그리고 다른 DBMS 제품에서도 사용할 수 있다.

NATURAL JOIN

• 등가 조인을 대신해 사용할 수 있는 조인 방식으로 조인 대상이 되는 두 테이블에 이름과 자료형이 같은 열을 찾은 후 그 열을 기준으로 등가 조인을 해 주는 방식이다.
• EMP 테이블과 DEPT 테이블은 공통 열 DEPTNO를 가지고 있으므로 NATURAL JOIN을 사용할 때 자동으로 DEPTNO 열을 기준으로 등가 조인된다.
• 기존 등가 조인과 다르게 조인 기준 열인 DEPTNO를 SELECT절에 명시할 때 테이블 이름을 붙이면 안 되는 특성이 있다.

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM,
DEPTNO, D.DNAME, D.LOC
FROM EMP E NATURAL JOIN DEPT D
ORDER BY DEPTNO, E.EMPNO;

JOIN ~ USING

• 이 조인 역시 등가 조인을 대신하는 방식이다. USING 키워드에 조인 기준으로 사용할 열을 명시하여 사용한다.

FROM TABLE1 JOIN TABLE2 USING (조인에 사용한 기준열)

• 다른 조인 방식과 마찬가지로 조인된 결과 행을 추가로 제한할 때 WHERE절에 조건식을 추가하여 함께 사용할 수 있다.
• 조인 기준 열로 명시된 열은 SELECT절에서 테이블 이름을 붙이지 않고 작성한다.

JOIN ~ ON

• 가장 범용성 있는 조인 방식이다.
• 기존 WHERE절에 있는 조인 조건식을 ON 키워드 옆에 작성한다.
• 조인 기준 조건식은 ON에 명시하고 그 밖의 출력행을 걸러 내기 위해 WHERE 조건식을 따로 사용하는 방식이다.

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM,
E.DEPTNO, 
D.DNAME, D.LOC
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE SAL <= 3000
ORDER BY E.DEPTNO, EMPNO;

OUTER JOIN

• OUTER JOIN 키워드는 외부 조인에 사용한다.

왼쪽 외부 조인 (Left Outer Join)	기존	WHERE TABLE1.COL1 = TABLE2.COL1(+)
	SQL-99	FROM TABLE1 LEFT OUTER JOIN TABLE2 ON (조인 조건식)
오른쪽 외부 조인 (Right Outer Join)	기존	WHERE TABLE1.COL1(+) = TABLE2.COL1
	SQL-99	FROM TABLE1 RIGHT OUTER JOIN TABLE2.ON (조인 조건식)
전체 외부 조인 (Full Outer Join)	기존	기본 문법은 없음 (UNION 집합 연산자를 활용)
	SQL-99	FROM TABLE1 FULL OUTER JOIN TABLE2 ON (조인 조건식)

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1 LEFT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
ORDER BY E1.EMPNO;

• 전체 외부 조인은 왼쪽 · 오른쪽 외부 조인을 모두 적용한, 즉 왼쪽 열이 NULL인 경우와 오른쪽 열이 NULL인 경우를 모두 출력하는 방식이다.
• SQL-99 방식의 외부 조인은 FULL OUTER JOIN ~ ON 키워드로 양쪽 모두 외부 조인된 결과 값을 출력할 수 있다.

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1 FULL OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
ORDER BY E1.EMPNO;

• SQL-99 조인은 FROM절에 특정 키워드를 사용함으로써 기존 WHERE절에 조건식으로 조인하는 조인 방식보다 더 간략하고 명시적으로 어떤 방식의 조인을 사용하고 있는지 알 수 있다.
• 조인 조건식과 출력 행을 선정하는 조건식을 구별할 수 있으므로 여러 테이블을 조인해야 하는 복잡한 SELECT문에서 SQL-99 조인의 장점이 드러난다.

SQL-99 조인 방식에서 세 개 이상의 테이블을 조인할 때

기존 조인 방식은 FROM절에 조인 테이블을 명시하고 조인 관련 조건식을 WHERE절에 명시하기 때문에 테이블 수가 두 개를 넘더라도 다음과 같이 작성하면 아무 문제가 없다

FROM TABLE1, TABLE2 TABLE3
WHERE TABLE1.COL = TABLE2.COL
AND TABLE2.COL = TABLE3.COM

하지만 FROM절에 조인 관련 내용을 작성해야 하는 SQL-99 방식에서는 FROM절에 두 개 테이블을 키워드로 조인한 바로 옆에 SQL-99 방식의 조인 내용을 추가로 작성하면 세 개 이상의 테이블도 조인할 수 있다.

FROM TABLE1 JOIN TABLE2 ON (조인 조건식)
JOIN TABLE3 ON (조건식)

잊기 전에 한 번 더!

• Q1. 급여(SAL)가 2000 초과인 사원들의 부서 정보, 사원 정보를 오른쪽과 같이 출력해 보세요. (단 SQL-99 이전 방식과 SQL-99 방식을 각각 사용하여 작성하세요.)

// SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND SAL > 2000;

// SQL-99 방식
SELECT DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
FROM EMP E JOIN DEPT D USING (DEPTNO)
WHERE SAL > 2000;

• Q2. 오른쪽과 같이 각 부서별 평균 급여, 최대 급여, 최소 급여, 사원수를 출력해 보세요. (단 SQL-99 이전 방식과 SQL-99 방식을 각각 사용하여 작성하세요.)

// SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME,
TRUNC(AVG(E.SAL)) AS AVG_SAL,
MAX(E.SAL) AS MAX_SAL,
MIN(E.SAL) AS MIN_SAL,
COUNT(*) AS CNT
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO, D.DNAME;

// SQL-99 방식
SELECT DEPTNO, D.DNAME, TRUNC(AVG(E.SAL)) AS AVG_SAL,
MAX(E.SAL) AS MAX_SAL,
MIN(E.SAL) AS MIN_SAL,
COUNT(*) AS CNT
FROM EMP E JOIN DEPT D USING (DEPTNO)
GROUP BY DEPTNO, D.DNAME;

• Q3. 모든 부서 정보와 사원 정보를 오른쪽과 같이 부서 번호, 사원 이름순으로 정렬하여 출력해 보세요. (단 SQL-99 이전 방식과 SQL-99 방식을 각각 사용하여 작성하세요.)

// SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
ORDER BY D.DEPTNO, E.ENAME; 

// SQL-99 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
FROM EMP E RIGHT OUTER JOIN DEPT D ON (D.DEPTNO = E.DEPTNO)
ORDER BY DEPTNO, E.ENAME;

// DEPTNO, D.DEPTNO ㅠㅠ ?

• Q4. 다음과 같이 모든 부서 정보, 사원 정보, 급여 등급 정보, 각 사원의 직속 상관의 정보를 부서 번호, 사원 번호 순서로 정렬하여 출력해 보세요. (단 SQL-99 이전 방식과 SQL-99 방식을 각각 사용하여 작성하세요.)

// SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME,
       E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO,
       S.LOSAL, S.HISAL, S.GRADE,
       E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
  FROM EMP E, DEPT D, SALGRADE S, EMP E2
 WHERE E.DEPTNO(+) = D.DEPTNO
   AND E.SAL BETWEEN S.LOSAL(+) AND S.HISAL(+)
   AND E.MGR = E2.EMPNO(+)
ORDER BY D.DEPTNO, E.EMPNO;

// SQL-99 방식
SELECT D.DEPTNO, D.DNAME,
       E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO,
       S.LOSAL, S.HISAL, S.GRADE,
       E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
  FROM EMP E RIGHT OUTER JOIN DEPT D
                ON (E.DEPTNO = D.DEPTNO)
              LEFT OUTER JOIN SALGRADE S
                ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
              LEFT OUTER JOIN EMP E2
                ON (E.MGR = E2.EMPNO)
ORDER BY D.DEPTNO, E.EMPNO;