Set Operations

tags: #sql

What are set operations in SQL?

Set operations are used in SQL to combine or compare the results of two or more SELECT statements.

SELECT column1, column2, ...
FROM table1
<set operation>
SELECT column1, column2, ...
FROM table2

Types of set operations

UNION: Combines the result sets of two or more SELECT statements into a single result set, eliminating any duplicate rows.

Sample Scenario

Suppose you have two tables employees and contractors, both with columns name and salary, and you want to combine the information from both tables into a single result set.

SELECT name, salary FROM employees WHERE <condition>...
UNION
SELECT name, salary FROM contractors WHERE <condition>...;

UNION ALL: Similar to UNION, but it does not eliminate duplicate rows from the result set.
INTERSECT: Returns only the rows that appear in both result sets of two or more SELECT statements.
EXCEPT: Returns only the rows that appear in the first result set, but not in the second result set of two SELECT statements.