Oracle SQL SET Operators: UNION, UNION ALL, INTERSECT and MINUS

Oracle SQL SET Operators: Complete Guide

SET operators combine the results of two or more SELECT statements into a single result set. They are powerful tools for comparing and merging data from different queries without needing complex JOINs.

Learning Objectives
By the end of this guide you will understand the four SET operators, their differences, the rules that apply to all of them, and how to use ORDER BY, column names, and precedence correctly.

1. The Four SET Operators

Operator	What it returns	Duplicates
UNION	All rows from both queries	Removed
UNION ALL	All rows from both queries	Kept
INTERSECT	Rows that appear in both queries	Removed
MINUS	Rows in the first query that are not in the second	Removed

2. Rules That Apply to All SET Operators

• Both SELECT statements must return the **same number of columns**
• Corresponding columns must have **compatible data types**
• Column names in the final result come from the **first SELECT statement** only
• ORDER BY can appear only once — at the very end of the entire statement
• Individual SELECT statements cannot have their own ORDER BY clause

Important: The second query’s column names and aliases are ignored. Only the first query determines the final column names.

3. UNION — Combined Rows, Duplicates Removed

SELECT department_id FROM employees
UNION
SELECT department_id FROM departments;

UNION returns all unique rows from both queries. Duplicates are automatically removed.

4. UNION ALL — Combined Rows, Duplicates Kept

SELECT department_id FROM employees
UNION ALL
SELECT department_id FROM departments;

UNION ALL is faster because it skips duplicate removal. It returns every row from both queries, including duplicates.

5. INTERSECT — Rows Common to Both Queries

SELECT job_id FROM employees WHERE department_id = 10
INTERSECT
SELECT job_id FROM employees WHERE department_id = 20;

INTERSECT returns only rows that appear in both result sets.

6. MINUS — Rows in First Query Not in Second

SELECT department_id FROM departments
MINUS
SELECT department_id FROM employees;

MINUS returns rows from the first query that do not appear in the second query. Order matters — swapping the queries gives a different result.

7. ORDER BY with SET Operators

SELECT last_name, department_id FROM employees
UNION
SELECT last_name, department_id FROM retired_employees
ORDER BY department_id, last_name;

The ORDER BY clause must appear at the very end of the entire statement.

8. Interactive Practice Quiz

Q1. Query A returns 50 rows. Query B returns 30 rows. 10 rows appear in both results. How many rows does UNION ALL return?

A. 70 rows B. 80 rows C. 60 rows D. 40 rows

Q2. Which statement is true about column names in a SET operation?

A. Column names come from the second SELECT statement B. Column names come from the first SELECT statement C. Oracle uses the most descriptive column name from either query D. You must use the same column aliases in both queries

Q3. Where must the ORDER BY clause appear when using SET operators?

A. After each individual SELECT statement B. At the very end of the entire statement C. It can appear anywhere D. Before the first SELECT statement

Conclusion

SET operators are a clean and efficient way to combine or compare the results of multiple queries. Understanding the differences between UNION and UNION ALL, the rules for column compatibility, and the correct placement of ORDER BY will help you write more powerful and readable SQL.

Practice combining queries with each operator — especially noticing how UNION removes duplicates while UNION ALL preserves them, and how INTERSECT and MINUS answer different comparison questions.