Set operations

Set operations combine compatible query outputs vertically, rather than matching columns horizontally with a join.

UNION and UNION ALL

UNION removes duplicate complete rows. UNION ALL keeps them. Use ALL when you need every record or when inputs are already known to be disjoint; deduplication has a cost and a meaning.

INTERSECT

INTERSECT returns rows present in both results. It can answer which customer IDs appear in two groups of orders. The default operation is distinct in SQLite.

EXCEPT

EXCEPT returns rows from the first result that do not appear in the second. Reversing the operands changes the question. SQL Server and PostgreSQL also support EXCEPT, with their own supported variants.

Compatible outputs

Each query must return the same number of columns with compatible meanings and types. Column names come from the first output. Apply the final ORDER BY to the combined result.