Oracle Database / Intermediate: application SQL and evidence / Lesson 9

Subqueries, factoring and hierarchical paths

Compose readable query stages and traverse depot relationships without confusing path semantics with materialization.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Choose scalar, existence and factored subqueries by cardinality.
Read START WITH, CONNECT BY and cycle handling.

A subquery has a row-count contract

A scalar subquery returns one column from at most one row. No row becomes NULL; more than one row causes an error. That makes it suitable only when keys or aggregation guarantee the expected cardinality. Do not hide an ambiguous relationship by wrapping an arbitrary MIN around a descriptive value unless choosing the minimum is the actual business rule. EXISTS asks only whether a qualifying row exists and does not need that scalar guarantee. Name the expected row count for every embedded query during review, especially when a previously unique source can gain historical revisions.

Factoring names a reasoning step

A WITH clause gives a query block a name so a reader can inspect its input grain and output contract. The optimizer may merge, inline or materialize eligible blocks; the text is not a promise that an intermediate table is created or computed once. Use factoring to separate the shipment population from per-customer totals and final selection. Keep period boundaries inside the population stage so every downstream measure uses the same rows. Avoid assuming that a factored block acts as a security boundary or prevents all predicate movement. Those requirements need supported database mechanisms.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

WITH october_shipments AS (
 SELECT customer_id, charge FROM shipments
 WHERE booked_at >= DATE '2026-10-01' AND booked_at < DATE '2026-11-01'
), customer_totals AS (
 SELECT customer_id, SUM(charge) AS charge_total
 FROM october_shipments GROUP BY customer_id
)
SELECT customer_id, charge_total FROM customer_totals
WHERE charge_total >= 100
ORDER BY customer_id;

Each stage has a visible grain: shipment first, customer second. Only customers with qualifying October totals are returned.

Declare a root and a parent-child direction

A depot hierarchy can store depot_id and parent_id. START WITH selects the roots, while CONNECT BY PRIOR depot_id = parent_id means the previous parent’s identifier matches the current child’s parent reference. Reversing PRIOR changes the traversal direction. LEVEL counts the root as one, and SYS_CONNECT_BY_PATH can display the route taken. A node reachable through multiple relationships can appear on multiple paths; hierarchical traversal is not automatically a distinct-node inventory. Decide whether the business result counts depots, links or complete routes before aggregating it.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

WITH depots AS (
 SELECT 1 depot_id, CAST(NULL AS NUMBER) parent_id, 'Hub' label FROM dual UNION ALL
 SELECT 2, 1, 'East' FROM dual UNION ALL
 SELECT 3, 2, 'Gate' FROM dual
)
SELECT depot_id, LEVEL AS depth, SYS_CONNECT_BY_PATH(label, '/') AS route
FROM depots
START WITH parent_id IS NULL
CONNECT BY PRIOR depot_id = parent_id
ORDER SIBLINGS BY depot_id;

Expected output

DEPOT_ID | DEPTH | ROUTE
1        | 1     | /Hub
2        | 2     | /Hub/East
3        | 3     | /Hub/East/Gate

The three-node chain produces /Hub, /Hub/East and /Hub/East/Gate at depths 1, 2 and 3.

Cycle tolerance does not repair a graph

An accidental parent link can create a cycle and ORA-01436. NOCYCLE allows a hierarchical query to return data despite a loop, and CONNECT_BY_ISCYCLE identifies relevant cycle conditions under that mode. It is a diagnostic or bounded traversal choice, not proof that the stored graph is a valid tree. Capture the offending links and correct them through the domain workflow. Keep a depth or scope limit when the application permits arbitrary graphs, and define how repeated nodes affect totals. Never sum shipment charges once per possible depot path unless that multiplication is explicitly required.

Filtering a displayed node is not pruning its descendants

Oracle hierarchical processing distinguishes root selection, relationship traversal and later WHERE filtering. A later WHERE condition can remove one displayed node without removing all descendants reached through it. If the requirement is to prevent traversal through a forbidden depot, encode that restriction at the appropriate traversal or source stage and test the path behavior carefully. ORDER SIBLINGS BY preserves sibling sorting within the hierarchy; a global ORDER BY can destroy the hierarchical presentation. Recursive subquery factoring offers another documented approach, but its syntax and cycle clauses should not be copied from PostgreSQL without checking Oracle rules.

Common errors and fixes

ORA-01427

Why it happens: A scalar subquery returned more than one row.
How to fix it: Repair the key restriction or change to the intended set-based question.
Prevent it next time: Assert expected cardinality using source constraints and duplicate fixtures.

ORA-01436

Why it happens: The hierarchical relationship loops back to an ancestor.
How to fix it: Identify the cycle and use NOCYCLE only with explicit diagnostic semantics.
Prevent it next time: Validate parent changes and test cycles in hierarchy import reviews.

Descendants remain after a parent is filtered out

Why it happens: A post-traversal WHERE predicate was mistaken for subtree pruning.
How to fix it: Place the scope restriction at the source or traversal stage as required.
Prevent it next time: Test a forbidden intermediate node with an otherwise valid descendant.

Check your understanding

1. A depot lookup is expected to return one label but now has two historical versions. Should MIN(label) silently fix it?

Show worked solution

No. Select the valid revision using explicit temporal or status rules, then establish uniqueness for that lookup. MIN merely selects lexicographic order and can hide the ambiguous data model. If history is intentionally plural, return a collection rather than pretending the relationship is scalar.

2. What does the root LEVEL equal, and which ordering preserves sibling structure?