Writing Oracle JOIN Queries and Subqueries

Many database questions require information from more than one row source. An order identifies a customer, an order item identifies a product, and an employee identifies a department. Oracle SQL can connect those related facts with a join, or it can nest one query inside another so that the result of a subquery helps answer the containing query's question.

The underlying relational concepts are not unique to Oracle AI Database 26ai. Oracle supports standard ANSI join syntax and the standard forms of nested and correlated subqueries, while also retaining legacy syntax and providing optimizer transformations that are important when reading execution plans. This module concentrates on the meaning of the SQL first. Physical execution is considered separately because written query order does not necessarily dictate execution order.

Oracle documentation normally uses the word subquery. Some older books and other database products use subselect for the same general idea of a nested SELECT. This module uses subquery consistently.

A join combines rows from two or more row sources according to a relationship expressed by a join condition. Row sources can include tables, views, materialized views, and subqueries in the FROM clause. A single join operation has two inputs, but one statement can join many row sources. Oracle's optimizer can choose the physical order in which it joins them.

Modern SQL places the relationship in a JOIN ... ON clause. The following inner join connects each order with the customer whose primary key matches the order's foreign key:

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;

An inner join returns only combinations for which the ON condition evaluates to true. If one customer has several orders, that customer's identifier and name appear in several result rows. This is not accidental duplication: it reflects the one-to-many relationship. A syntactically correct join can therefore return more rows than either source, and the query author must understand the expected cardinality.

An outer join preserves unmatched rows from the left side, the right side, or both sides and supplies nulls for columns from the missing row. Oracle also supports its proprietary (+) operator in a WHERE clause, but Oracle recommends ANSI LEFT, RIGHT, and FULL OUTER JOIN syntax for new SQL. Lesson 3 explains how to recognize the legacy form in existing applications.

If row sources are combined without the required relationship, the logical result can be a Cartesian product: every row from one source is paired with every row from the other. That is usually an error. Use an explicit CROSS JOIN when every possible pair is intentionally required.

A subquery is a SELECT statement nested inside another SQL statement. Each query portion is a query block with its own row sources, conditions, selected expressions, and aliases. Subqueries can provide values to WHERE or HAVING, act as row sources in FROM, return a scalar expression, or supply values in data-changing statements where the SQL grammar permits them.

Form	Defining feature	Typical use
Noncorrelated subquery	Does not reference a column from an outer query block	Build a set or calculate a value independently of the current outer row
Correlated subquery	References a column from an outer query block	Test or calculate something relative to each candidate outer row
Scalar subquery	Returns one column and at most one row	Supply one value where an expression is allowed
Inline view	Appears as a row source in the FROM clause	Expose an aggregated, filtered, ranked, or calculated result to an outer query

A scalar subquery has an important cardinality contract. If it returns no row, its value is null. If it returns more than one row, Oracle raises ORA-01427. A subquery used with IN, by contrast, supplies a set of values and is expected to return zero, one, or many rows.

Always qualify columns with table aliases when a nested statement could otherwise be ambiguous. Oracle resolves an unqualified name in the current query block before looking outward, so a misspelled or unintended reference can produce valid SQL with the wrong meaning rather than a helpful error.

The following noncorrelated subquery supplies customer identifiers for a membership test. The outer query returns customers who placed an order during 2025:

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'
       );

The typed, half-open date range includes every time value in 2025 without converting order_date to text. The subquery can return the same customer identifier several times, but IN is a membership condition rather than a join that multiplies outer rows. Each customer row is either in the qualifying set or it is not.

A single-column IN condition compares one expression with one subquery column. Oracle also supports row-value comparisons in which the number, order, and compatible datatypes of the expressions on the left correspond to the subquery columns. Lesson 2 develops those two forms.

EXISTS asks whether its subquery returns at least one row. The values in the subquery's select list do not determine the truth result, which is why the conventional select list contains the literal 1:

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'
       );

The condition o.customer_id = c.customer_id correlates the inner query block with the current customer from the outer block. Logically, the question is: “Does at least one qualifying order exist for this customer?” That meaning differs from asking the subquery to return a value for display or calculation.

The IN and EXISTS examples can return the same customers for this data requirement, but neither spelling is universally faster. Oracle can transform eligible subqueries into semijoins or otherwise produce similar plans. Choose syntax that expresses the rule clearly, then use representative data, statistics, and execution plans when performance must be evaluated.

Nulls become especially important with negative conditions. If a NOT IN subquery returns a null, the comparison can evaluate to unknown and prevent every candidate row from qualifying. A properly correlated NOT EXISTS expresses the absence of a matching row without that same set-membership issue. Lesson 5 examines existence tests in detail.

A join and a subquery can answer closely related questions, but a rewrite must preserve result cardinality and null behavior. Joining customers to orders returns one result row for every matching customer-order pair. If one customer has five orders, an ordinary inner join returns five rows for that customer. An IN or EXISTS condition that asks only whether an order exists returns the customer row once.

Adding DISTINCT to a join can hide repeated customer values, but it is not a universal substitute for a semijoin-style condition. It changes duplicate handling after the join and may also remove duplicates that have legitimate meaning in the selected data. Express an existence question with EXISTS when that is the rule instead of manufacturing rows and removing them afterward.

A scalar subquery represents another distinct contract. It supplies one value, so the data and predicates must guarantee at most one returned row. Replacing it with a join can multiply the containing row if the joined source is not unique. Conversely, replacing a join with a scalar subquery can raise ORA-01427 when several related rows exist. Constraints, keys, and the intended relationship determine whether a reformulation is valid.

Before converting between forms, state the required unit of the result: one row per customer, one row per order, one row per customer-order pair, or one calculated value for each outer row. That business-level cardinality is more important than whether one version appears shorter.

A subquery in the FROM clause acts as a row source and is commonly called an inline view. It can calculate an intermediate result that the containing query then filters or joins. This example produces one 2025 order total per customer before applying the minimum-total condition:

SELECT annual.customer_id,
       annual.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
       ) annual
WHERE  annual.order_total > 1000;

The alias annual names the derived row source and establishes the qualifier used by the outer query. The inner query's aggregation changes its cardinality to one row per customer. The outer query filters those derived rows without repeating the aggregate expression.

The nested layout helps a reader reason about query blocks, but it does not prove that Oracle runs the innermost text first. SQL describes the required result. The cost-based optimizer can reorder joins, select access paths, choose nested loops, hash joins, or sort-merge joins, and apply transformations while preserving the statement's semantics.

For example, Oracle may unnest a subquery into a join, transform an EXISTS condition into a semijoin, merge an inline view into its parent query block, or preserve an intermediate result when that produces a suitable plan. Whether a transformation is available and beneficial depends on the statement, constraints, indexes, statistics, estimated cardinalities, and database features in use.

Hints can influence optimization, but they are not a substitute for correct SQL, representative statistics, and plan analysis. This module teaches the logical structures needed to write and interpret queries. Detailed plan diagnosis and physical tuning belong to the SQL tuning workflow.

By the end of Module 5, you should be able to:

1. Use single-column and row-value forms of IN with subqueries.
2. Interpret ANSI outer joins and Oracle's legacy (+) syntax while preferring ANSI syntax for new SQL.
3. Distinguish noncorrelated, correlated, scalar, and inline-view subqueries.
4. Use EXISTS and NOT EXISTS when the rule concerns the presence or absence of a matching row.
5. Explain alias scope and logical query blocks without assuming a fixed physical execution sequence.
6. Compare join and subquery formulations by result semantics, null behavior, readability, and observed execution plans.