Comparing UNION, INTERSECT, and MINUS in Oracle SQL

Oracle SQL provides three set operators that combine the results of two queries:

1. UNION (and UNION ALL)
2. INTERSECT
3. MINUS (Oracle’s equivalent of the SQL standard EXCEPT)

All three operators work on complete result sets, not on individual rows in isolation. Instead of joining tables horizontally (like JOIN), set operators stack compatible result sets vertically and then apply set logic (union, intersection, or difference).

UNION and UNION ALL

UNION combines two result sets and returns distinct rows. UNION ALL combines the same result sets but keeps duplicates. Both require the two queries to return the same number of columns with compatible data types.

Basic syntax:

SELECT column1, column2, column3
FROM   table1
WHERE  condition1
UNION
SELECT column1, column2, column3
FROM   table2
WHERE  condition2;

Key rules:

• The number of columns must match in both SELECT statements.
• Corresponding columns must have compatible data types.
• UNION performs an implicit DISTINCT across all columns in the combined result.
• UNION ALL skips the distinct step and is often faster when duplicates are acceptable.

Example: Using UNION to combine two subsets

SELECT employee_id, first_name, last_name, department_id
FROM   employees
WHERE  department_id = 10
UNION
SELECT employee_id, first_name, last_name, department_id
FROM   employees
WHERE  department_id = 20;

This query returns the set of distinct employees who belong to department 10 or department 20. If an employee somehow appears in both groups, UNION ensures they appear only once in the final result.

Oracle Database SQL

Example: UNION ALL to retain duplicates

SELECT employee_id, first_name, last_name, department_id
FROM   employees
WHERE  department_id = 10
UNION ALL
SELECT employee_id, first_name, last_name, department_id
FROM   employees
WHERE  department_id = 20;

Here, UNION ALL returns all rows from both result sets, including duplicates. Use this when duplicates are meaningful (for example, counting events) and you do not want the overhead of duplicate removal.

Ordering combined results

To sort the combined result set, apply ORDER BY at the end of the entire expression:

SELECT employee_id, first_name, last_name, department_id
FROM   employees
WHERE  department_id = 10
UNION
SELECT employee_id, first_name, last_name, department_id
FROM   employees
WHERE  department_id = 20
ORDER  BY department_id, last_name;

The ORDER BY clause applies to the final set, not to the individual queries.

INTERSECT and MINUS at a Glance

Oracle also supports two other set operators with distinct semantics:

Command	Description
UNION	Combines two result sets and returns all distinct rows from both.
INTERSECT	Returns rows that appear in both result sets.
MINUS	Returns rows that appear in the first result set but not in the second.

All three operators remove duplicates by default (that is, they operate on sets). Only UNION has the special UNION ALL variant, which behaves like a multiset (bag) by preserving duplicates.

A helpful way to think about these operators is with Venn diagrams. Consider two result sets, A and B. The animation below shows which parts of A and B are returned by each operator:

• INTERSECT: the overlapping region where A and B both contain the same row.
• UNION: all regions in A or B (without duplicates).
• MINUS: the portion that belongs to A but not to B.

Set operators impose some important structural rules on the queries they combine:

1. Rule 1: Matching column count and compatible data types
   Both queries must produce the same number of columns in the SELECT list, and corresponding columns must have compatible data types.
   • If the first query returns (DATE, NUMBER, VARCHAR2), the second must also return three columns, in that same logical order.
   • The column names do not need to match; only the positions and data types matter.
2. Rule 2: The first query defines column names and lengths
   The column names and lengths in the final result come from the first query. If two corresponding character columns have different lengths, the length from the first query is used. If data from the second query is longer, Oracle may truncate values or raise an error.

Example: Using INTERSECT

SELECT TO_CHAR(LAST_UPDATE_DATE,     'YYYY-MM') AS update_month,
       UPDATED_BY_USER
FROM   PRODUCT
INTERSECT
SELECT TO_CHAR(LAST_UPDATE_DATETIME, 'YYYY-MM') AS update_month,
       CREATED_BY_USER
FROM   PET_CARE_LOG
ORDER  BY 1;

This query returns the (year–month, user) combinations that appear in both tables: months in which a given employee both updated a product record and created at least one log entry in PET_CARE_LOG.

The next section walks through typical business scenarios and shows which operator to choose.

Consider three common questions and the operator that best answers each one:

1. INTERSECT: “Which employees have updated at least one record in the PRODUCT table and added at least one record in the PET_CARE_LOG table?”
   Use INTERSECT to find the overlap between two sets of employees.
2. UNION: “Which customers have purchased a dog in one sale and a cat in another sale?”
   Use UNION (or UNION ALL when duplicates are meaningful) to merge customer lists from the dog and cat sales.
3. MINUS: “Which products purchased by Amy Black have not been purchased by Lester Lee?”
   Use MINUS to subtract Lester’s product set from Amy’s product set.

The same ideas apply to classic mailing list examples. Suppose you have two mailing lists, List A and List B:

• UNION: everyone in A or B (each person only once).
• INTERSECT: people who are in both A and B.
• MINUS: people in A who are not in B (or vice versa, depending on order).

Example: Comparing Books on Hand vs Books on Order

Imagine two tables:

• BOOKSHELF – books currently on the shelf.
• BOOK_ORDER – books that have been ordered but not yet received.

First query: titles from the first half of the alphabet currently on the shelf:

SELECT Title
FROM   BOOKSHELF
WHERE  Title < 'M%';

Second query: all titles currently on order:

SELECT Title
FROM   BOOK_ORDER;

To see the combined set of titles (on hand or on order), use UNION:

SELECT Title
FROM   BOOKSHELF
WHERE  Title < 'M%'
UNION
SELECT Title
FROM   BOOK_ORDER;

The first query returns a certain number of titles from BOOKSHELF, the second returns another set from BOOK_ORDER. The UNION result contains each distinct title that appears in either table. If the same title appears in both tables, it appears only once in the final list. If you needed to preserve duplicate rows for analysis (for example, counting how many times a title appears across both tables), you would use UNION ALL instead.

By choosing the appropriate set operator and applying these rules, you can express many business questions in a clear, declarative way.

In the next lesson, you will build on these ideas and explore more advanced set operator techniques.