SQL EXCEPT: Find the Difference Between Two Tables

#sql

Need to compare two sets of data in SQL and identify what’s missing or different? The EXCEPT operator is your friend. It returns rows from the first query that don’t exist in the second.

Below we’ll explore its syntax, use cases, and practical examples to show how EXCEPT helps you maintain cleaner data.

What Is SQL EXCEPT?

EXCEPT is a set operation that compares the results of two SELECT statements and returns the rows from the first that are not in the second.

Use Cases:

Spot unsold inventory items
Identify active users not marked as inactive
Clean up duplicate entries
Validate imports or data syncs

Syntax Overview

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

Make sure:

Both queries return the same columns
Data types match across columns

Practical Examples

Employees Not Formerly Terminated

SELECT id, name FROM employees
EXCEPT
SELECT id, name FROM former_employees;

Unique Orders

SELECT order_id, customer_id FROM orders
EXCEPT
SELECT order_id, customer_id FROM (
  SELECT order_id, customer_id
  FROM orders
  GROUP BY order_id, customer_id
  HAVING COUNT(*) > 1
) AS duplicates;

Unsold Products

SELECT item_id FROM inventory
EXCEPT
SELECT item_id FROM sales;

NULL-Safe Comparison

SELECT user_id FROM logs WHERE status IS NOT NULL
EXCEPT
SELECT user_id FROM logs_archive WHERE status IS NOT NULL;

FAQ

What is SQL EXCEPT?

It compares two datasets and returns unique rows from the first that aren’t in the second.

Can it compare across multiple columns?

Yes. As long as both queries return the same number of columns and types.

Do all SQL databases support EXCEPT?

No. SQL Server, PostgreSQL, and SQLite support it. MySQL does not.

How are NULL values treated?

NULLs are treated as distinct. Filter them explicitly if needed.

Conclusion

The EXCEPT operator is a practical and readable way to isolate differences between datasets. Whether you're debugging, auditing, or cleaning up messy data, it saves time and simplifies your queries.

To explore EXCEPT visually and test edge cases, use a tool like DbVisualizer, which supports multiple DBMSs and query debugging.

Check out SQL EXCEPT: Everything You Need to Know article for more insights.