SET OPERATIONSQuery Techniques
Buy Me A Coffee

Combine rows from multiple queries: UNION, INTERSECT, EXCEPT/MINUS.

Combine results from multiple queries: UNION stacks and deduplicates, INTERSECT keeps only common rows, EXCEPT keeps rows from the first query not in the second. Each has ALL variants that skip deduplication. Essential for set logic in data analysis.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all No INTERSECT/EXCEPT (emulate)
PostgreSQL ✓ Supported all Full SET support
SQL Server ✓ Supported 2005 EXCEPT supported
Oracle ✓ Supported all MINUS instead of EXCEPT
SQLite ✓ Supported all INTERSECT/EXCEPT since 3.15

Details

Combine result-sets by set logic.

Standard Syntax

SELECT … UNION ALL SELECT …;

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since 2005 Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

No INTERSECT/EXCEPT (emulate)

SELECT col FROM A UNION ALL SELECT col FROM B;

PostgreSQL

Full SET support

SELECT … INTERSECT SELECT … EXCEPT SELECT …;

SQL Server

EXCEPT supported

SELECT … INTERSECT SELECT … EXCEPT SELECT …;

Oracle

MINUS instead of EXCEPT

SELECT … INTERSECT SELECT … MINUS SELECT …;

SQLite

INTERSECT/EXCEPT since 3.15

SELECT … INTERSECT SELECT … EXCEPT SELECT …;