Explain UNION, UNION ALL, INTERSECT and MINUS in Oracle SQL

How it works

UNION combines two result sets and removes duplicates — an implicit sort/dedup that costs more than UNION ALL, so use UNION ALL whenever duplicates genuinely can't occur or don't matter. INTERSECT returns only rows present in both result sets. MINUS returns rows from the first result set that don't appear in the second (Oracle's name for what ANSI SQL calls EXCEPT). All three require the same number of columns, with compatible datatypes in the same position, across both queries.

SELECT employee_id FROM employees WHERE department_id = 90
UNION
SELECT employee_id FROM employees WHERE salary > 15000;

SELECT employee_id FROM employees WHERE department_id = 90
INTERSECT
SELECT employee_id FROM employees WHERE salary > 15000;

SELECT employee_id FROM employees WHERE department_id = 90
MINUS
SELECT employee_id FROM employees WHERE salary > 15000;

Related Questions