Oracle Joins and Subqueries: Module Conclusion

A database question often spans more than one row source. A customer row identifies a person, an order row records a transaction, and an order-item row identifies what was purchased. Oracle SQL can connect those facts with joins, test membership in results returned by subqueries, ask whether related rows exist, calculate values relative to an outer row, or expose a derived result as an inline view.

These forms overlap, but they are not interchangeable merely because two statements happen to return the same rows for one sample dataset. The correct form depends on the required result grain, the number of rows that each relationship can produce, which unmatched rows must be preserved, and how nulls should affect the answer. A rewrite that ignores any of those properties can be syntactically valid and still change the business meaning of the query.

This conclusion consolidates the Module 5 workflow for Oracle AI Database 26ai. Its central principle is to define the logical result first and evaluate physical performance separately. SQL describes the result the database must produce; Oracle's cost-based optimizer determines join order, access paths, join methods, and eligible query transformations.

Before choosing syntax, state what one result row represents. A report might require one row per customer, one row per order, one row per product, one row per customer-order pair, or one summarized row per customer. That unit is the result grain. It determines whether matching detail rows may legitimately multiply the result or whether the query should only test their presence.

Suppose one customer has five orders. An inner join between CUSTOMER and CUSTOMER_ORDER naturally returns five matched pairs. That is correct for an order-level report. It is not correct for a customer list that asks only whether each customer has placed an order. An EXISTS condition or an IN membership test can express the latter question without first producing five outer result rows.

Adding DISTINCT to a join can remove repeated projected values, but it does not retroactively change the meaning of the join. It can also hide an incorrect relationship or remove duplicates that carry legitimate meaning. Use keys and constraints to understand the source cardinality, then select a SQL form that matches the intended grain directly.

A join is the natural form when the result needs data from two or more related row sources. The JOIN ... ON syntax separates the relationship from filters that apply to the combined rows. Qualified aliases also make the origin of each column explicit.

SELECT c.customer_id,
       c.customer_name,
       o.order_id,
       o.order_date
FROM   customer c
JOIN   customer_order o
       ON o.customer_id = c.customer_id
WHERE  o.order_date >= DATE '2025-01-01'
AND    o.order_date <  DATE '2026-01-01';

The half-open date range includes every time value during 2025 and keeps the date column unmodified. Each returned row represents one qualifying customer-order combination. If a customer has several qualifying orders, the customer columns repeat because each pair is a distinct result row.

A missing or incomplete relationship can create a Cartesian product, pairing every row from one source with every row from another. Use an explicit CROSS JOIN when that result is intentional. Otherwise, confirm that the join condition connects the appropriate primary, unique, and foreign-key columns and that the observed row count agrees with the modeled relationship.

The IN condition asks whether an expression equals a member of a list or a set returned by a subquery. A one-expression left side requires a one-expression subquery. The subquery may return zero, one, or many rows, and duplicate non-null values do not change membership truth.

SELECT c.customer_id,
       c.customer_name
FROM   customer c
WHERE  c.customer_id IN (
         SELECT o.customer_id
         FROM   customer_order o
         WHERE  o.order_date >= DATE '2025-01-01'
         AND    o.order_date <  DATE '2026-01-01'
       );

This result has one row per qualifying customer because the outer query reads customer rows and uses the subquery only as a comparison set. The subquery can contain its own joins, filters, grouping, or calculations, but it must return the number of expressions expected by the left side.

Oracle also supports row-value membership. A condition such as (product_id, customer_id) IN (SELECT product_id, customer_id ...) compares complete tuples. Expression count, position, meaning, and compatible datatype must correspond. Two separate one-column membership conditions are not equivalent when both values must come from the same returned row.

Nulls require special care. SQL conditions can evaluate to true, false, or unknown. If a NOT IN subquery returns a null, the exclusion test can become unknown for every candidate and return no rows. A correlated NOT EXISTS is often the clearer form when the rule concerns the absence of a matching row, provided the correlation itself expresses the correct relationship.

