집합

합집합 : UNION

교집합 : INTERSECT
차집합 : MINUS

 

 

 

합집합 : UNION

-- UNION
SELECT job_id
FROM employees
WHERE job_id IN('AD_VP','FI_ACCOUNT')
 
UNION   --합집합

SELECT job_id
FROM jobs
WHERE job_id IN('AD_VP', 'FI_ACCOUNT');

 

 

교집합 : INTERSECT 

--INTERSECT 
SELECT employee_id
FROM employees

INTERSECT   --교집합   -->매니저ID만 나옴

SELECT manager_id
FROM employees;

              --교집합은 JOIN과 동일

--JOIN
SELECT DISTINCT b.employee_id   --> DISTINCT로 중복제거
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;

 

 

 

차집합 : MINUS

SELECT employee_id
FROM employees

MINUS   -- 차집합 --> 매니저가 아닌사람들만 나옴: LEFT JOIN

SELECT manager_id
FROM employees;

            --차집합을 JOIN으로

SELECT e.first_name, e.department_id, d.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
    AND e.department_id IS NULL;