9.3 Set Operator Guidelines & ORDER BY Rules

9.3 Set Operator Guidelines & ORDER BY Rules

Writing compound queries in Oracle SQL requires adhering to strict syntactic and structural rules established by the relational engine. While combining queries with UNION, UNION ALL, INTERSECT, and MINUS provides immense expressive power, the 1Z0-071 exam heavily tests edge cases, sorting limitations, datatype restrictions, and precedence behaviors.

This section breaks down the universal guidelines and common error traps associated with set operations in Oracle Database.

---

Output Column Names and Column Aliases

A fundamental rule of Oracle compound queries governs how column headers in the final output result set are labeled:

The Column Naming Rule: The column names and column aliases displayed in the final query result set are determined strictly and exclusively by the SELECT list of the first component query.

Any column aliases defined in the second, third, or subsequent component queries are completely ignored by Oracle for header formatting.

-- Demonstrating Column Header Determination
SELECT employee_id AS emp_identification, first_name AS given_name, salary AS monthly_compensation
FROM employees
WHERE department_id = 10
UNION ALL
SELECT contractor_id AS c_id, contractor_name AS c_name, hourly_rate * 160 AS rate
FROM contractors
WHERE department_id = 10;

OUTPUT COLUMN HEADERS:
+--------------------+------------+----------------------+
| EMP_IDENTIFICATION | GIVEN_NAME | MONTHLY_COMPENSATION |
+--------------------+------------+----------------------+
| 200                | Jennifer   | 4400.00              |
| 901                | Robert     | 8000.00              |
+--------------------+------------+----------------------+

Notice that the output headers use EMP_IDENTIFICATION, GIVEN_NAME, and MONTHLY_COMPENSATION from Query 1. The aliases C_ID, C_NAME, and RATE from Query 2 have no effect on output naming.

---

The Rules of the ORDER BY Clause in Compound Queries

Sorting data in compound queries follows very specific rules that differ significantly from single SELECT statements. Questions testing ORDER BY in set operations appear frequently on the 1Z0-071 exam.

+-----------------------------------------------------------------------------------+
|                         ORDER BY RULES IN SET OPERATIONS                          |
|                                                                                   |
|  1. Placement: Must appear ONLY ONCE at the VERY END of the compound query.       |
|  2. Referencing: Must use Column Names/Aliases from QUERY 1, or Numeric Positions.|
|  3. Illegality: Cannot be placed on individual component queries (unless inline). |
+-----------------------------------------------------------------------------------+

Rule 1: Placement at the Very End

The ORDER BY clause can appear only once in the entire compound statement, and it must be the last clause of the final component query.

-- INVALID QUERY: ORDER BY inside component query raises ORA-00933
SELECT employee_id, first_name FROM employees WHERE department_id = 10
ORDER BY employee_id -- ERROR: ORDER BY cannot be placed here!
UNION ALL
SELECT contractor_id, contractor_name FROM contractors WHERE department_id = 10;

Oracle Error: ORA-00933: SQL command not properly ended.

-- VALID QUERY: Single ORDER BY at the very end
SELECT employee_id, first_name FROM employees WHERE department_id = 10
UNION ALL
SELECT contractor_id, contractor_name FROM contractors WHERE department_id = 10
ORDER BY employee_id;

Rule 2: Valid Identifiers in the ORDER BY Clause

Because the result set column names are established by the first query block, the terminating ORDER BY clause can reference:

1. Column names from the SELECT list of the first query.
2. Column aliases defined in the SELECT list of the first query.
3. 1-based numeric positional notation (ORDER BY 1, 2 DESC).

Attempting to reference a column name or alias that exists only in the second or subsequent query blocks raises an ORA-00904 error:

-- INVALID QUERY: Referencing Query 2 alias in ORDER BY
SELECT employee_id AS emp_num, last_name FROM employees
UNION ALL
SELECT contractor_id AS cont_num, contractor_name FROM contractors
ORDER BY cont_num; -- ERROR: CONT_NUM is not recognized from Query 1

Oracle Error: ORA-00904: "CONT_NUM": invalid identifier.

-- VALID CORRECTIONS:
-- Option A: Reference Query 1 alias
SELECT employee_id AS emp_num, last_name FROM employees
UNION ALL
SELECT contractor_id AS cont_num, contractor_name FROM contractors
ORDER BY emp_num;

-- Option B: Reference 1-based positional number
SELECT employee_id AS emp_num, last_name FROM employees
UNION ALL
SELECT contractor_id AS cont_num, contractor_name FROM contractors
ORDER BY 1 DESC;

---

Unsupported Datatypes in Set Operations

Not all Oracle datatypes can be used with set operators. Set operators must compare full row values to evaluate equality, perform deduplication, or sort records. Certain data structures do not support direct binary or hash comparisons in the relational engine.

+-----------------------------------------------------------------------------------+
|                         DATATYPE SUPPORT IN SET OPERATIONS                        |
|                                                                                   |
|  SUPPORTED DATATYPES:                                                             |
|  - Character: VARCHAR2, CHAR, NVARCHAR2, NCHAR                                    |
|  - Numeric: NUMBER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE                            |
|  - Datetime: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, INTERVAL                 |
|  - Identifiers: ROWID, UROWID                                                     |
|                                                                                   |
|  UNSUPPORTED DATATYPES (Raise ORA-00932 / ORA-00997):                             |
|  - Large Objects: BLOB, CLOB, NCLOB, BFILE                                       |
|  - Legacy Types: LONG, LONG RAW                                                   |
|  - Collections: VARRAY, Nested Tables, Object Types (unless MAP method defined)  |
+-----------------------------------------------------------------------------------+

