[oracle] JOIN

재현·2024년 6월 13일

🔍 JOIN

✍ 조인은 두 개의 테이블을 연결하여 하나의 테이블처럼 출력할 때 사용한다. 두 개의 테이블을 연결해서 출력한다는 점에서 조인과 집합 연산자는 비슷하지만 집합 연산자는 두 개 이상의 SELECT 문을 통해 결과 값을 연결한 것이라면 조인은 두 개 이상의 테이블 데이터를 연결했다는 점에서 다르다.

🔍 ORACLE JOIN

🔍 카테시안 곱(Cartesian Product)

✍ 여러 테이블을 연결할 때, 제대로 조건을 걸지 않으면 서로 연관되지 않은 행이 연결되어 불필요한 데이터까지 출력한다. EX) 연결된 테이블 간의 행 갯수를 서로 곱한 값

SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS; -- 2889개의 행 출력

✍ 조인을 알아보기 전 테이블 별칭과 명시하는 법을 다시 알아보자.

-- ERROR = DEPARTMENT_ID가 두 테이블에 전부 존재하기 때문에 조회하려는 테이블을 명시해주어야 한다.
SELECT FIRST_NAME, DEPARTMENT_ID, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS;

-- DEPARTMENT_ID 앞에 테이블 명 명시
SELECT FIRST_NAME, EMPLOYEES.DEPARTMENT_ID, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS;

-- 별칭 테이블 명시
SELECT FIRST_NAME, e.DEPARTMENT_ID, DEPARTMENT_NAME
FROM EMPLOYEES e, DEPARTMENTS d;

🔍 EQUI JOIN

✍ 테이블을 연결한 후에 출력 행을 각 테이블의 특정 열에 일치한 데이터를 기준으로 선정하는 방식입니다. 일반적으로 가장 많이 사용되는 조인 방식이다.

-- ORACLE JOIN
-- EQUI JOIN는 '=' 연산자를 사용하여 조인 조건을 설정
-- 두 테이블 간의 DEPARTMENT_ID 값이 같은 것만 출력
SELECT FIRST_NAME, e.DEPARTMENT_ID, DEPARTMENT_NAME
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;

SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
AND e.DEPARTMENT_ID = 60;

🔍 NON-EQUI JOIN

✍ 두 개의 테이블 간의 컬럼 값들이 정확하게 일치하지 않는 경우에 사용된다. '=' 연산자가 아닌 다른 연산자를 사용한다.

-- NON-EQUI JOIN = 테이블 간의 비교조건이 '='가 아닌 조인
SELECT e.FIRST_NAME, e.SALARY, j.JOB_TITLE
FROM EMPLOYEES e, JOBS j
WHERE e.SALARY BETWEEN j.MIN_SALARY AND j.MAX_SALARY;

🔍 SELF JOIN

✍ 한 테이블을 FROM절에 두 번 명시하고 별칭을 각각 다르게 두어 두 개의 테이블인 것 처럼 JOIN하는 방식이다.

-- SELF JOIN = 같은 테이블 간의 조인을 해야하기 때문에 AS(별칭)이 필수다.
SELECT e.FIRST_NAME, m.FIRST_NAME 
FROM EMPLOYEES e, EMPLOYEES m
WHERE e.MANAGER_ID = M.EMPLOYEE_ID AND e.EMPLOYEE_ID = 103;

🔍 OUTER JOIN(ORACLE)

✍ 두 테이블 간 조인 수행에서 조인 기준 열의 한 쪽이 NULL이어도 출력를 해야하는 조인 방식을 외부 조인(OUTER JOIN)이라고 한다. 오라클에서는 표기를 (+)로 한다.

-- 직무변동이 있는 사원의 정보만 출력 (EQUI JOIN)
SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.HIRE_DATE,
    j.START_DATE, j.END_DATE, j.JOB_ID, j.DEPARTMENT_ID
FROM EMPLOYEES e, JOB_HISTORY j
WHERE e.EMPLOYEE_ID = j.EMPLOYEE_ID
ORDER BY j.EMPLOYEE_ID;

-- OUTER JOIN
SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.HIRE_DATE,
    j.START_DATE, j.END_DATE, j.JOB_ID, j.DEPARTMENT_ID
FROM EMPLOYEES e, JOB_HISTORY j
WHERE e.EMPLOYEE_ID = j.EMPLOYEE_ID(+) -- j테이블의 정보가 없는 e테이블의 사원들도 출력, (+)표시
ORDER BY j.EMPLOYEE_ID;

🔍 ANSI JOIN

✍ 조인의 형태가 FROM절에서 지정되며 조인 조건이 ON절 또는 USING절에 표시, 오라클보다 더 직관적이고 어떤 데이터베이스에서도 사용 가능하다.

🔍 CROSS JOIN

✍ 두 테이블에 대한 카테시안 곱(Cartesian Product) 출력

SELECT e.DEPARTMENT_ID, d.DEPARTMENT_ID
FROM EMPLOYEES e
CROSS JOIN DEPARTMENTS d;

🔍 NATURAL JOIN

✍ 같은 이름을 갖는 컬럼에 대한 조인

