Set operations in SQL are used to combine the results of two or more SELECT queries. These operations are based on the mathematical set theory and are useful for handling multiple queries and datasets in a single result.
UNION:
Combines the results of two or more SELECT queries into a single result set. It eliminates duplicate rows. Use Case: When you need to create a list from different tables where each item should appear only once.
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
INTERSECT:
Returns only the rows that are common to both SELECT statements. Use Case: Useful for finding a common subset of data from multiple datasets.
SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2;
EXCEPT (or MINUS in some databases):
Returns rows from the first SELECT query that are not found in the second SELECT query. Use Case: When you need to find records that are in one dataset and not in another.
SELECT column_name(s) FROM table1
EXCEPT
SELECT column_name(s) FROM table2;