SQL - Set Operations in SQL

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