EXCEPT is a set operator that returns rows from the first SELECT statement that do NOT appear in the second SELECT statement.

Key points:

  • Think of it as A − B (set difference)
  • Automatically removes duplicates

Both queries must return:

  • Same number of columns
  • Compatible data types
  • Same column order

Step 1: Basic Syntax

SELECT column_list
FROM table1EXCEPTSELECT column_list
FROM table2;

Step 2: Example tables

Employees_2024

EmployeeID Name Department
1 John IT
2 Jane HR
3 Mike IT
4 Sara Finance

Employees_2025

EmployeeID Name Department
2 Jane HR
3 Mike IT

Step 3: Simple EXCEPT example

Find employees who were in 2024 but NOT in 2025:

SELECT EmployeeID, Name, Department
FROM Employees_2024EXCEPT

SELECT EmployeeID, Name, Department
FROM Employees_2025;

Result:

EmployeeID Name Department
1 John IT
4 Sara Finance

Jane and Mike are excluded because they exist in both tables.

Step 4: EXCEPT removes duplicates

Even if a row appears multiple times in the first query, EXCEPT returns it only once.

SELECT Department
FROM Employees_2024EXCEPT

SELECT Department FROM Employees_2025;

Step 5: EXCEPT with WHERE clause

Each SELECT can have its own filter:

SELECT Name
FROM Employees_2024
WHERE Department = 'IT'EXCEPT

SELECT Name
FROM Employees_2025
WHERE Department = 'IT';

Result:

Name
John

Step 6: EXCEPT with ORDER BY

ORDER BY must be placed at the end:

SELECT EmployeeID, Name
FROM Employees_2024EXCEPT

SELECT EmployeeID, Name FROM Employees_2025 ORDER BY Name ASC;

Step 7: EXCEPT vs NOT IN vs LEFT JOIN

EXCEPT

SELECT EmployeeID FROM TableA
EXCEPT
SELECT EmployeeID FROM TableB;

NOT IN

SELECT EmployeeID
FROM TableA
WHERE EmployeeID NOT IN (SELECT EmployeeID FROM TableB);

⚠ NOT IN can behave unexpectedly with NULL values.

LEFT JOIN

SELECT a.EmployeeID
FROM TableA a
LEFT JOIN TableB b ON a.EmployeeID = b.EmployeeID
WHERE b.EmployeeID IS NULL;

Categorized in:

SQL Server,