SQL EXCEPT

What is SQL EXCEPT?

EXCEPT returns the distinct rows from the first SELECT statement that do not appear in the second SELECT statement.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL EXCEPT: Supports: PostgreSQL, SQL Server, SQLite, Snowflake, BigQuery, Redshift. Not supported: MySQL, older MariaDB, Oracle (use MINUS).

SQL EXCEPT Full Explanation

EXCEPT is a set operator defined in the SQL standard. It compares two result sets returned by independent SELECT statements and outputs only the unique rows that exist in the first set but not in the second. Duplicate rows are eliminated from both inputs before comparison, so the output is always distinct. Column counts and data types must match positionally across the two SELECT statements. Ordering is not preserved unless an outer ORDER BY clause is supplied. Some systems offer EXCEPT ALL to retain duplicates, but plain EXCEPT behaves like EXCEPT DISTINCT. When combining multiple set operators, parentheses clarify precedence. Performance depends on the underlying optimizer, but indexes and compatible data types improve speed. In Oracle, the equivalent operator is MINUS. MySQL lacks native EXCEPT support, requiring workarounds with LEFT JOIN or NOT EXISTS.

SQL EXCEPT Syntax

SELECT column_list
FROM table_a
EXCEPT
SELECT column_list
FROM table_b;

SQL EXCEPT Parameters

Example Queries Using SQL EXCEPT

-- Find emails present in staging but not yet in production
SELECT email
FROM staging_users
EXCEPT
SELECT email
FROM prod_users;

-- Compare two tables for regression testing
SELECT *
FROM new_feature_results
EXCEPT
SELECT *
FROM baseline_results;

-- Use parentheses with other operators
(SELECT id FROM t1)
EXCEPT
(SELECT id FROM t2)
INTERSECT
(SELECT id FROM t3);

Expected Output Using SQL EXCEPT

Each query returns a result set containing only those rows that appear in the first SELECT but not in the second, with duplicates removed

Use Cases with SQL EXCEPT

Identify rows that need to be inserted, updated, or deleted during ETL or data migration
Regression-test outputs by comparing new and baseline result sets
Validate data synchronization between environments
Generate exception reports for auditing or QA

Common Mistakes with SQL EXCEPT

Mismatching column counts or incompatible data types between the SELECT statements
Expecting duplicates in the output when using plain EXCEPT instead of EXCEPT ALL
Forgetting that ORDER BY inside individual SELECTs is ignored; apply ORDER BY after the final set operator
Assuming MySQL supports EXCEPT natively

First Introduced In

SQL:1999

Frequently Asked Questions

What does SQL EXCEPT do?

EXCEPT returns the distinct rows that are present in the first SELECT result set but absent in the second.

Does EXCEPT keep duplicates?

No. Plain EXCEPT removes duplicates. Use EXCEPT ALL (where supported) if you need to keep them.

Can I order the results of an EXCEPT query?

Yes. Add an ORDER BY clause after the entire EXCEPT expression, not inside the individual SELECT statements.

What is the alternative to EXCEPT in Oracle or MySQL?

Oracle uses the MINUS operator. MySQL has no equivalent, so use LEFT JOIN or NOT EXISTS patterns instead.