SQL - 조인(Join)

1) 동등 조인

-- 동등 조인(Inner Join) 
SELECT TO_CHAR(e.hire_date, 'MM'), SUM(j.max_salary) 
FROM EMPLOYEES E, JOBS J 
WHERE (E.JOB_ID = J.JOB_ID) 
GROUP BY TO_CHAR(e.hire_date, 'MM'); 

-- ANSI 조인 
SELECT TO_CHAR(e.hire_date, 'MM'), SUM(j.max_salary) 
FROM EMPLOYEES E INNER JOIN JOBS J ON (E.JOB_ID = J.JOB_ID) 
GROUP BY TO_CHAR(e.hire_date, 'MM'); 

-- 예제
SELECT A.EMPLOYEE_ID, A.DEPARTMENT_ID, B.DEPARTMENT_NAME, 
       C.LOCATION_ID, C.CITY 
FROM EMPLOYEES A, DEPARTMENTS B, LOCATIONS C 
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID  
    AND B.LOCATION_ID = C.LOCATION_ID; 

-- HR 스키마의 모든 테이블을 JOIN하기 
-- 조인 조건에 따라 결과값이 다르게 출력 
SELECT *  
FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l, COUNTRIES c, 
     REGIONS r, JOB_HISTORY j, JOBS j2 
WHERE (e.department_id = d.department_id) 
    AND (d.location_id = l.location_id) 
    AND (l.country_id = c.country_id) 
    AND (r.region_id = c.region_id) 
    AND (j.department_id = e.department_id) 
    AND (j.job_id = j2.job_id);

 

2) 외부 조인

-- LEFT JOIN, RIGHT JOIN, FULL JOIN 
SELECT * FROM EMPLOYEES e, DEPARTMENTS d 
WHERE (e.department_id(+) = d.department_id); 

SELECT * FROM EMPLOYEES e RIGHT OUTER JOIN DEPARTMENTS d 
         ON (e.department_id = d.department_id);  -- 위 조인과 동일한 결과 

SELECT * FROM EMPLOYEES e FULL JOIN DEPARTMENTS d -- FULL OUTER JOIN 
         ON (e.department_id = d.department_id);  -- OUTER 키워드 생략 가능

 

3) 자체 조인

-- 예제 employees 테이블을 자체 조인하여 직원별 담당 매니저가 누구인지 조회 
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.LAST_NAME, A.MANAGER_ID, 
       B.FIRST_NAME || ' ' || B.LAST_NAME MANAGER_NAME 
FROM EMPLOYEES A, EMPLOYEES B 
WHERE A.MANAGER_ID = B.EMPLOYEE_ID 
ORDER BY A.EMPLOYEE_ID;

 

4) 집합 연산자

--- 4) 집합 연산자: 집합으로 연결하기 
-- UNION, UNION ALL, INTERSECT, MINUS 
-- 예제 6-4 
SELECT DEPARTMENT_ID FROM EMPLOYEES 
UNION 
SELECT DEPARTMENT_ID FROM DEPARTMENTS; 

-- 예제
SELECT DEPARTMENT_ID FROM EMPLOYEES 
UNION ALL 
SELECT DEPARTMENT_ID FROM DEPARTMENTS 
ORDER BY DEPARTMENT_ID; 

-- UNION ALL에서 WHERE절을 사용하기 
SELECT DEPARTMENT_ID FROM EMPLOYEES 
WHERE department_id > 100 
UNION ALL 
SELECT DEPARTMENT_ID FROM DEPARTMENTS 
WHERE department_id > 200 
ORDER BY 1; 

-- 예제
SELECT DEPARTMENT_ID FROM EMPLOYEES 
INTERSECT 
SELECT DEPARTMENT_ID FROM DEPARTMENTS 
ORDER BY DEPARTMENT_ID; 

-- 예제
SELECT DEPARTMENT_ID FROM DEPARTMENTS 
MINUS 
SELECT DEPARTMENT_ID FROM EMPLOYEES; 

(SELECT SALARY, LAST_NAME FROM EMPLOYEES 
UNION 
SELECT MANAGER_ID,DEPARTMENT_NAME FROM DEPARTMENTS) 
MINUS 
SELECT MIN_SALARY, JOB_ID FROM JOBS; 

-- 예제 
SELECT EMPLOYEE_ID FROM EMPLOYEES 
MINUS 
SELECT EMPLOYEE_ID FROM JOB_HISTORY;