An inner join returns matching combinations. An outer join additionally preserves unmatched rows from one or both inputs and supplies nulls for expressions belonging to the missing side. A left outer join preserves the left input, a right outer join preserves the right input, and a full outer join preserves unmatched rows from both.

SELECT c.customer_id,
       c.customer_name,
       o.order_id
FROM   customer c
LEFT JOIN customer_order o
       ON o.customer_id = c.customer_id
      AND o.order_date >= DATE '2025-01-01'
      AND o.order_date <  DATE '2026-01-01';

Every customer remains in this result. The date restrictions belong in the ON clause because they define which orders are eligible to match. Moving those optional-side restrictions to the WHERE clause would reject the null-extended rows and could make the result behave like an inner join for this purpose.

Oracle continues to recognize its proprietary (+) operator, which appears beside the optional or null-generated table's column in a WHERE condition. It is important when maintaining legacy applications, but ANSI LEFT, RIGHT, and FULL OUTER JOIN syntax is clearer and supports forms that the legacy notation cannot express. Convert old statements by preserving row counts, optional-side filters, and null behavior—not by moving punctuation mechanically.

When counting matched rows after an outer join, count a non-null key from the optional side rather than COUNT(*). The preserved row still exists even when no optional row matched, so COUNT(*) includes it. A non-null optional-side primary key distinguishes an actual match from a null-extended placeholder.

A correlated subquery contains an outer reference: an inner expression refers to an alias defined by a containing query block. Logically, that reference makes the inner question relative to the current outer row. Correlation describes name scope and meaning; it does not prove that Oracle physically reruns the written inner text once for every outer row.

SELECT e.employee_id,
       e.last_name,
       e.salary
FROM   employee e
WHERE  e.salary = (
         SELECT MAX(peer.salary)
         FROM   employee peer
         WHERE  peer.department_id = e.department_id
       );

The outer reference e.department_id connects the aggregate to the department of the current employee. The scalar aggregate returns one value for the comparison, and the equality condition returns every employee tied for the maximum salary in that department. Adding an arbitrary row limiter would hide ties and change the requirement.

Correlation does not automatically make a subquery scalar. A scalar subquery must return one column and at most one row. No row becomes a null scalar value; more than one row raises ORA-01427. If the data model permits several matching rows, enforce uniqueness, aggregate according to the rule, or choose a set or existence condition. Do not suppress a cardinality error by selecting an arbitrary row.

Qualify columns in every nested query block. Oracle resolves an unqualified name in the current block before looking outward. A misspelled or unintended reference can therefore produce valid SQL with the wrong correlation rather than a syntax error. Clear aliases expose exactly which values cross a query-block boundary.

EXISTS is true when its subquery returns at least one row. The expressions in the subquery select list do not supply values to the outer query, which is why SELECT 1 is a useful convention. The important part is the subquery's row-producing logic and, for a correlated test, the predicate that connects it to the outer row.

SELECT c.customer_id,
       c.customer_name
FROM   customer c
WHERE  EXISTS (
         SELECT 1
         FROM   customer_order o
         WHERE  o.customer_id = c.customer_id
         AND    o.order_date >= DATE '2025-01-01'
         AND    o.order_date <  DATE '2026-01-01'
       );

This query and the earlier positive IN example can express the same customer requirement. The IN form emphasizes membership in a returned set of identifiers. The EXISTS form emphasizes the presence of a related row. Neither spelling is universally faster, because Oracle may transform eligible statements into similar semijoin plans.

NOT EXISTS expresses an antijoin-style question: keep the outer row only when no correlated inner row satisfies all predicates. It is usually safer than NOT IN for absence tests involving nullable values because its truth depends on whether a matching row exists, not on a chain of null-sensitive inequality comparisons. The correlation predicate must still be complete; omitting it can turn a per-row question into a global test against the entire inner table.

A subquery in the FROM clause is an inline view. Its select list defines the columns visible to the containing query, and its table alias names the derived row source. An inline view is useful when a query must filter, join, or present an intermediate grouped or calculated result.

