Set Operators

SQL set operators are used to combine the results of two or more `SELECT` statements into a single result set. The queries being combined must have the same number of columns, and the columns must have compatible data types.

Key Rules:

• The number and order of columns must be the same in all queries.
• The data types must be compatible.
• The column names in the final result set are taken from the first query.

`UNION`: Combines result sets and removes duplicate rows.

-- Get a unique list of all department names from both tables
SELECT department FROM employees
UNION
SELECT name FROM departments;

`UNION ALL`: Combines result sets but includes all duplicate rows. It's faster than `UNION` because it doesn't check for duplicates.

-- Get a list of all employee and department names
SELECT name FROM employees
UNION ALL
SELECT name FROM departments;

The `INTERSECT` operator returns only the rows that appear in both result sets.

-- Find names that are both an employee and a department name
SELECT name FROM employees
INTERSECT
SELECT name FROM departments;

The `EXCEPT` operator returns distinct rows from the first (`LEFT`) query that are not present in the second (`RIGHT`) query's result set.

-- Find departments that have no employees
SELECT name FROM departments
EXCEPT
SELECT department FROM employees;