EXCEPT equivalent in MySQL

Introduction

We use EXCEPT to find rows in one list that are not in another. MySQL does not have EXCEPT, so we use other ways to get the same result.

You want to find customers who bought product A but not product B.

You want to list employees who attended training last year but not this year.

You want to find emails in one list that are missing from another list.

You want to compare two sets of data and find what is unique to the first set.

Syntax

MySQL

SELECT columns FROM table1
WHERE NOT EXISTS (
  SELECT 1 FROM table2 WHERE table2.column = table1.column
);

This uses a subquery to check if a row exists in the second table.

You can also use LEFT JOIN with IS NULL to get the same result.

Examples

This finds customers who have no orders.

MySQL

SELECT id FROM customers
WHERE NOT EXISTS (
  SELECT 1 FROM orders WHERE orders.customer_id = customers.id
);

This also finds customers who have no orders using LEFT JOIN.

MySQL

SELECT id FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

Sample Program

This finds ids in products_a that are not in products_b, simulating EXCEPT.

MySQL

CREATE TABLE products_a (id INT);
CREATE TABLE products_b (id INT);

INSERT INTO products_a VALUES (1), (2), (3);
INSERT INTO products_b VALUES (2), (4);

SELECT id FROM products_a
WHERE NOT EXISTS (
  SELECT 1 FROM products_b WHERE products_b.id = products_a.id
);

Important Notes

MySQL does not support EXCEPT directly, so use NOT EXISTS or LEFT JOIN with IS NULL.

Make sure the columns you compare have the same data type.

Summary

EXCEPT finds rows in one set but not in another.

MySQL uses NOT EXISTS or LEFT JOIN ... IS NULL to do the same.

This helps compare lists and find unique items.