SET OPERATIONS

Query 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

Filter by Database
SQL SET OPERATIONS Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native all No INTERSECT/EXCEPT (emulate)
PostgreSQL Native all Full SET support
SQL Server Native 2005 EXCEPT supported
Oracle Native all MINUS instead of EXCEPT
SQLite Native all INTERSECT/EXCEPT since 3.15

Details

Combine result-sets by set logic.

Standard Syntax

SELECT … UNION ALL SELECT …;

Version Support

MySQL: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native since 2005 Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

No INTERSECT/EXCEPT (emulate)

SELECT col FROM A UNION ALL SELECT col FROM B;

PostgreSQL Native syntax

Full SET support

SELECT … INTERSECT SELECT … EXCEPT SELECT …;

SQL Server Native syntax

EXCEPT supported

SELECT … INTERSECT SELECT … EXCEPT SELECT …;

Oracle Native syntax

MINUS instead of EXCEPT

SELECT … INTERSECT SELECT … MINUS SELECT …;

SQLite Native syntax

INTERSECT/EXCEPT since 3.15

SELECT … INTERSECT SELECT … EXCEPT SELECT …;