1Z0-071 Exam Prep
Chapter 9
Volume 2
SET operators combine the results of two or more SELECT statements into a single result set. The 1Z0-071 exam tests the rules shared by all four operators, the differences between them, and the specific traps around column names, data types, ORDER BY placement, and duplicate handling.
The questions are often about what is valid syntax versus what raises an error — so the rules need to be precise, not approximate.
In this post
- The four SET operators and what they do
- Rules that apply to all SET operators
- UNION — combined rows, duplicates removed
- UNION ALL — combined rows, duplicates kept
- INTERSECT — rows that appear in both results
- MINUS — rows in the first result not in the second
- Column names in the final result
- ORDER BY with SET operators
- Operator precedence when combining multiple SET operators
- Quick reference
- Practice questions
01 — The four SET operators and what they do
| Operator | Returns | Duplicates |
|---|---|---|
UNION | All rows from both queries | Removed |
UNION ALL | All rows from both queries | Kept |
INTERSECT | Only rows that appear in both queries | Removed |
MINUS | Rows in the first query not found in the second | Removed |
02 — Rules that apply to all SET operators
These rules apply regardless of which SET operator you use. The exam tests all of them.
- Both SELECT statements must return the same number of columns
- Corresponding columns must have compatible data types
- Column names in the final result come from the first SELECT statement
- ORDER BY can only appear once — at the very end of the entire statement
- Individual SELECT statements in a SET operation cannot have their own ORDER BY
-- WRONG: different number of columns SELECT employee_id, last_name, salary FROM employees UNION SELECT department_id, department_name FROM departments; -- Error: queries have different number of columns -- WRONG: ORDER BY in the middle SELECT employee_id, last_name FROM employees ORDER BY last_name UNION SELECT department_id, department_name FROM departments; -- Error: ORDER BY not allowed here -- CORRECT: ORDER BY at the end only SELECT employee_id, last_name FROM employees UNION SELECT department_id, department_name FROM departments ORDER BY last_name;
Data type compatibility: The column types must be compatible — not necessarily identical. Oracle will implicitly convert where possible (for example, NUMBER and VARCHAR2 in the same column position raises an error because they are not compatible). The exam tests this with obvious mismatches.
03 — UNION — combined rows, duplicates removed
UNION combines all rows from both queries and removes duplicate rows from the final result. Two rows are considered duplicates only if every column value matches.
-- All unique department IDs from both employees and departments tables SELECT department_id FROM employees UNION SELECT department_id FROM departments; -- Duplicates removed: if dept 50 appears in both tables, it appears once -- NULL is treated as equal to NULL for duplicate checking in SET operators
NULL handling in SET operators: Unlike comparison operators where
NULL = NULL is UNKNOWN, SET operators treat NULL as equal to NULL for the purpose of duplicate elimination. Two rows where the same column position is NULL in both are considered duplicates.
04 — UNION ALL — combined rows, duplicates kept
UNION ALL combines all rows from both queries without removing any duplicates. It is faster than UNION because Oracle skips the sort and deduplication step.
-- All department IDs including duplicates SELECT department_id FROM employees UNION ALL SELECT department_id FROM departments; -- If dept 50 exists in both tables, it appears twice in the result -- Counting total rows to understand the difference -- Query A returns 107 rows, Query B returns 27 rows -- UNION may return fewer than 134 rows (duplicates removed) -- UNION ALL always returns exactly 134 rows
Exam row count questions: When the exam asks how many rows a SET operator returns, UNION ALL is the only one with a predictable count — it is always the sum of both query row counts. UNION, INTERSECT, and MINUS all depend on how many duplicates exist.
05 — INTERSECT — rows that appear in both results
INTERSECT returns only rows that are present in both query results. A row must match completely — every column — to be included. Duplicates in the output are removed.
-- Employees who are also listed as managers somewhere SELECT employee_id FROM employees INTERSECT SELECT manager_id FROM employees; -- Returns employee IDs that appear in both the employee list -- and the manager list — i.e. employees who manage someone
INTERSECT vs JOIN: INTERSECT and an INNER JOIN can sometimes answer the same question, but they are not the same thing. INTERSECT compares whole rows across two query results. A JOIN combines columns from two tables on a matching condition. The exam does not ask you to choose between them — but knowing the distinction helps you read queries correctly.
06 — MINUS — rows in the first result not in the second
MINUS returns rows from the first query that do not appear in the second query. Order matters — swapping the queries gives a different result. Duplicates in the output are removed.
-- Departments that have no employees SELECT department_id FROM departments MINUS SELECT department_id FROM employees; -- Returns department IDs that exist in DEPARTMENTS -- but do not appear in any employee row -- Swapping produces a completely different result SELECT department_id FROM employees MINUS SELECT department_id FROM departments; -- Returns employee department IDs that have no matching department row -- (employees assigned to a department that doesn't exist in DEPARTMENTS)
MINUS is not symmetric:
A MINUS B and B MINUS A produce different results. The exam will present both orders and ask which one answers the stated question correctly.
07 — Column names in the final result
When two queries are combined with a SET operator, the column names in the final result set come from the first SELECT statement only. The second query's column names are ignored entirely.
SELECT employee_id AS id, last_name AS name FROM employees UNION SELECT department_id AS dept, department_name FROM departments; -- Result columns are named: ID and NAME -- The second query's aliases (dept) and column names are ignored -- Ordering by column name uses the first query's names SELECT employee_id, last_name FROM employees UNION SELECT department_id, department_name FROM departments ORDER BY last_name; -- Valid: last_name is the column name from the first query
ORDER BY with column position: Because column names come from the first query, you can also ORDER BY column position number —
ORDER BY 2 sorts by the second column regardless of its name. This avoids any ambiguity about which query's names apply.
08 — ORDER BY with SET operators
A single ORDER BY clause goes at the very end of the entire SET operation. It sorts the final combined result. You cannot put ORDER BY inside any individual SELECT that is part of a SET operation.
-- CORRECT: single ORDER BY at the end SELECT last_name, department_id, salary FROM employees UNION SELECT last_name, department_id, 0 FROM retired_employees ORDER BY department_id, last_name; -- ORDER BY can reference column name from first query -- or column position number ORDER BY 2, 1; -- same as ORDER BY department_id, last_name
09 — Operator precedence when combining multiple SET operators
When you chain multiple SET operators in one statement, INTERSECT has higher precedence than UNION and MINUS. UNION and MINUS have equal precedence and are evaluated left to right. You can override precedence with parentheses.
-- INTERSECT is evaluated first, then UNION SELECT job_id FROM employees WHERE department_id = 10 UNION SELECT job_id FROM employees WHERE department_id = 20 INTERSECT SELECT job_id FROM employees WHERE department_id = 30; -- Evaluated as: dept10_jobs UNION (dept20_jobs INTERSECT dept30_jobs) -- Use parentheses to force a different order (SELECT job_id FROM employees WHERE department_id = 10 UNION SELECT job_id FROM employees WHERE department_id = 20) INTERSECT SELECT job_id FROM employees WHERE department_id = 30; -- Evaluated as: (dept10_jobs UNION dept20_jobs) INTERSECT dept30_jobs
Precedence rule: INTERSECT binds more tightly than UNION and MINUS. In a chain without parentheses, INTERSECT operations are resolved before any UNION or MINUS operations regardless of their position in the statement.
10 — Quick reference
| Rule | Detail |
|---|---|
| Column count must match | Both SELECT statements need the same number of columns |
| Data types must be compatible | Corresponding columns must hold compatible types |
| Column names from first query | Second query column names and aliases are ignored |
| ORDER BY placement | One ORDER BY only — at the end of the entire statement |
| UNION removes duplicates | Slower than UNION ALL due to sort and dedup step |
| UNION ALL keeps duplicates | Row count = sum of both query row counts exactly |
| INTERSECT | Returns rows common to both — duplicates removed |
| MINUS order matters | A MINUS B ≠ B MINUS A |
| NULL in SET operators | NULL equals NULL for duplicate checking — unlike comparison operators |
| INTERSECT precedence | Higher than UNION and MINUS — evaluated first without parentheses |
11 — Practice questions
Q1 — Query A returns 50 rows. Query B returns 30 rows. 10 rows appear in both results. How many rows does each operator return?
Q2 — Which of these statements about SET operators is true?
Q3 — A developer wants to find all department IDs that exist in the DEPARTMENTS table but have no employees assigned. Which query is correct?
Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set
19 chapters · 1,480 practice questions · 1,249 SQL examples
Chapter 9 covers all four SET operators with full practice sets testing column compatibility, ordering rules, NULL behaviour, and precedence. Instant PDF download.
Get the full guide — $25 →
Chapter 9 covers all four SET operators with full practice sets testing column compatibility, ordering rules, NULL behaviour, and precedence. Instant PDF download.
No comments:
Post a Comment