SQL INTERSECT and EXCEPT

UNION combines two results. Its siblings compare them: INTERSECT returns the rows that appear in both results, and EXCEPT returns rows in the first result that are not in the second. MySQL added both in version 8.0.31, so MySQL 8.4 supports them; older versions need a join or EXISTS.

The two lists

Cities of employees and of customers:

INTERSECT: rows in both

Cities where we have both an employee and a customer:

SELECT city FROM employeesINTERSECTSELECT city FROM customersORDER BY city;

EXCEPT: rows in the first only

Cities with employees but no customers:

SELECT city FROM employeesEXCEPTSELECT city FROM customersORDER BY city;

The order matters. Cities with customers but no employees:

SELECT city FROM customersEXCEPTSELECT city FROM employeesORDER BY city;

Practical example: products never ordered

SELECT product_id FROM productsEXCEPTSELECT product_id FROM order_items;

Several columns

The comparison uses the whole row, so all selected columns must match. Customer ids that ordered in both January and April 2025:

SELECT customer_id FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'INTERSECTSELECT customer_id FROM orders WHERE order_date BETWEEN '2025-04-01' AND '2025-04-30';

Alternatives with EXISTS

On MySQL before 8.0.31 (and many interview answers), write INTERSECT with EXISTS and EXCEPT with NOT EXISTS:

SELECT DISTINCT e.cityFROM employees eWHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.city = e.city)ORDER BY e.city;

INTERSECT and EXCEPT FAQ