UNION/UNION ALL/INTERSECT/MINUS, Query Composition, and Duplicate Semantics

Learning outcomes

ServiceHub keeps a current queue and an archive. Analysts need “all rows,” “unique rows,” “rows present in both,” and “rows present only in the current system.” These requirements sound similar but map to different set operators. Choosing UNION when duplicates are meaningful—or assuming another database’s EXCEPT support without checking Oracle’s current syntax—changes both semantics and work performed.

1. Set operators combine rows by position

Each component query must return the same number of expressions, and corresponding expressions must belong to compatible data-type groups. Column names in the final result are derived from the first component query. Set operators do not inspect primary keys or infer relationships.

Current Oracle AI Database 26ai documentation lists EXCEPT as a synonym for MINUS and supports INTERSECT ALL, MINUS ALL, and EXCEPT ALL. Treat that as current-version syntax; do not assume an older Oracle estate accepts every synonym or ALL form.

2. UNION ALL and UNION answer different questions

If ServiceHub wants an event feed that preserves repeated rows from current and archive sources, UNION ALL is the correct operator. If the business definition says identical projected rows represent one logical result, UNION performs duplicate elimination.

SELECT event_code, region_codeFROM servicehub_sql_current_setUNION ALLSELECT event_code, region_codeFROM servicehub_sql_archive_setORDER BY event_code, region_code;SELECT event_code, region_codeFROM servicehub_sql_current_setUNIONSELECT event_code, region_codeFROM servicehub_sql_archive_setORDER BY event_code, region_code;

Do not use UNION merely to “be safe.” Duplicate elimination may require sorting or hashing and, more importantly, it changes meaning. First define whether duplicates are legitimate records or redundant projections.

3. Deliberately wrong: corresponding columns are from incompatible type groups

Oracle does not simply coerce any corresponding expressions across set-operator branches. A numeric expression paired with an unrelated character expression can raise a data-type compatibility error.

SELECT event_idFROM servicehub_sql_current_setUNIONSELECT event_codeFROM servicehub_sql_archive_set;-- ORA-01790: expression must have same datatype as corresponding expression

Repair the contract explicitly. If text is genuinely the desired common representation, cast the number to a sufficiently sized VARCHAR2. If the columns represent different business concepts, the real repair is to stop combining them.

SELECT TO_CHAR(event_id) AS event_keyFROM servicehub_sql_current_setUNIONSELECT event_code AS event_keyFROM servicehub_sql_archive_setORDER BY event_key;

4. NULL participates in set semantics differently from ordinary equality

In a WHERE predicate, NULL = NULL is not true. Set operators nevertheless have defined duplicate/multiset behavior for rows containing nulls. Current Oracle documentation describes occurrence-based handling for the ALL variants—for example, MINUS ALL subtracts occurrences, including null occurrences.

SELECT region_codeFROM servicehub_sql_current_setINTERSECT ALLSELECT region_codeFROM servicehub_sql_archive_setORDER BY region_code NULLS LAST;SELECT region_codeFROM servicehub_sql_current_setMINUS ALLSELECT region_codeFROM servicehub_sql_archive_setORDER BY region_code NULLS LAST;

This is another reason not to “simulate” a set operator with ordinary equality predicates unless you have proven equivalent null and duplicate semantics.

5. ORDER BY belongs to the final compound result

A compound query has one final result set. Apply the final ORDER BY there, using output column names/aliases or valid positions. If component queries need ordering for a separate row-limiting or analytic reason, isolate that logic in subqueries rather than assuming branch-level ordering controls the compound result.

SELECT event_code AS code, region_codeFROM servicehub_sql_current_setUNION ALLSELECT event_code, region_codeFROM servicehub_sql_archive_setORDER BY code, region_code NULLS LAST;

When several set operators are chained, use parentheses or factored query blocks to make intent unmistakable. Current Oracle documentation treats the set operators with equal precedence; clear grouping also protects maintainability if standards or future precedence rules evolve.

6. Execution evidence: duplicate elimination has work, but operator shape is not a contract

Compare estimated plans for UNION ALL and UNION. A duplicate-eliminating query typically needs a uniqueness operation such as a sort or hash step, while UNION ALL can concatenate row sources. Exact plan operators remain optimizer choices.

EXPLAIN PLAN SET STATEMENT_ID = 'SH05L4_ALL'FORSELECT event_code, region_code FROM servicehub_sql_current_setUNION ALLSELECT event_code, region_code FROM servicehub_sql_archive_set;SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'SH05L4_ALL', 'BASIC'));EXPLAIN PLAN SET STATEMENT_ID = 'SH05L4_UNION'FORSELECT event_code, region_code FROM servicehub_sql_current_setUNIONSELECT event_code, region_code FROM servicehub_sql_archive_set;SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'SH05L4_UNION', 'BASIC'));

7. Hands-on lab: current versus archive as a multiset

DROP TABLE servicehub_sql_archive_set IF EXISTS PURGE;DROP TABLE servicehub_sql_current_set IF EXISTS PURGE;CREATE TABLE servicehub_sql_current_set (    event_id     NUMBER,    event_code   VARCHAR2(30),    region_code  VARCHAR2(10));CREATE TABLE servicehub_sql_archive_set (    event_id     NUMBER,    event_code   VARCHAR2(30),    region_code  VARCHAR2(10));INSERT INTO servicehub_sql_current_set VALUES (1,'EV-A','NORTH');INSERT INTO servicehub_sql_current_set VALUES (2,'EV-B','SOUTH');INSERT INTO servicehub_sql_current_set VALUES (3,'EV-B','SOUTH');INSERT INTO servicehub_sql_current_set VALUES (4,'EV-C',NULL);INSERT INTO servicehub_sql_archive_set VALUES (10,'EV-B','SOUTH');INSERT INTO servicehub_sql_archive_set VALUES (11,'EV-C',NULL);INSERT INTO servicehub_sql_archive_set VALUES (12,'EV-D','WEST');COMMIT;

SELECT event_code, region_codeFROM servicehub_sql_current_setUNION ALLSELECT event_code, region_codeFROM servicehub_sql_archive_setORDER BY event_code, region_code NULLS LAST;SELECT event_code, region_codeFROM servicehub_sql_current_setUNIONSELECT event_code, region_codeFROM servicehub_sql_archive_setORDER BY event_code, region_code NULLS LAST;SELECT event_code, region_codeFROM servicehub_sql_current_setINTERSECTSELECT event_code, region_codeFROM servicehub_sql_archive_setORDER BY event_code, region_code NULLS LAST;SELECT event_code, region_codeFROM servicehub_sql_current_setEXCEPTSELECT event_code, region_codeFROM servicehub_sql_archive_setORDER BY event_code, region_code NULLS LAST;

DROP TABLE servicehub_sql_archive_set PURGE;DROP TABLE servicehub_sql_current_set PURGE;

8. Production judgment and next step

Choose set operators from data semantics, not from habit. Preserve duplicates with UNION ALL when they are meaningful; eliminate them only when the result definition requires set semantics. Make type conversion explicit, test nulls, and apply a deterministic final ordering when consumers depend on order. Check target-version syntax before using newer aliases or ALL variants in mixed-version estates.

No extra edition, pack, restart, parameter, or COMPATIBLE change is required for these Chapter 05 mandatory labs on the declared 26ai baseline. Lesson 5 now tackles hierarchical data, where traversal order and cycle handling become part of correctness.

Authoritative references

• Set Operators — current 26ai operator list
• The Set Operators — type compatibility, NULL, duplicate, EXCEPT, and ALL semantics
• SELECT — compound query and ORDER BY syntax
• DBMS_XPLAN — plan display