How do SQL UNION and INTERSECT differ?

SQL UNION and INTERSECT are set operations used to combine or compare results from two SELECT statements. UNION merges the results of two queries into a single dataset, removing duplicate rows by default. INTERSECT, on the other hand, returns only the rows that exist in both query results. While both operations require the SELECT statements to have the same number of columns with compatible data types, their purposes are distinct: UNION aggregates data, whereas INTERSECT filters for overlaps.

To illustrate, consider two tables, employees_sales and employees_marketing. Using UNION, you could combine names from both tables:

SELECT name FROM employees_sales
UNION
SELECT name FROM employees_marketing;

This returns all unique names from both departments. If “Alice” appears in both tables, she’ll be listed once. To include duplicates, use UNION ALL. In contrast, INTERSECT identifies common entries:

SELECT name FROM employees_sales
INTERSECT
SELECT name FROM employees_marketing;

Here, only names present in both tables (like “Alice”) are returned. Unlike UNION, INTERSECT focuses on shared data rather than combining datasets.

Key considerations include handling duplicates and column compatibility. UNION prioritizes breadth, merging data for reports or aggregated views. INTERSECT is narrower, useful for validation (e.g., finding overlapping customers). Not all SQL dialects support INTERSECT (e.g., MySQL lacks it), so alternatives like JOINs or EXISTS may be needed. Both operations require aligned columns but serve opposite goals: UNION expands results, while INTERSECT restricts them to commonalities. Understanding these distinctions helps choose the right tool for data analysis or integration tasks.