[SQL] JOIN 조인

# SELF JOIN ( A = B)

-- 셀프조인
SELECT A.EMPLOYEE_ID, A.EMP_NAME, B.EMPLOYEE_ID, B.EMP_NAME, A.DEPARTMENT_ID
FROM EMPLOYEES A, EMPLOYEES B
WHERE A.EMPLOYEE_ID < B.EMPLOYEE_ID
      AND A.DEPARTMENT_ID = B.DEPARTMENT_ID
      AND A.DEPARTMENT_ID = 60
ORDER BY A.EMPLOYEE_ID, B.EMPLOYEE_ID;

# INNER JOIN ( A ∩ B)

-- INNER JOIN
-- ORACLE JOIN (이퀄조인이라고도 함)
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.JOB_ID, B.DEPARTMENT_ID
FROM DEPARTMENTS A, JOB_HISTORY B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;

-- ANSI JOIN
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.JOB_ID, B.DEPARTMENT_ID
FROM DEPARTMENTS A INNER JOIN JOB_HISTORY B
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID;

♨주의 INNER JOIN은 NULL 값이 있는 경우 제외한다.

# RIGHT OUTER JOIN 

-- RIGHT OUTER JOIN
-- ORACLE JOIN
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.JOB_ID, B.DEPARTMENT_ID
FROM DEPARTMENTS A, JOB_HISTORY B
WHERE A.DEPARTMENT_ID(+) = B.DEPARTMENT_ID;  -- RIGHT JOIN +부호 왼쪽

--ANSI JOIN
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.JOB_ID, B.DEPARTMENT_ID
FROM DEPARTMENTS A RIGHT OUTER JOIN JOB_HISTORY B
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID;

# LEFT OUTER JOIN

-- LEFT OUTER JOIN
-- ORACLE JOIN
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.JOB_ID, B.DEPARTMENT_ID
FROM DEPARTMENTS A, JOB_HISTORY B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID(+);  -- LEFT면 +부호 오른쪽에

--ANSI JOIN
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.JOB_ID, B.DEPARTMENT_ID
FROM DEPARTMENTS A LEFT OUTER JOIN JOB_HISTORY B
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID;

# FULL OUTER JOIN (A∪B)

양쪽 테이블 열 기준으로 모든 데이터를 가져온다. 값이 없다면 NULL을 채워서 반환 

-- FULL OUTER JOIN (ANSI JOIN만 가능)
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.JOB_ID, B.DEPARTMENT_ID
FROM DEPARTMENTS A FULL OUTER JOIN JOB_HISTORY B
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID;

 

# CROSS JOIN (CARTESIAN PRODUCT)

-- CROSS JOIN
-- 카테시안 프로덕트 N X M개의 행이 생성된다.
-- WHERE 절의 조건이 없는 형태
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.JOB_ID, B.DEPARTMENT_ID
FROM DEPARTMENTS A, JOB_HISTORY B;

---

# USING

-- USING
-- JOIN 조건을 지정해주는 칼럼이 두 테이블 모두 동일한경우 ON 대신 USING을 사용할 수 있음
-- 치명적 단점
-- 1. 테이블에 칼럼 이름이 다 같을 것이라는 보장이 없다!(다른 테이블에서는 DEPT_ID일지도?)
-- 2. DEPARTMENT_ID는 무조건 DEPARTMENT_ID라고 해야함(A.DEPARTMENT_ID라고하면 안됨)
SELECT A.EMPLOYEE_ID, A.EMP_NAME, DEPARTMENT_ID, B.DEPARTMENT_NAME
FROM EMPLOYEES A INNER JOIN DEPARTMENTS B
USING (DEPARTMENT_ID) -- 식별자에 괄호 쳐줘야 함
WHERE  A.HIRE_DATE >=  TO_DATE('2003-01-01','YYYY-MM-DD');

ERROR

---

# 테이블 3개 JOIN

-- ONE MORE TABLE ? INNER JOIN 예시
-- ORACLE JOIN
SELECT A.EMPLOYEE_ID, B.DEPARTMENT_ID, B.DEPARTMENT_NAME,
       C.START_DATE
FROM EMPLOYEES A, DEPARTMENTS B, JOB_HISTORY C
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
      AND A.EMPLOYEE_ID = C.EMPLOYEE_ID
ORDER BY 1;

-- ANSI JOIN (테이블을 하나씩 붙여가는 구조)
SELECT A.EMPLOYEE_ID, B.DEPARTMENT_ID, B.DEPARTMENT_NAME,
       C.START_DATE
FROM EMPLOYEES A INNER JOIN DEPARTMENTS B
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
INNER JOIN JOB_HISTORY C
ON A.EMPLOYEE_ID = C.EMPLOYEE_ID
ORDER BY 1;

혹시 ON에 여러 조건을 넣어 줄 수 있을까?

-- ON에 여러조건 넣는것도 가능한가?
-- 처음 들어왔을때와 현재의 부서가 변경되지 않은사람들
SELECT A.EMPLOYEE_ID, B.DEPARTMENT_ID, B.DEPARTMENT_NAME,
       C.START_DATE
FROM EMPLOYEES A INNER JOIN DEPARTMENTS B
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
INNER JOIN JOB_HISTORY C
ON A.EMPLOYEE_ID = C.EMPLOYEE_ID
   AND A.DEPARTMENT_ID = C.DEPARTMENT_ID
ORDER BY 1;