Set operations are used to combine results from two or more SELECT queries. All queries must return the same number of columns with compatible data types.
1.UNION
- Combines results of two queries
- Removes duplicates by default
Syntax:
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
Example:
SELECT name FROM employees
UNION
SELECT name FROM managers;
Result:
List of unique names from both tables
2.UNION ALL
- Combines results including duplicates
Syntax:
SELECT name FROM employees
UNION ALL
SELECT name FROM managers;
Result:
All names from both tables, duplicates included
3.INTERSECT
- Returns only common rows from both queries
Syntax:
SELECT name FROM employees
INTERSECT
SELECT name FROM managers;
Result:
Names that appear in both tables
4.EXCEPT / MINUS
- Returns rows from the first query that do not exist in the second
Syntax (EXCEPT in MySQL 8+, MINUS in Oracle):
SELECT name FROM employees
EXCEPT
SELECT name FROM managers;
Result:
Names in employees but not in managers
Key Points
- Number of columns must match in all queries
- Column data types should be compatible
- Useful for merging, comparing, or filtering datasets
