UNION & Set Operations
Combine results from multiple queries
Set operations combine result sets from multiple SELECT statements.
UNION
Combines results and removes duplicates:
-- Combine customers and suppliers into one list SELECT name, email, 'Customer' AS type FROM customers UNION SELECT name, email, 'Supplier' AS type FROM suppliers; -- Note: Columns must match in number and type
UNION ALL
Combines results and keeps all duplicates (faster):
-- All transactions (even duplicates) SELECT amount, 'Credit' AS type FROM credits UNION ALL SELECT amount, 'Debit' AS type FROM debits;
INTERSECT
Returns only rows that appear in both queries:
-- Customers who are also suppliers SELECT name FROM customers INTERSECT SELECT name FROM suppliers;
EXCEPT (MINUS)
Returns rows from first query that aren't in second:
-- Customers who are NOT suppliers SELECT name FROM customers EXCEPT SELECT name FROM suppliers; -- Note: Oracle uses MINUS instead of EXCEPT
Rules
- - All queries must have the same number of columns
- - Column data types must be compatible
- - Column names come from the first query
- - ORDER BY goes at the very end