SELECT sales_summary.customer_id,
       sales_summary.order_total
FROM   (
         SELECT o.customer_id,
                SUM(o.order_total) AS order_total
         FROM   customer_order o
         WHERE  o.order_date >= DATE '2025-01-01'
         AND    o.order_date <  DATE '2026-01-01'
         GROUP  BY o.customer_id
       ) sales_summary
WHERE  sales_summary.order_total > 1000;

The inner query changes the grain to one row per customer before the outer query applies its threshold. The alias order_total becomes an exposed inline-view column. Source columns not projected by the inner select list are outside the outer query block's scope.

An inline view is not necessarily a stored intermediate table. Oracle may keep its query block separate, merge it into the containing block, or push an eligible predicate into it. A WITH clause can name the same conceptual result more prominently, and HAVING may be simpler when the only requirement is to filter a grouped result within one query block. Choose the structure that communicates the requirement clearly.

Indentation and nesting help humans see query blocks, dependencies, and scope. They do not prescribe a fixed runtime sequence. Oracle's optimizer can reorder joins, select nested loops, hash joins, or sort-merge joins, unnest eligible subqueries, use semijoin or antijoin strategies, merge views, and push predicates when those transformations preserve the required result.

This is why rules such as “the inner query always runs first,” “joins are always faster,” or “a correlated subquery executes once per outer row” are unreliable. Two logically equivalent statements can produce the same plan, while two visually similar statements can have different cardinality, null behavior, or cost. The SQL text establishes correctness; current statistics, data distribution, indexes, bind values, and optimizer settings influence the physical plan.

Tune only after the result is correct. Use representative data, confirm estimated and actual row counts where suitable tooling is available, and inspect the execution plan for the statement in its real environment. Hints can influence plan selection, but they are not substitutes for a sound data model, accurate statistics, appropriate indexes, and correctly expressed relationships.

A reliable review asks more than whether the statement executes:

1. Define what one result row represents and identify the key or expression set that determines that grain.
2. State whether zero, one, or many matching inner rows are permitted for each relationship.
3. Decide whether unmatched rows must be removed, preserved from one side, or preserved from both sides.
4. Trace every nullable expression through IN, NOT IN, outer joins, equality tests, and aggregate calculations.
5. Qualify columns and verify that every correlation reference resolves to the intended query block.
6. Place relationship predicates and optional-side restrictions where they preserve the intended outer-join semantics.
7. Test data containing zero, one, and several matches, duplicate values, null keys, tied maxima, and shared descriptive names.
8. Add an outermost ORDER BY when presentation order is part of the requirement.
9. Compare alternate formulations by result semantics first and by measured execution behavior second.

Small sample data often hides the most consequential defects. A scalar subquery appears safe when every parent has one child. A direct join seems to preserve customer grain when every customer has one order. A NOT IN test appears correct until the inner expression contains a null. Deliberate boundary cases turn these assumptions into testable contracts.

After completing this module, you should be able to:

• Choose between a join, membership condition, existence test, scalar subquery, and inline view according to the required result.
• Predict when a join preserves, removes, or multiplies rows based on keys and relationship cardinality.
• Use single-expression and row-value IN subqueries with corresponding expression counts, positions, and datatypes.
• Write ANSI outer joins and interpret Oracle's legacy (+) notation without changing preserved-row semantics.
• Identify outer references in correlated subqueries and enforce the cardinality contract of scalar subqueries.
• Use EXISTS and NOT EXISTS for presence and absence while accounting for null-sensitive alternatives.
• Expose a derived result through an inline view or WITH clause and distinguish that logical structure from materialization.
• Evaluate performance with current execution evidence instead of assuming that written nesting dictates physical execution.

The unifying skill is preserving meaning across row sources. Start with the business question, define the result grain, account for relationship cardinality and nulls, and then choose the SQL form that states those rules most clearly. Once the result is proven correct, Oracle AI Database 26ai can be evaluated and tuned through its actual execution plan rather than through folklore about joins and subqueries.