SQL 조인 실습 코드

참고도서: 모두의 SQL(길벗) 6장 http://www.yes24.com/Product/Goods/64434562?OzSrank=1

모두의 SQL

 

 

출처: HR 스키마 (Oracle Database 11g 교안)

---- 모두의 SQL 6장: 조인과 집합으로 여러 테이블 연결하기 ----
--- 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');

-- 예제 6-1
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) 외부 조인(Outer Join)
-- 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) 자체 조인(Self Join)
-- 예제 6-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) 집합 연산자: 집합으로 연결하기
-- UNION, UNION ALL, INTERSECT, MINUS
-- 예제 6-4
SELECT DEPARTMENT_ID FROM EMPLOYEES
UNION
SELECT DEPARTMENT_ID FROM DEPARTMENTS;

-- 예제 6-5
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;

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

-- 예제 6-7
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;