SET Operators

Learn about UNION, UNION ALL, INTERSECT, and MINUS operators for combining query results. SET operators are essential for data comparison and analysis.

What are SET Operators?

SET operators are used to combine the results of two or more SELECT statements. The columns and data types in the SELECT statements must match.

Types of SET Operators

UNION: Combines results and removes duplicates.
UNION ALL: Combines all results including duplicates.
INTERSECT: Returns only common rows.
MINUS: Returns rows from the first query not present in the second.

Example: UNION

SELECT department_id FROM employees
UNION
SELECT department_id FROM departments;

Example: UNION ALL

SELECT department_id FROM employees
UNION ALL
SELECT department_id FROM departments;

Example: INTERSECT

SELECT department_id FROM employees
INTERSECT
SELECT department_id FROM departments;

Example: MINUS

SELECT department_id FROM departments
MINUS
SELECT department_id FROM employees;

Rules for Using SET Operators

Each SELECT must have the same number of columns.
Corresponding columns must be of compatible data types.
ORDER BY can be used only once, at the end of the final SELECT.

Practice Task

Use UNION to combine employee and manager IDs.
Find departments that have employees using INTERSECT.
List department IDs with no employees using MINUS.

Previous Lesson Next Lesson