Oracle SQL Set Operators: How to Use UNION ALL, MINUS, and More

Have you ever wanted to compile data scattered across multiple tables into a single list, or compare two datasets to extract only “common data” or “differences”?

Using Oracle SQL set operators (UNION ALL, UNION, INTERSECT, MINUS), you can combine the results of multiple SELECT statements vertically or extract data based on mathematical set theory.

This article explains the differences between these four operators, how to use them specifically, their impact on performance, and practical precautions for real-world scenarios, targeting Oracle Database 19c.

Conclusion/Summary (To-Do List)

Set operators allow you to handle multiple query results as a single result set. Use them according to your needs as follows:

Want to combine everything (Fastest): UNION ALL Connects results simply without removing duplicates. It is fast because no sorting occurs.
Want to combine without duplicates: UNION Removes duplicate rows after combining results (internal sort or hash operation occurs).
Want to know common data: INTERSECT Returns only rows that exist in both result sets (intersection).
Want to know the difference: MINUS Returns rows that exist in the first result set but not in the second (difference).

1. Basics and Rules of Set Operators

Before executing specific code, let’s cover the important rules common to all set operators. Failure to observe these will result in errors.

Same number of columns: The number of columns selected must be consistent across all combined SELECT statements.
Compatible data types: The 1st column must match the 1st column, the 2nd with the 2nd, and so on. Data types must be compatible (e.g., numeric with numeric).
ORDER BY at the end: If you want to sort, the ORDER BY clause is written only once, after the very last SELECT statement.
Handling of NULL: In set operators, NULLs are treated as “equal values” (behavior differs from standard comparison operators).

Creating Validation Data

We will create sample tables (table_a, table_b) to be used in all examples in this article. Note: The SQL does not include multibyte characters (such as Japanese) in the data itself, using only alphanumeric characters.

-- 1. Delete existing tables if they exist (ignore errors)
-- DROP TABLE table_a PURGE;
-- DROP TABLE table_b PURGE;

-- 2. Create Table A (Assuming employee data)
CREATE TABLE table_a (
    id    NUMBER PRIMARY KEY,
    name  VARCHAR2(50) NOT NULL,
    value NUMBER NOT NULL
);

-- 3. Create Table B (Assuming project member data)
CREATE TABLE table_b (
    id    NUMBER PRIMARY KEY,
    name  VARCHAR2(50) NOT NULL,
    value NUMBER NOT NULL
);

-- 4. Insert data into table_a
-- Alice: A only, Bob: Common (values match), Charlie: ID/Name match but values differ
INSERT INTO table_a (id, name, value) VALUES (1, 'Alice',   100);
INSERT INTO table_a (id, name, value) VALUES (2, 'Bob',     200);
INSERT INTO table_a (id, name, value) VALUES (3, 'Charlie', 300);

-- 5. Insert data into table_b
-- Bob: Common, Charlie: Value differs, David: B only
INSERT INTO table_b (id, name, value) VALUES (2, 'Bob',     200);
INSERT INTO table_b (id, name, value) VALUES (3, 'Charlie', 400);
INSERT INTO table_b (id, name, value) VALUES (4, 'David',   500);

-- 6. Commit
COMMIT;

SQL> SELECT * FROM table_a;

        ID NAME                      VALUE
---------- -------------------- ----------
         1 Alice                       100
         2 Bob                         200
         3 Charlie                     300

SQL> SELECT * FROM table_b;

        ID NAME                      VALUE
---------- -------------------- ----------
         2 Bob                         200
         3 Charlie                     400
         4 David                       500

2. UNION ALL: Combine All Results

UNION ALL concatenates and displays multiple query results as they are. It does not remove duplicates.

Characteristics

Performance: It is the fastest among the four operators because it does not perform a duplicate check.
Use Cases: Integrating log data, or simple combination of data where duplicates are impossible (or acceptable).

Execution Example

SELECT id, name, value FROM table_a
UNION ALL
SELECT id, name, value FROM table_b;

Execution Result and Explanation:

SQL> SELECT id, name, value FROM table_a
  2  UNION ALL
  3  SELECT id, name, value FROM table_b;

        ID NAME                      VALUE
---------- -------------------- ----------
         1 Alice                       100
         2 Bob                         200
         3 Charlie                     300
         2 Bob                         200
         3 Charlie                     400
         4 David                       500

6 rows selected.

ID | NAME    | VALUE
---|---------|------
 1 | Alice   | 100
 2 | Bob     | 200
 3 | Charlie | 300
 2 | Bob     | 200  <-- Bob appears as a duplicate
 3 | Charlie | 400
 4 | David   | 500

The 3 rows from Table A and the 3 rows from Table B, totaling 6 rows, are output exactly as they are.

3. UNION: Combine with Duplicates Excluded

UNION removes duplicate rows after combining the results.

Characteristics

Performance: It is more costly than UNION ALL because an internal sort (or hash operation) is performed to eliminate duplicates.
Note: Only when the “entire row” matches completely is it considered a duplicate. If even part of the columns differ, they remain as separate data.

