Scalar/Correlated Subqueries, EXISTS/IN, Semijoins, Antijoins, and Subquery Factoring

Learning outcomes

ServiceHub needs to answer questions such as “which assets have work orders?”, “which assets have none?”, and “what is the latest work-order cost for each asset?” These questions can be expressed with scalar subqueries, correlated subqueries, EXISTS, IN, and common table expressions. Oracle may later transform some of them into joins, but the transformation is safe only because the original SQL semantics are well-defined.

1. Classify the subquery by the promise it makes

A scalar subquery must return at most one row and one column; zero rows yield null, while more than one row raises an error. An uncorrelated subquery can be evaluated without values from the outer query. A correlated subquery references an outer query block and is logically evaluated in that context, although the optimizer may transform the physical execution.

SELECT    a.asset_id,    a.asset_name,    (SELECT MAX(w.estimated_cost)       FROM servicehub_sql_work_order_s w      WHERE w.asset_id = a.asset_id) AS max_estimated_costFROM servicehub_sql_asset_s aORDER BY a.asset_id;SELECT a.asset_id, a.asset_nameFROM servicehub_sql_asset_s aWHERE EXISTS (    SELECT 1    FROM servicehub_sql_work_order_s w    WHERE w.asset_id = a.asset_id)ORDER BY a.asset_id;

The first subquery is scalar because MAX guarantees one aggregate row for each outer asset. The second expresses an existence question; duplicates in the work-order table do not duplicate the asset row.

2. Deliberately wrong: a scalar subquery returns multiple rows

If you remove the aggregate and an asset has two work orders, the scalar contract is violated.

SELECT    a.asset_id,    (SELECT w.estimated_cost       FROM servicehub_sql_work_order_s w      WHERE w.asset_id = a.asset_id) AS estimated_costFROM servicehub_sql_asset_s a;-- ORA-01427: single-row subquery returns more than one row

The repair depends on the business meaning. If you need the maximum cost, aggregate with MAX. If you need the latest work order, define “latest” with a deterministic ordering rule and return exactly one row. If you need every work order, a join or separate result set is the correct shape. Do not silence ORA-01427 with an arbitrary row limit unless that row is genuinely the required business row.

3. EXISTS and IN can express the same positive membership question

For many positive membership predicates, IN and EXISTS can be semantically equivalent. Oracle’s optimizer may transform either into a semijoin, which returns an outer row when at least one inner match exists without multiplying the outer row for multiple matches.

SELECT a.asset_id, a.asset_nameFROM servicehub_sql_asset_s aWHERE a.asset_id IN (    SELECT w.asset_id    FROM servicehub_sql_work_order_s w    WHERE w.asset_id IS NOT NULL)ORDER BY a.asset_id;SELECT a.asset_id, a.asset_nameFROM servicehub_sql_asset_s aWHERE EXISTS (    SELECT 1    FROM servicehub_sql_work_order_s w    WHERE w.asset_id = a.asset_id)ORDER BY a.asset_id;

Write the form that best communicates the relationship and handles nullability correctly. Do not force a semijoin hint merely because you learned the name of the transformation; the optimizer chooses among legal join methods using cost and statistics.

4. The NOT IN NULL trap is a semantic failure, not an optimizer bug

NOT IN is equivalent to a comparison against all values. If the right-hand set contains a null, the comparison includes an unknown result, so the overall predicate cannot become true for ordinary values. Current Oracle documentation explicitly warns that a NOT IN list or subquery containing null can return no rows.

SELECT a.asset_id, a.asset_nameFROM servicehub_sql_asset_s aWHERE a.asset_id NOT IN (    SELECT w.asset_id    FROM servicehub_sql_work_order_s w);-- If the subquery contains NULL, this can return no assets.

For “no matching row exists,” NOT EXISTS usually expresses the business meaning directly and is not poisoned by unrelated nulls in the inner column.

SELECT a.asset_id, a.asset_nameFROM servicehub_sql_asset_s aWHERE NOT EXISTS (    SELECT 1    FROM servicehub_sql_work_order_s w    WHERE w.asset_id = a.asset_id)ORDER BY a.asset_id;

Another legal repair is to prove the NOT IN subquery cannot return null—for example with a NOT NULL constraint or an explicit IS NOT NULL predicate. Choose the form that makes the invariant visible.

5. Observe semijoin and antijoin transformations without depending on them

Oracle’s optimizer can flatten eligible existence subqueries into semijoin or antijoin operations. EXPLAIN PLAN is sufficient to see whether the estimated plan chosen for this tiny lab contains operations such as HASH JOIN SEMI, NESTED LOOPS SEMI, or an ANTI variant. Exact operators are not guaranteed.

