9.1 UNION and UNION ALL Operators

9.1 UNION and UNION ALL Operators

In relational database management, retrieving comprehensive business data often requires combining information across disparate tables, historical archives, or distinct operational subsets. While SQL joins combine data horizontally by attaching columns from multiple tables side-by-side based on related keys, SQL set operators combine data vertically by stacking rows from multiple independent SELECT statements into a single, unified result set.

Set operators in Oracle SQL are grounded in mathematical set theory. Mastering the operational mechanics, sorting behaviors, duplicate handling, and performance characteristics of UNION and UNION ALL is essential for writing efficient queries and achieving certification on the Oracle Database SQL Certified Associate (1Z0-071) examination.

---

Set Theory Foundations in SQL

In mathematical set theory, a set is an unordered collection of unique elements. Relational databases adapt these principles through compound queries. A compound query consists of two or more component SELECT queries (query blocks) joined by a set operator:

+-----------------------------------------------------------------------------------+
|                             COMPOUND QUERY ARCHITECTURE                           |
|                                                                                   |
|  SELECT col1, col2 FROM table_a       <-- First Component Query (Query Block 1)   |
|  [SET OPERATOR]                       <-- UNION, UNION ALL, INTERSECT, or MINUS   |
|  SELECT col1, col2 FROM table_b       <-- Second Component Query (Query Block 2)  |
+-----------------------------------------------------------------------------------+

          SET A                             SET B
     +-------------+                   +-------------+
     |  Row 101    |                   |  Row 102    |
     |  Row 102    |                   |  Row 104    |
     |  Row 103    |                   |  Row 105    |
     +-------------+                   +-------------+
            \                                 /
             \       UNION / UNION ALL       /
              +-----------------------------+
                            v
                  COMBINED RESULT SET

Unlike standard mathematical sets, SQL tables and query result sets can contain duplicate rows unless explicitly deduplicated. Oracle SQL provides two distinct union operators to control duplicate preservation and sorting behavior:

1. UNION: Merges result sets, eliminates duplicate rows, and automatically sorts the final output.
2. UNION ALL: Merges result sets, preserves all duplicate rows, and does NOT sort the final output.

---

The UNION Operator

The UNION operator returns all unique rows retrieved by either the first query or the second query (or both). It corresponds to the mathematical union operation ($A \cup B$) with strict duplicate elimination.

+-----------------------------------------------------------------------------------+
|                            UNION VENN DIAGRAM (A UNION B)                         |
|                                                                                   |
|                 /-------------\       /-------------\                             |
|                /   ########    \     /    ########   \                            |
|               /   ##########    \   /    ##########   \                           |
|              |   ############====###====############  |                          |
|              |   ############   #####   ############  |                          |
|              |   ############====###====############  |                          |
|               \   ##########    /   \    ##########   /                           |
|                \   ########    /     \    ########   /                            |
|                 \-------------/       \-------------/                             |
|                     Set A                 Set B                                   |
|                                                                                   |
|         [Shaded area represents all unique rows from Set A and Set B]            |
+-----------------------------------------------------------------------------------+

Operational Mechanics of UNION

When Oracle Database executes a query containing UNION:

1. It executes the first component query and gathers its result set into temporary memory.
2. It executes the second component query and appends its rows to the collection.
3. It executes an internal SORT UNIQUE (or HASH UNIQUE) operation across all projected columns.
4. It eliminates any identical duplicate rows across all columns.
5. It returns the remaining distinct rows, sorted in ascending order by default according to the columns from left to right (primary sort on column 1, secondary sort on column 2, etc.).

Concrete Example: Active vs. Archived Employees

Consider two tables tracking active staff and historical archives:

-- Sample Table: CURRENT_STAFF
-- EMP_ID | JOB_ID  | DEPT_ID
-- 101    | SA_REP  | 80
-- 102    | MK_MAN  | 20
-- 103    | IT_PROG | 60
-- 104    | SA_REP  | 80

-- Sample Table: HISTORICAL_STAFF
-- EMP_ID | JOB_ID  | DEPT_ID
-- 102    | MK_MAN  | 20
-- 104    | SA_REP  | 80
-- 105    | HR_REP  | 40

Executing a UNION compound query:

SELECT employee_id, job_id, department_id 
FROM current_staff
UNION
SELECT employee_id, job_id, department_id 
FROM historical_staff;

Step-by-Step Row Processing:

