ExamFinch.com My Study

Study guide

9.1 UNION and UNION ALL Operators

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

9.1 UNION and UNION ALL Operators

Key Takeaways Explain This Topic AI

Set operators combine row outputs from two or more independent SELECT queries horizontally into a single unified result set.

The UNION operator merges result sets, eliminates duplicate rows, and automatically sorts the final output in ascending order by the first column (and subsequent columns).

The UNION ALL operator merges result sets while preserving all duplicate rows, does NOT sort the output, and executes significantly faster with lower CPU and memory overhead.

All component SELECT statements in a compound query must have the exact same number of expressions (Degree) and corresponding columns must belong to compatible datatype families.

Explicit conversion functions (TO_CHAR, TO_NUMBER, TO_DATE) and typed NULL literals (e.g., TO_CHAR(NULL), CAST(NULL AS NUMBER)) should be used to align mismatched column types and arities.

Last updated: August 2026

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:

UNION : Merges result sets, eliminates duplicate rows , and automatically sorts the final output.

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 ∪ B A \cup B A ∪ 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 :

It executes the first component query and gathers its result set into temporary memory.

It executes the second component query and appends its rows to the collection.

It executes an internal SORT UNIQUE (or HASH UNIQUE ) operation across all projected columns.

It eliminates any identical duplicate rows across all columns.

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:

CURRENT_STAFF produces 4 rows: (101, SA_REP, 80) , (102, MK_MAN, 20) , (103, IT_PROG, 60) , (104, SA_REP, 80) .

HISTORICAL_STAFF produces 3 rows: (102, MK_MAN, 20) , (104, SA_REP, 80) , (105, HR_REP, 40) .

Total raw combined rows = 7.

Duplicate rows identified: (102, MK_MAN, 20) and (104, SA_REP, 80) appear in both tables.

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

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.

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.

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

Column 1 Type Column 2 Type Compatible? Oracle Behavior

NUMBER(6) NUMBER(10,2) YES Valid; numeric precision and scale adjust dynamically.

VARCHAR2(20) CHAR(30) YES Valid; character lengths promote to the maximum size.

DATE TIMESTAMP YES Valid; promoted to TIMESTAMP .

VARCHAR2(50) NUMBER NO Raises ORA-01790 ; no implicit conversion performed across queries.

DATE VARCHAR2(20) NO Raises ORA-01790 ; conversion must be explicitly coded.

NUMBER DATE NO Raises ORA-01790 ; completely incompatible datatype families.

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:

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.

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.

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

  • Feature / Attribute UNION UNION ALL
  • Duplicate Elimination Yes (eliminates duplicate rows across all columns) No (preserves all duplicate rows)

Default Sort Behavior Automatically sorts output ascending by Column 1, 2... Does not sort; returns natural retrieval order

Execution Speed Slower (requires sorting / hashing entire dataset) Fast (streams rows directly with zero sort delay)

Memory (PGA) Usage High (allocates sort memory; may spill to Temp disk) Minimal (requires no sorting buffer)

First-Row Latency High (must process all rows before returning row 1) Instantaneous (can stream first rows immediately)

Result Row Count ≤ Total rows of Query 1 + Query 2 \le \text{Total rows of Query 1} + \text{Query 2} ≤ Total rows of Query 1 + Query 2 Exactly = Total rows of Query 1 + Query 2 = \text{Total rows of Query 1} + \text{Query 2} = Total rows of Query 1 + Query 2

Ideal Use Case When distinct unique records are strictly required When combining non-overlapping sets or logging events