Course contentsShow
← Data Science
Lesson 1002 of 2,14526. Advanced SQLFree lesson

EXCEPT: Finding Differences

Learn to identify rows present in one query but not another using EXCEPT (or MINUS in some databases).

EXCEPT: Finding Differences

What you'll learn: How to find rows that exist in one query result but not in another using the EXCEPT set operation.

What Is EXCEPT?

EXCEPT is a set operation that returns rows from the first query that do not appear in the second query. Think of it like a subtraction operation for tables: "Give me everything from Table A, minus anything that also appears in Table B."

Some databases (notably Oracle) use MINUS instead of EXCEPT, but the concept is identical.

Basic Syntax

SELECT column1, column2
FROM table1
EXCEPT
SELECT column1, column2
FROM table2;

The result contains only rows from the first query that have no match in the second query. Duplicate rows are automatically removed (like UNION, not UNION ALL).

When to Use EXCEPT

Common scenarios include:

  • Data auditing: Find customers who placed orders in 2023 but not in 2024
  • Finding gaps: Identify products in inventory but never sold
  • Comparing datasets: Detect records in a source system missing from a target

Example Pattern

-- Find employees who have never been assigned to a project
SELECT employee_id, employee_name
FROM employees
EXCEPT
SELECT employee_id, employee_name
FROM project_assignments;

This returns only employees with no matching row in project_assignments.

Important Notes

  • Column count and data types must match between both queries
  • Column names come from the first query
  • Order doesn't matter in set operations (use ORDER BY after the entire operation)
  • EXCEPT is not commutative: A EXCEPT B ≠ B EXCEPT A

Key Takeaway: Use EXCEPT to find rows present in the first result set but absent from the second—essential for identifying gaps, differences, and missing data between two datasets.