1. CURRENT_STAFF produces 4 rows: (101, SA_REP, 80), (102, MK_MAN, 20), (103, IT_PROG, 60), (104, SA_REP, 80).
2. HISTORICAL_STAFF produces 3 rows: (102, MK_MAN, 20), (104, SA_REP, 80), (105, HR_REP, 40).
3. Total raw combined rows = 7.
4. Duplicate rows identified: (102, MK_MAN, 20) and (104, SA_REP, 80) appear in both tables.
5. Oracle eliminates the redundant occurrences and sorts the distinct rows by EMPLOYEE_ID ascending.

FINAL RESULT SET (UNION - 5 Unique Rows, Sorted):
+-------------+---------+---------------+
| EMPLOYEE_ID | JOB_ID  | DEPARTMENT_ID |
+-------------+---------+---------------+
| 101         | SA_REP  | 80            |
| 102         | MK_MAN  | 20            |
| 103         | IT_PROG | 60            |
| 104         | SA_REP  | 80            |
| 105         | HR_REP  | 40            |
+-------------+---------+---------------+

---

The UNION ALL Operator

The UNION ALL operator combines the results of two or more queries into a single result set without eliminating duplicate rows and without performing any sorting operation.

+-----------------------------------------------------------------------------------+
|                          UNION ALL ROW COMBINATION FLOW                           |
|                                                                                   |
|   Query 1 Result: [Row A, Row B, Row C]                                           |
|   Query 2 Result: [Row B, Row C, Row D]                                           |
|                                                                                   |
|   UNION ALL Output: [Row A, Row B, Row C, Row B, Row C, Row D]                    |
|   (All 6 rows returned; duplicates preserved; Query 1 rows output before Query 2) |
+-----------------------------------------------------------------------------------+

Executing the same query with UNION ALL:

SELECT employee_id, job_id, department_id 
FROM current_staff
UNION ALL
SELECT employee_id, job_id, department_id 
FROM historical_staff;

FINAL RESULT SET (UNION ALL - All 7 Rows Preserved, Unsorted):
+-------------+---------+---------------+
| EMPLOYEE_ID | JOB_ID  | DEPARTMENT_ID |
+-------------+---------+---------------+
| 101         | SA_REP  | 80            | <-- Query 1 Output Start
| 102         | MK_MAN  | 20            |
| 103         | IT_PROG | 60            |
| 104         | SA_REP  | 80            | <-- Query 1 Output End
| 102         | MK_MAN  | 20            | <-- Query 2 Output Start (Duplicate of Row 2)
| 104         | SA_REP  | 80            | <-- Query 2 (Duplicate of Row 4)
| 105         | HR_REP  | 40            | <-- Query 2 Output End
+-------------+---------+---------------+

Critical Observation: In UNION ALL, rows from the first query block are emitted first, followed immediately by the rows from the second query block in their natural retrieval sequence. No sorting is performed unless an explicit ORDER BY clause is attached at the end.

---

Execution Performance Analysis: UNION vs. UNION ALL

On the 1Z0-071 exam and in production enterprise systems, understanding the performance cost of UNION versus UNION ALL is crucial.

