INTERSECT / EXCEPT
Set operations on two result sets. INTERSECT returns rows in both sides; EXCEPT returns rows in the left side but not the right. Both deduplicate by default. Use INTERSECT ALL / EXCEPT ALL (Postgres, DuckDB) to preserve duplicate counts.
Syntax #
sql
SELECT col FROM table_1
INTERSECT
SELECT col FROM table_2;
SELECT col FROM table_1
EXCEPT
SELECT col FROM table_2;Example #
Loading SQL editor...
Warning
INTERSECT and EXCEPT compare full rows, not just a key. If the two SELECTs have different extra columns, the row signatures won't match and the result is usually empty or surprising. Keep both sides to the same minimal column list — or rewrite as IN (SELECT ...) / NOT EXISTS when you're really just testing membership on one key. For an anti-join on large tables, LEFT JOIN ... IS NULL typically outperforms EXCEPT.