Set Operations: INTERSECT and EXCEPT

While JOIN combines columns horizontally, set operators like INTERSECT and EXCEPT compare entire rows across two result sets. They are the SQL equivalent of set intersection and set difference from mathematics.

INTERSECT

INTERSECT returns only the rows that are present in both query results. Duplicates are automatically removed.

SELECT product_id FROM current_inventory
INTERSECT
SELECT product_id from incoming_shipment;

Use cases:

• Find customers who bought in both Q1 and Q2.
• Find employees who are also managers.
• Identify common records between two data sources during migration.

EXCEPT (MINUS in Oracle)

EXCEPT returns rows from the first query that are not present in the second query. Duplicates are removed.

SELECT product_id FROM current_catalog
EXCEPT
SELECT product_id FROM discontinued_products;

Use cases:

• Find products that were never sold.
• Identify users who haven’t logged in.
• Detect missing records in one system compared to another.

INTERSECT vs INNER JOIN

These can sometimes produce the same result, but they work differently:

• INTERSECT compares entire rows (all columns must match).
• INNER JOIN matches on a join condition (typically one or two columns) and combines columns from both sides.

-- INTERSECT: row must be identical in both tables
SELECT * FROM TableA INTERSECT SELECT * FROM TableB;

-- INNER JOIN: only one column needs to match
SELECT a.* FROM TableA a INNER JOIN TableB b ON a.id = b.id;

EXCEPT vs NOT IN / NOT EXISTS

EXCEPT is often a clearer alternative to NOT IN or NOT EXISTS:

-- Using NOT IN
SELECT id FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

-- Using EXCEPT (cleaner for simple cases)
SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;

Caution: NOT IN behaves unexpectedly if the subquery contains NULLs — it returns zero rows. EXCEPT handles NULLs correctly.

INTERSECT and EXCEPT with ORDER BY

Place ORDER BY at the very end of the combined query:

SELECT product_id FROM warehouse_a
INTERSECT
SELECT product_id FROM warehouse_b
ORDER BY product_id;

Rules for Set Operators

1. Both queries must have the same number of columns.
2. Corresponding columns must have compatible data types.
3. The column names in the result come from the first query.
4. ORDER BY can only appear at the end of the entire set operation.

SELECT product_id FROM warehouse_a
INTERSECT
SELECT product_id FROM warehouse_b;

SELECT customer_id FROM q1_sales
INTERSECT
SELECT customer_id FROM q2_sales;

SELECT product_id FROM products
EXCEPT
SELECT product_id FROM sales;

SELECT user_id FROM users
EXCEPT
SELECT user_id FROM login_history;

-- Rows missing from the target
SELECT * FROM source_table
EXCEPT
SELECT * FROM target_table;

(SELECT id, name, balance FROM system_a
 EXCEPT
 SELECT id, name, balance FROM system_b)
UNION ALL
(SELECT id, name, balance FROM system_b
 EXCEPT
 SELECT id, name, balance FROM system_a);