집합 (UNION, INTERSECT, MINUS)

/*
집합
    합집합: UNION - FULLOUTER
    교집합: INTERSECT - INNER
    차집합: MINUS
*/
 
--UNION
SELECT job_id
FROM employees
WHERE job_id IN('AD_VP', 'FI_ACCOUNT')
UNION ALL
SELECT job_id
FROM jobs
WHERE job_id IN('AD_VP', 'FI_ACCOUNT');
 
--INTERSECT
--매니저에 대한 정보만 추린다(교집합으로)
--sorting  이 자동적으로 된다
-- 중복이 없다
SELECT employee_id
FROM employees
INTERSECT 
SELECT manager_id
FROM employees;
--inner join 으로 해본것, 위와동일
SELECT DISTINCT b.employee_id
FROM employees a, employees b
WHERE a.manager_id = b.manager_id ;
 
--MINUS
 
SELECT employee_id
FROM employees
MINUS
SELECT manager_id
FROM employees;
 
-- join
SELECT *
FROM employees a, departments b
WHERE a.department_id = b.department_id(+) 
    and a.manager_id IS NOT NULL;