UNION, INTERSECT, and EXCEPT

Introduction

SQL provides powerful set operations that allow combining results from multiple queries. Three key operations—UNION, INTERSECT, and EXCEPT—help in merging, filtering, and subtracting data efficiently. These operations simplify complex queries and improve data retrieval efficiency.

1. UNION

The UNION operator combines the results of two or more SELECT queries into a single result set, removing duplicate records by default.

Syntax:

SELECT column_names FROM table1
UNION
SELECT column_names FROM table2;

Example:

SELECT name FROM Customers
UNION
SELECT name FROM Suppliers;

This query retrieves unique names from both Customers and Suppliers tables.

UNION ALL

If duplicates should be included, use UNION ALL:

SELECT name FROM Customers
UNION ALL
SELECT name FROM Suppliers;

2. INTERSECT

The INTERSECT operator returns only the common records present in both queries.

Syntax:

SELECT column_names FROM table1
INTERSECT
SELECT column_names FROM table2;

Example:

SELECT name FROM Customers
INTERSECT
SELECT name FROM Suppliers;

This query fetches names that exist in both Customers and Suppliers.

3. EXCEPT

The EXCEPT operator returns the records from the first query that are not present in the second query.

Syntax:

SELECT column_names FROM table1
EXCEPT
SELECT column_names FROM table2;

Example:

SELECT name FROM Customers
EXCEPT
SELECT name FROM Suppliers;

This query retrieves names present in Customers but not in Suppliers.

Key Considerations:

• The number of columns and their data types must match in both queries.
• UNION removes duplicates unless UNION ALL is used.
• INTERSECT and EXCEPT may not be supported in all database systems.

Conclusion

SQL set operations—UNION, INTERSECT, and EXCEPT—offer efficient ways to manipulate and compare datasets across tables. Understanding these operations helps in building optimized queries for data analysis and reporting.