-- EMPLOYEES 테이블과 DEPARTMENTS 테이블의 공통 컬럼은 DEPARTMENT_ID, MANAGER_ID 이다.
SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES
NATURAL JOIN DEPARTMENTS;

🔍 USING JOIN

✍ 원하는 컬럼에 대해서만 선택적으로 JOIN이 가능한 조인

SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES
JOIN DEPARTMENTS
USING (DEPARTMENT_ID);

🔍 ON JOIN

✍ 열 이름이 다르거나, 동등조인을 사용하지 못할 경우, JOIN 이후 논리 연산과 서브쿼리 같은 추가 서술을 할 수 있다.

SELECT DEPARTMENT_NAME, STREET_ADDRESS, CITY, STATE_PROVINCE
FROM DEPARTMENTS d
JOIN LOCATIONS l
ON d.LOCATION_ID = l.LOCATION_ID;

🔍 3개 이상 테이블 조인(ANSI, ORACLE)

-- ANSI JOIN(3개 이상 테이블 조인)
-- 오라클에서도 3개 이상 테이블 조인이 가능하지만, WHERE에 여러 개로 JOIN을 명시해야하므로
-- ANSI가 더 직관적으로 조인 조건을 명시할 수 있다.
SELECT e.FIRST_NAME, d.DEPARTMENT_NAME,
    l.STREET_ADDRESS || ', ' || l.CITY || ', ' || L.STATE_PROVINCE
        AS ADDRESS
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
JOIN LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID;

-- ORALCE(3개 이상 테이블 조인)
SELECT e.FIRST_NAME, d.DEPARTMENT_NAME,
    l.STREET_ADDRESS || ', ' || l.CITY || ', ' || l.STATE_PROVINCE
        AS ADDRESS
FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
AND d.LOCATION_ID = l.LOCATION_ID;

-- ANSI JOIN (WHERE)
SELECT e.FIRST_NAME AS NAME, d.DEPARTMENT_NAME AS DEPARTMENT
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE EMPLOYEE_ID = 103;

-- ANSI JOIN (WHERE절 포함 3개 이상 테이블 조인)
SELECT e.FIRST_NAME AS NAME, d.DEPARTMENT_NAME AS DEPARTMENT,
    l.STREET_ADDRESS || ', ' || l.CITY || ', ' || l.STATE_PROVINCE
        AS ADDRESS
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
JOIN LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID
WHERE EMPLOYEE_ID = 103;

-- -- ANSI JOIN (WHERE절 생략)
SELECT e.FIRST_NAME AS NAME, d.DEPARTMENT_NAME AS DEPARTMENT
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID AND EMPLOYEE_ID = 103;

-- 3개 이상 테이블 조인과 WHERE 절 생략을 합쳐서 사용한 조인
-- 오라클에서 FROM 절에 3개 테이블을 명시해서 사용하는 조인보다 ANSI JOIN이 명시가 되어 가독성이 좋듯이
-- ON 절 뒤에 AND로 WHERE절을 생략할 수 있지만 가독성이 좋지 않아서 WHERE절을 명시하는 것이 좋다.
SELECT e.FIRST_NAME AS NAME, d.DEPARTMENT_NAME AS DEPARTMENT,
    l.STREET_ADDRESS || ', ' || l.CITY || ', ' || l.STATE_PROVINCE
        AS ADDRESS
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
JOIN LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID AND EMPLOYEE_ID = 103;

🔍 OUTER JOIN(ANSI)

✍ 두 테이블 간 조인 수행에서 조인 기준 열의 한 쪽이 NULL이어도 출력를 해야하는 조인 방식을 외부 조인(OUTER JOIN)이라고 한다. ANSI 기준에서는 LEFT/RIGHT/FULL 등이 있다.

-- OUTER JOIN(ANSI) = 오라클에서 제공하는 OUTER JOIN 표기(+)는 이해가 어렵고 실수를 유발하기 쉽다는 단점이 있다.
-- ANSI는 LEFT, RIGHT로 제공하기 때문에 가독성이 좋을 뿐만 아니라 FULL로 양쪽 OUTER JOIN을 지원한다.
-- LEFT/RIGHT/FULL OUTER JOIN A,B가 있다면 LEFT는 A가, RIGHT B가, FULL은 둘 다 기준이 된다.
SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.HIRE_DATE,
    j.START_DATE, j.END_DATE, j.JOB_ID, j.DEPARTMENT_ID
FROM EMPLOYEES e
LEFT OUTER JOIN JOB_HISTORY j
ON e.EMPLOYEE_ID = j.EMPLOYEE_ID
ORDER BY j.EMPLOYEE_ID;

SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.HIRE_DATE,
    j.START_DATE, j.END_DATE, j.JOB_ID, j.DEPARTMENT_ID
FROM EMPLOYEES e
RIGHT OUTER JOIN JOB_HISTORY j
ON e.EMPLOYEE_ID = j.EMPLOYEE_ID
ORDER BY j.EMPLOYEE_ID;

SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.HIRE_DATE,
    j.START_DATE, j.END_DATE, j.JOB_ID, j.DEPARTMENT_ID
FROM EMPLOYEES e
FULL OUTER JOIN JOB_HISTORY j
ON e.EMPLOYEE_ID = j.EMPLOYEE_ID
ORDER BY j.EMPLOYEE_ID;