EXCEPT (MINUS) for differences in SQL

Introduction

We use EXCEPT (or MINUS) to find items in one list that are not in another. It helps us see differences between two sets of data.

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

You need to see which employees are in one department but not in another.

You want to check which students registered for one course but skipped another.

You want to compare two lists of emails to find which emails are unique to the first list.

Syntax

SQL

SELECT column_list FROM table1
EXCEPT
SELECT column_list FROM table2;

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

In some databases like Oracle, MINUS is used instead of EXCEPT.

Examples

This finds employees who are not managers.

SQL

SELECT name FROM employees
EXCEPT
SELECT name FROM managers;

This shows products sold in 2023 but not in 2022 (Oracle syntax).

SQL

SELECT product_id FROM sales_2023
MINUS
SELECT product_id FROM sales_2022;

This lists emails still subscribed after some unsubscribed.

SQL

SELECT email FROM newsletter_subscribers
EXCEPT
SELECT email FROM unsubscribed;

Sample Program

This example finds fruits that appeared in 2023 but not in 2022.

SQL

CREATE TABLE fruits2023 (name VARCHAR(20));
CREATE TABLE fruits2022 (name VARCHAR(20));

INSERT INTO fruits2023 (name) VALUES ('apple'), ('banana'), ('cherry');
INSERT INTO fruits2022 (name) VALUES ('banana'), ('date');

SELECT name FROM fruits2023
EXCEPT
SELECT name FROM fruits2022;

Important Notes

Both queries must select the same number of columns with compatible data types.

EXCEPT removes duplicates from the result by default.

Order of queries matters: EXCEPT returns rows in the first query that are missing in the second.

Summary

EXCEPT (or MINUS) helps find differences between two sets of data.

It returns rows from the first query that do not appear in the second.

Useful for comparing lists and spotting unique items.