EXPLAIN PLAN SET STATEMENT_ID = 'SH05L3_EXISTS'FORSELECT a.asset_idFROM servicehub_sql_asset_s aWHERE EXISTS (    SELECT 1    FROM servicehub_sql_work_order_s w    WHERE w.asset_id = a.asset_id);SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'SH05L3_EXISTS', 'BASIC +PREDICATE'));EXPLAIN PLAN SET STATEMENT_ID = 'SH05L3_NOTEXISTS'FORSELECT a.asset_idFROM servicehub_sql_asset_s aWHERE NOT EXISTS (    SELECT 1    FROM servicehub_sql_work_order_s w    WHERE w.asset_id = a.asset_id);SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'SH05L3_NOTEXISTS', 'BASIC +PREDICATE'));

Plan shape is implementation evidence, not the definition of EXISTS. If Oracle later chooses another legal plan, the required result remains the same.

6. WITH names a query block; it does not promise materialization

Oracle’s WITH clause, also called subquery factoring, lets you name a query block and reference it later. The optimizer may treat that block like an inline view or may choose a temporary transformation; do not rely on a particular materialization strategy unless you have a measured, version-appropriate reason and understand the implications.

WITH active_work_orders AS (    SELECT work_order_id, asset_id, estimated_cost    FROM servicehub_sql_work_order_s    WHERE status_code = 'OPEN')SELECT    a.asset_id,    a.asset_name,    COUNT(w.work_order_id) AS open_work_ordersFROM servicehub_sql_asset_s aLEFT JOIN active_work_orders w  ON w.asset_id = a.asset_idGROUP BY a.asset_id, a.asset_nameORDER BY a.asset_id;

7. Hands-on lab: make NULL and transformation behavior visible

DROP TABLE servicehub_sql_work_order_s IF EXISTS PURGE;DROP TABLE servicehub_sql_asset_s IF EXISTS PURGE;CREATE TABLE servicehub_sql_asset_s (    asset_id    NUMBER PRIMARY KEY,    asset_name  VARCHAR2(80) NOT NULL);CREATE TABLE servicehub_sql_work_order_s (    work_order_id  NUMBER PRIMARY KEY,    asset_id       NUMBER,    status_code    VARCHAR2(20) NOT NULL,    estimated_cost NUMBER(10,2));INSERT INTO servicehub_sql_asset_s VALUES (1,'Pump A');INSERT INTO servicehub_sql_asset_s VALUES (2,'Pump B');INSERT INTO servicehub_sql_asset_s VALUES (3,'Valve C');INSERT INTO servicehub_sql_asset_s VALUES (4,'Fan D');INSERT INTO servicehub_sql_work_order_s VALUES (201,1,'OPEN',400);INSERT INTO servicehub_sql_work_order_s VALUES (202,1,'CLOSED',250);INSERT INTO servicehub_sql_work_order_s VALUES (203,2,'OPEN',300);INSERT INTO servicehub_sql_work_order_s VALUES (204,NULL,'OPEN',50);COMMIT;

SELECT a.asset_idFROM servicehub_sql_asset_s aWHERE a.asset_id NOT IN (    SELECT w.asset_id    FROM servicehub_sql_work_order_s w)ORDER BY a.asset_id;-- Expected: no rows because the subquery includes NULL.SELECT a.asset_idFROM servicehub_sql_asset_s aWHERE NOT EXISTS (    SELECT 1    FROM servicehub_sql_work_order_s w    WHERE w.asset_id = a.asset_id)ORDER BY a.asset_id;-- Expected asset IDs: 3 and 4.

DROP TABLE servicehub_sql_work_order_s PURGE;DROP TABLE servicehub_sql_asset_s PURGE;

8. Production judgment and next step

Choose subquery forms from semantics: scalar subqueries require a true single-row contract; EXISTS/NOT EXISTS express existence cleanly; IN/NOT IN require careful null reasoning. Let constraints make nullability guarantees explicit. Use DBMS_XPLAN to observe transformations, but do not code against a particular semijoin/antijoin operator.

No licensed pack is needed for the estimated-plan path used here. Later performance chapters distinguish EXPLAIN PLAN from executed-cursor statistics and licensing-sensitive diagnostics. Lesson 4 now combines entire query results with set operators, where duplicate and null semantics become the main correctness boundary.

Authoritative references

• IN Condition — NOT IN and NULL semantics
• Joins — Semijoins and Antijoins — optimizer semijoin/antijoin concepts and NULL-aware behavior
• Unnesting of Nested Subqueries — subquery transformation rules
• SELECT — subquery_factoring_clause — WITH clause semantics and restrictions
• DBMS_XPLAN — plan display functions