LEARN. PRACTICE. GROW.Concept and mechanism
A subquery can supply a set or a scalar value, and that choice imposes different rules. A scalar supplies one column from at most one row: zero rows yields NULL and multiple rows raise an error. EXISTS tests matching existence without multiplying the outer row by every inner result. NOT IN needs care when its list contains NULL because UNKNOWN logic can remove expected results. Correlated NOT EXISTS can clearly express absence, but also define the meaning of an outer NULL key. Do not switch operators merely to suppress an error without confirming the business population. A query that returns fewer exceptions may simply be measuring less.
Guided application
In a fictional reconciliation, UNION ALL preserves occurrences from two sources, whereas UNION removes projected duplicates. MINUS identifies first-set values absent from the second; reversing order changes the question. INTERSECT finds the common part. Projections need compatible column counts and type groups. Retain source and a stable key when differences need explanation. For legacy top-N syntax, do not assume limited ROWNUM in the same block is applied after ORDER BY: sort in a subquery before limiting or use suitable row limiting. ROW_NUMBER supports numbering within partitions with deterministic ordering. Additional 26ai documentation features should not automatically be assumed in 19c installations or as new 1Z0-071 exam objectives.
SELECT id FROM expected MINUS SELECT id FROM received; identifies missing expected IDs without measuring multiplicity.
Common pitfalls
NOT IN with NULL; scalar as automatic selection; reversed MINUS; UNION as lossless concatenation; ROWNUM as ranking.
Related topics: Model, SELECT, and result population · Functions, conversions, and data contracts · Joins, grain, and aggregation
Choose the operator from the data question, including duplicates and absence.
Reference: Scalar cardinality · 1Z0-071 public objectives inspected 2026-09-30; revision date not published