+-----------------------------------------------------------------------------------+
|                         EXECUTION PIPELINE COMPARISON                             |
|                                                                                   |
|  UNION ALL Pipeline:                                                              |
|  [Query Block 1] --------> [Append Stream] --------> [Client Output Stream]       |
|  [Query Block 2] ------------^                                                    |
|  (Zero sorting, minimal PGA memory, immediate first-row delivery)                 |
|                                                                                   |
|  UNION Pipeline:                                                                  |
|  [Query Block 1] --------> [Full Result Buffer] --> [SORT/HASH UNIQUE] --> [Output|
|  [Query Block 2] ------------^                      (PGA/Temp Disk Spill)         |
|  (High CPU utilization, sorting delay, blocks streaming until complete)          |
+-----------------------------------------------------------------------------------+

Why UNION ALL Is Dramatically Faster

1. Elimination of Sort Overhead: To eliminate duplicate rows, the database engine must compare every column value of every row against all other rows. In Oracle, this requires a SORT UNIQUE or HASH UNIQUE operation.
2. Memory and Temporary Tablespace Consumption: When combining large tables (e.g., millions of records), the sorting buffer may exceed available Program Global Area (PGA) memory allocation (SORT_AREA_SIZE / PGA_AGGREGATE_TARGET). When this happens, Oracle must spill intermediate sort runs to the physical Temporary Tablespace on disk, causing heavy I/O latency.
3. Pipelining and Time-to-First-Row: UNION ALL can stream rows to the client application immediately as they are retrieved from storage blocks. In contrast, UNION cannot return the first row until the entire dataset from all component queries has been fetched, buffered, sorted, and deduplicated.

Best Practice Recommendation

Development Rule: Always use UNION ALL by default unless business requirements strictly dictate that duplicate rows must be eliminated, or if you know with mathematical certainty that the component sets are completely disjoint (mutually exclusive) where duplicates cannot exist.

---

Column Count and Datatype Compatibility Rules

Oracle Database enforces strict semantic validation on all compound queries. Violating these rules prevents SQL parsing and generates immediate compilation errors.

                       COMPOUND QUERY COMPATIBILITY RULES

     Query 1: SELECT col1, col2, col3 FROM table_a
                     |     |     |
                     |     |     |   Must Match in Number (Arity / Degree)
                     |     |     |   Must Match in Datatype Family
                     v     v     v
     Query 2: SELECT colA, colB, colC FROM table_b

Rule 1: Identical Number of Projected Columns (Degree / Arity)

Every component SELECT statement in a compound query must select the exact same number of columns or expressions.

-- INVALID QUERY: Raises ORA-01789
SELECT employee_id, first_name, last_name FROM employees
UNION ALL
SELECT department_id, department_name FROM departments;

Oracle Error: ORA-01789: query block has incorrect number of result columns. Cause: Query 1 projects 3 columns, whereas Query 2 projects only 2 columns.

Rule 2: Compatible Datatype Families

Corresponding columns across all SELECT lists must belong to the same datatype family based on their ordinal position:

• Position 1 in Query 1 must match Position 1 in Query 2.
• Position 2 in Query 1 must match Position 2 in Query 2, and so forth.

-- INVALID QUERY: Raises ORA-01790
SELECT employee_id, hire_date FROM employees
UNION ALL
SELECT department_id, department_name FROM departments;

Oracle Error: ORA-01790: expression must have same datatype as corresponding expression. Cause: In position 2, HIRE_DATE is of datatype DATE, while DEPARTMENT_NAME is of datatype VARCHAR2. Because DATE and VARCHAR2 belong to incompatible datatype families, parsing fails.

Datatype Compatibility Matrix for Set Operators

Exam Trap: Oracle SQL does not perform automatic implicit datatype conversion across corresponding columns in set operations. Even if a VARCHAR2 column contains numeric text like '100', pairing it with a NUMBER column raises ORA-01790 unless explicit conversion is applied.

---

Controlling Column Types with Explicit Conversion and Placeholders

When combining datasets with differing structures or datatypes, developers must use explicit conversion functions and literal placeholders to ensure compatibility.

-- Standardizing Column Count and Datatypes across Unequal Tables
SELECT 
    employee_id                     AS entity_id,
    first_name || ' ' || last_name  AS entity_name,
    hire_date                       AS start_date,
    salary                          AS compensation,
    'INTERNAL_EMPLOYEE'             AS source_category
FROM employees
UNION ALL
SELECT 
    contractor_id                   AS entity_id,
    company_name                    AS entity_name,
    TO_DATE(contract_start, 'YYYY-MM-DD') AS start_date, -- Explicit Conversion
    hourly_rate * 2000              AS compensation,     -- Derived Calculation
    'EXTERNAL_CONTRACTOR'           AS source_category
FROM contractors
UNION ALL
SELECT 
    partner_id                      AS entity_id,
    partner_name                    AS entity_name,
    NULL                            AS start_date,       -- Typed NULL placeholder
    TO_NUMBER(NULL)                 AS compensation,     -- Explicit NULL Number
    'BUSINESS_PARTNER'              AS source_category
FROM business_partners;

Key Techniques Illustrated:

1. Typed NULL Placeholders: When a table lacks a corresponding attribute (such as start_date for business_partners), insert a literal NULL, TO_DATE(NULL), TO_NUMBER(NULL), or TO_CHAR(NULL) to balance the column count.
2. Explicit Type Casting: Convert strings to dates with TO_DATE() or numbers with TO_NUMBER() so that each ordinal position shares an identical datatype family.
3. Source Tagging Literals: Adding literal tags like 'INTERNAL_EMPLOYEE' allows downstream applications to identify the origin of each row.

---

Comprehensive Comparison: UNION vs. UNION ALL