SQL UNION and INTERSECT are both used to combine the results of two or more SQL queries, but they serve different purposes and yield different results. UNION combines the results of multiple SELECT statements into a single result set, including all unique rows from both queries. In contrast, INTERSECT returns only the rows that are found in both queries, providing a way to extract the common data. This fundamental differentiation in functionality can significantly impact the kind of data you retrieve during database operations.
To illustrate, consider two tables, employees_2022 and employees_2023. Let’s say employees_2022 has records for employees active in 2022, while employees_2023 has records for employees active in 2023. If you want to see all unique employees from both years, you would use a UNION statement like this:
SELECT employee_id FROM employees_2022
UNION
SELECT employee_id FROM employees_2023;
This query would return a list of all employee IDs, consolidating those from both years while eliminating duplicates. On the other hand, if you're interested in finding out which employees were active in both years, you would use INTERSECT:
SELECT employee_id FROM employees_2022
INTERSECT
SELECT employee_id FROM employees_2023;
This query would return only the employee IDs that appear in both tables, thereby providing insights into continuity or changes in workforce between the two years.
It’s also worth noting that when using UNION, SQL automatically removes duplicates from the final result set, while with INTERSECT, the result set itself naturally consists of only those records that are common across the involved queries. Additionally, both UNION and INTERSECT require that the queries being combined have the same number of columns, and those columns must be of compatible data types. Understanding these distinctions will help developers effectively query databases to meet their specific data retrieval needs.