Execution Example

SELECT id, name, value FROM table_a
UNION
SELECT id, name, value FROM table_b;

Execution Result and Explanation:

SQL> SELECT id, name, value FROM table_a
  2  UNION
  3  SELECT id, name, value FROM table_b;

        ID NAME                      VALUE
---------- -------------------- ----------
         1 Alice                       100
         2 Bob                         200
         3 Charlie                     300
         3 Charlie                     400
         4 David                       500

ID | NAME    | VALUE
---|---------|------
 1 | Alice   | 100
 2 | Bob     | 200  <-- Duplicate Bob merged into one row
 3 | Charlie | 300  <-- Remains because values differ
 3 | Charlie | 400  <-- Remains because values differ
 4 | David   | 500

Bob (ID: 2) became 1 row because all columns match. On the other hand, for Charlie (ID: 3), since the value column differs (300 and 400), they are not considered duplicates and both are displayed.

4. INTERSECT: Extract Common Parts (Intersection)

INTERSECT returns only the rows that exist in common in both query results.

Use Cases

Verifying data integrity between two different systems.
Extracting data that “meets Condition A AND meets Condition B” (similar to an AND condition).

Execution Example

SELECT id, name, value FROM table_a
INTERSECT
SELECT id, name, value FROM table_b;

Execution Result and Explanation:

SQL> SELECT id, name, value FROM table_a
  2  INTERSECT
  3  SELECT id, name, value FROM table_b;

        ID NAME                      VALUE
---------- -------------------- ----------
         2 Bob                         200

Only the completely matching row for Bob is output. Charlie is not considered “common data” because the values differ.

5. MINUS: Extract Differences (Difference)

MINUS subtracts the rows found in the second query result from the first query result and returns the remaining rows.

Use Cases

Identifying data “present in A but not in B” (e.g., products registered in the master but having no sales).
Checking differences before and after data migration.

Execution Example

SELECT id, name, value FROM table_a
MINUS
SELECT id, name, value FROM table_b;

Execution Result and Explanation:

SQL> SELECT id, name, value FROM table_a
  2  MINUS
  3  SELECT id, name, value FROM table_b;

        ID NAME                      VALUE
---------- -------------------- ----------
         1 Alice                       100
         3 Charlie                     300

We subtract the contents of Table B from Table A.

Alice: Remains because she is not in Table B.
Bob: Disappears because he is also in Table B.
Charlie: Since the entire row (Value) does not match despite having the same ID, he is considered distinct from the data in Table B, so the data from Table A remains.

Note: The order of description is important for MINUS. If you write table_b MINUS table_a, the result will be reversed (David and Charlie(400)).

6. Troubleshooting (Common Errors)

Here are common errors encountered when using set operators and how to deal with them.

Error Code: ORA-01790

Error Message: expression must have same datatype as corresponding expression

Cause: The n-th column of the first SELECT and the n-th column of the second SELECT do not match in type (e.g., number vs. string).
Solution: Match the types using TO_CHAR or TO_NUMBER functions, or use NULL placeholders (NULL AS col_name) to align the types.

Error Code: ORA-01789

Error Message: query block has incorrect number of result columns

Cause: The number of columns differs between the combined SELECT statements.
Solution: Match the number of columns. Insert NULL or fixed values into the missing columns to adjust the count.

7. Operational and Implementation Notes

Pros and Cons

Pros: Queries that would become difficult to read with complex conditions (combinations of OR and AND in the WHERE clause) can be written simply by splitting them.
Cons: UNION, INTERSECT, and MINUS often perform full sorts or hash joins internally, so care must be taken regarding performance when handling large amounts of data. Use UNION ALL preferentially if possible.

Performance Improvement Tips

If you know that duplicate data does not exist, or if duplicates are acceptable, always use UNION ALL. This allows skipping unnecessary duplicate elimination processes (sorting), which can dramatically improve response speed.

8. FAQ: Frequently Asked Questions

Q: Where should I write the ORDER BY clause? A: Write it after the very last SELECT statement. Writing it in an intermediate SELECT statement will cause an error. For column specification, use the column names or column numbers from the first SELECT statement.

Q: How are duplicate rows handled in INTERSECT and MINUS? A: Basically, duplicates are eliminated, and unique rows are returned (similar to when DISTINCT is applied).

Q: Can I combine 3 or more tables? A: Yes, it is possible. You can write them consecutively like SELECT ... UNION ALL SELECT ... UNION ALL SELECT .... They are evaluated in order from the top.

Summary

Mastering Oracle SQL set operators makes data integration and comparison very easy.

UNION ALL: Combine including duplicates (Fastest / Basic choice).
UNION: Combine excluding duplicates (High cost).
INTERSECT: Extract only common rows.
MINUS: Extract differences (A – B).
Rules: Match column counts and types. Sort at the end.

Start by connecting data with UNION ALL, and try checking differences with MINUS depending on your requirements.

This article explains the content targeting Oracle Database 19c (screens and default values may differ in other versions).

[reference]
Oracle Database SQL Language Reference, 19c