UNION and UNION ALL

UNION

The UNION operator combines the result sets of two or more SELECT statements into a single result set. By default, UNION removes duplicate rows from the combined result.

-- Combine customer names from US and UK customers
SELECT customer_name, email, 'US' AS country
FROM customers_us
UNION
SELECT customer_name, email, 'UK' AS country
FROM customers_uk;

Rules for UNION:

1. All SELECT statements must have the same number of columns
2. Corresponding columns must have compatible data types
3. The ORDER BY clause can only appear at the end
4. Column names come from the first SELECT statement

-- Combine products from multiple sources
SELECT product_name, price, 'Electronics' AS category
FROM electronics_products
UNION
SELECT product_name, price, 'Clothing' AS category
FROM clothing_products
ORDER BY product_name;

UNION performs a DISTINCT operation internally to remove duplicates, which requires sorting and comparison. This makes it slower than UNION ALL. Use UNION only when you genuinely need to eliminate duplicates. For large datasets, UNION ALL is significantly faster.

UNION ALL

UNION ALL combines result sets like UNION but keeps all rows, including duplicates. It does not perform the deduplication step, making it much faster.

-- Combine all orders from 2024 and 2025 (duplicates are fine)
SELECT order_id, order_date, total_amount
FROM orders_2024
UNION ALL
SELECT order_id, order_date, total_amount
FROM orders_2025
ORDER BY order_date;

Since UNION ALL skips deduplication, it is the preferred choice when:

• You know there are no duplicates
• Duplicates are acceptable in the result
• Performance is critical
• You are combining data from different time periods or sources

-- Combine sales from all regions for a report
SELECT 'North' AS region, product_id, amount FROM sales_north
UNION ALL
SELECT 'South' AS region, product_id, amount FROM sales_south
UNION ALL
SELECT 'East' AS region, product_id, amount FROM sales_east
UNION ALL
SELECT 'West' AS region, product_id, amount FROM sales_west;

A common mistake is using UNION when UNION ALL would suffice. Always ask yourself: do I actually need deduplication? If the answer is no, use UNION ALL for better performance.

INTERSECT

INTERSECT returns only the rows that appear in both result sets. Like UNION, it removes duplicates by default.

-- Find customers who placed orders in both 2024 and 2025
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2024
INTERSECT
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2025;

INTERSECT is useful for finding common elements between two datasets. The column count and types must match, just like with UNION.

-- Find products that appear in both the electronics and bestseller lists
SELECT product_id, product_name FROM electronics_catalog
INTERSECT
SELECT product_id, product_name FROM bestsellers;

Not all databases support INTERSECT. In MySQL, you can simulate it using INNER JOIN or EXISTS:

-- MySQL equivalent of INTERSECT
SELECT DISTINCT a.customer_id
FROM orders a
INNER JOIN orders b ON a.customer_id = b.customer_id
WHERE YEAR(a.order_date) = 2024 AND YEAR(b.order_date) = 2025;

INTERSECT is conceptually similar to an INNER JOIN between two queries, but the syntax is cleaner for pure set operations.

EXCEPT / MINUS

EXCEPT (or MINUS in Oracle) returns rows from the first query that do not appear in the second query. It is the set difference operation.

-- Find customers who ordered in 2024 but NOT in 2025
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2024
EXCEPT
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2025;

EXCEPT removes duplicates by default. Use EXCEPT ALL to keep duplicates (supported in PostgreSQL and SQL Server).

-- Find products in the catalog that are out of stock
SELECT product_id, product_name FROM products
EXCEPT
SELECT p.product_id, p.product_name
FROM products p
JOIN inventory i ON p.product_id = i.product_id
WHERE i.quantity > 0;

The order of queries matters with EXCEPT. A EXCEPT B is different from B EXCEPT A:

-- Customers in 2024 but not 2025
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2024
EXCEPT
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2025;

-- Customers in 2025 but not 2024
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2025
EXCEPT
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2024;

EXCEPT is commonly used for data reconciliation, finding missing records, and comparing snapshots of data across different time periods.