Why LOBs and LONGs Are Prohibited

• CLOB and BLOB columns can store gigabytes of unstructured data. Comparing multi-gigabyte LOB locators across millions of rows for sorting or duplicate elimination would exhaust memory and disk subsystems.
• LONG and LONG RAW columns are legacy storage formats with strict restrictions (e.g., only one LONG column per table, cannot be used in WHERE, GROUP BY, DISTINCT, or set operations).

-- INVALID QUERY: CLOB in set operation raises ORA-00932
SELECT document_id, document_title, document_body_clob FROM legal_docs_2024
UNION
SELECT document_id, document_title, document_body_clob FROM legal_docs_2025;

Oracle Error: ORA-00932: inconsistent datatypes: expected - got CLOB.

Workaround for LOB Queries

If LOB data must be retrieved from compound sets, perform the set operation on unique primary keys first, or convert a bounded substring of the CLOB using DBMS_LOB.SUBSTR:

-- Valid Workaround: Substring extraction for comparison
SELECT document_id, DBMS_LOB.SUBSTR(document_body_clob, 4000, 1) AS doc_preview
FROM legal_docs_2024
UNION ALL
SELECT document_id, DBMS_LOB.SUBSTR(document_body_clob, 4000, 1) AS doc_preview
FROM legal_docs_2025;

---

Compound Query Precedence & Parenthetical Grouping

When three or more component queries are combined using multiple set operators, Oracle evaluates them according to strict precedence rules:

The Precedence Rule: All four set operators (UNION, UNION ALL, INTERSECT, MINUS) have EQUAL precedence. Oracle evaluates compound queries strictly from left to right (top to bottom) in the order they appear in the SQL text.

DEFAULT EVALUATION FLOW (Equal Precedence - Left to Right):

Query A  \ 
          --- [Operator 1] ---> Intermediate Result Set  \ 
Query B  /                                                --- [Operator 2] ---> Final Result
                                                 Query C / 

Overriding Precedence with Parentheses

To alter the default top-to-bottom evaluation sequence, you can enclose component queries within parentheses. The parenthesized compound query will be evaluated first, and its result set will be passed to the outer operator.

PARENTHESIZED EVALUATION FLOW:

                                                 Query B  \ 
                                                           --- [Operator 2] ---> Intermediate Set
                                                 Query C  /                        |
                                                                                   v
Query A ------------------------------------------------------------> [Operator 1] ---> Final Result

Concrete Example: Precedence Impact on Output

Consider three sets of numbers:

• $\text{Set } A = {1, 2, 3, 4}$
• $\text{Set } B = {3, 4, 5, 6}$
• $\text{Set } C = {4, 5, 7}$

Scenario 1: Default Left-to-Right Evaluation (A UNION B MINUS C)

SELECT num FROM set_a
UNION
SELECT num FROM set_b
MINUS
SELECT num FROM set_c;

1. First operation: Set A UNION Set B $\rightarrow {1, 2, 3, 4, 5, 6}$
2. Second operation: \{1, 2, 3, 4, 5, 6\} MINUS Set C (\{4, 5, 7\}) $\rightarrow \mathbf{{1, 2, 3, 6}}$

Scenario 2: Parenthesized Evaluation (A UNION (B MINUS C))

SELECT num FROM set_a
UNION
(SELECT num FROM set_b
 MINUS
 SELECT num FROM set_c);

1. Parenthesized operation: Set B MINUS Set C $\rightarrow {3, 4, 5, 6} - {4, 5, 7} = {3, 6}$
2. Outer operation: Set A (\{1, 2, 3, 4\}) UNION \{3, 6\} $\rightarrow \mathbf{{1, 2, 3, 4, 6}}$

Key Takeaway: Scenario 1 returns [1, 2, 3, 6], whereas Scenario 2 returns [1, 2, 3, 4, 6]. Adding parentheses completely changes the outcome!

---

Compound Queries in DDL, DML, and Subqueries

Compound queries can be seamlessly integrated into broader SQL statements:

1. Create Table As Select (CTAS)

CREATE TABLE all_active_personnel AS
SELECT employee_id, first_name, last_name, email, hire_date FROM employees
UNION ALL
SELECT contractor_id, first_name, last_name, email, contract_start FROM contractors;

2. Multi-Row Insert via Subquery

INSERT INTO audit_log_summary (user_id, action_date, action_name)
SELECT user_id, login_time, 'LOGIN' FROM login_history
UNION ALL
SELECT user_id, logout_time, 'LOGOUT' FROM logout_history;

3. Inline Views in the FROM Clause

SELECT department_id, COUNT(*) AS staff_count, AVG(compensation) AS avg_comp
FROM (
    SELECT department_id, salary AS compensation FROM employees
    UNION ALL
    SELECT department_id, hourly_rate * 160 AS compensation FROM contractors
)
GROUP BY department_id
HAVING COUNT(*) > 5
ORDER BY avg_comp DESC;

---

Common Set Operator Errors Reference Guide