Oracle Outer Joins and the Legacy (+) Operator

An inner join returns only row combinations that satisfy its join condition. An outer join also preserves selected rows that have no match. Oracle supplies null values for expressions from the missing row so that the preserved row can remain in the result.

Oracle AI Database 26ai supports standard ANSI LEFT, RIGHT, and FULL OUTER JOIN syntax. It also recognizes Oracle's older (+) operator in a WHERE clause. Oracle recommends the ANSI form because it states the join directly in the FROM clause and avoids restrictions attached to (+). You still need to recognize the older form when maintaining legacy applications, reports, and views.

The preserved input contributes all of its qualifying rows, whether or not the other input matches. The optional input may fail to match. Oracle documentation also calls the optional input the null-generated table because its expressions become null in an unmatched result row. No row is inserted into either base table; null extension occurs only while forming the query result.

Join form	Rows preserved	Unmatched expressions
INNER JOIN	Only matching row combinations	Unmatched rows are excluded
LEFT OUTER JOIN	Every qualifying row from the left input	Right-side expressions become null when no match exists
RIGHT OUTER JOIN	Every qualifying row from the right input	Left-side expressions become null when no match exists
FULL OUTER JOIN	Unmatched rows from both inputs, plus all matches	Expressions from whichever side is missing become null

“Left” and “right” refer to the input positions in the SQL, not to a diagram or storage location. You can often reverse the inputs and exchange a right outer join for an equivalent left outer join. Choose the arrangement that makes the preserved business entity easiest to identify.

The Pet Store report must list every product, including products without a matching row in SALE_ITEM. Therefore, PRODUCT is the preserved input and SALE_ITEM is optional. The ANSI query expresses that relationship with a left outer join:

SELECT p.product_name,
       COALESCE(SUM(si.sale_amount), 0) AS dollars
FROM   product p
LEFT OUTER JOIN sale_item si
       ON si.product_id = p.product_id
GROUP  BY p.product_id,
          p.product_name
ORDER  BY p.product_name;

A product with sale items contributes its matching amounts to SUM. A product without a match still contributes one null-extended result row. Its si.sale_amount is null, so SUM returns null for that product and COALESCE displays zero. Grouping by the product identifier as well as the name prevents two distinct products with the same display name from being combined.

Product sales totals, including products with no sale items

PRODUCT_NAME	DOLLARS
Air Filter	0
Bird Food	3.5
Bird Treat	4.5
Box Turtle	15.5
Bunny Food	0
Canary	20
Cat	0
Cat Collar	0
Cat Food	0
Catnip	1.99
Chew Toy	13
Cockatiel	0
Dog Collar	5.65
Dog Food	10.3

The displayed sequence comes from ORDER BY p.product_name. An outer join controls which rows qualify; it does not guarantee their presentation order.

Preserving a product does not mean that the join always produces exactly one row for that product. The number of joined rows depends on the number of matches. If a product has three sale items, the join produces three product-sale-item combinations before grouping. If it has one sale item, the join produces one matching combination. If it has no sale item, the left outer join produces one null-extended combination so the product is not lost.

This distinction explains why an outer join can increase the number of rows as well as preserve otherwise missing rows. It also explains the aggregation step in the report. GROUP BY collapses all joined combinations for one product into one result group, and SUM calculates the total across its matching sale items. For the null-extended combination, no non-null amount exists, so null handling supplies the displayed zero.

When debugging, temporarily select the join key from each input before adding aggregation. The key values reveal whether a row matched, appeared more than once because of valid one-to-many data, or was null-extended because no related row existed. Restore the aggregate only after the join relationship produces the intended row combinations.

In legacy Oracle syntax, the tables are comma-separated and the join condition appears in WHERE. The (+) marker is attached to a column from the optional table. The unmarked table is preserved. This is the legacy equivalent of the preceding ANSI query:

SELECT p.product_name,
       NVL(SUM(si.sale_amount), 0) AS dollars
FROM   product p,
       sale_item si
WHERE  p.product_id = si.product_id(+)
GROUP  BY p.product_id,
          p.product_name
ORDER  BY p.product_name;

Read p.product_id = si.product_id(+) as: preserve PRODUCT rows and permit SALE_ITEM not to match. The marker does not mean “add rows to SALE_ITEM.” It identifies the input whose selected expressions may be null-generated in the result.

The use of NVL here is independent of the legacy join operator. Both NVL and COALESCE are null-handling expressions, and either join style can use an appropriate null-handling expression. The join determines row preservation; the function determines how a null aggregate is displayed.

The location of (+) determines the direct ANSI translation:

-- Legacy: SALE_ITEM is optional and PRODUCT is preserved.
FROM   product p,
       sale_item si
WHERE  p.product_id = si.product_id(+)

-- ANSI equivalent.
FROM   product p
LEFT OUTER JOIN sale_item si
       ON si.product_id = p.product_id

If the marker moves to the product column, PRODUCT becomes optional and SALE_ITEM is preserved:

-- Legacy: PRODUCT is optional.
FROM   product p,
       sale_item si
WHERE  p.product_id(+) = si.product_id

-- Direct ANSI translation with the original input order.
FROM   product p
RIGHT OUTER JOIN sale_item si
        ON si.product_id = p.product_id

The same relationship can often be written more naturally by reversing the inputs:

FROM   sale_item si
LEFT OUTER JOIN product p
       ON p.product_id = si.product_id

A full outer join preserves unmatched rows from both inputs. The legacy (+) notation has no direct single-query-block form for that requirement. Use ANSI syntax:

SELECT ...
FROM   table_a a
FULL OUTER JOIN table_b b
       ON b.key_value = a.key_value;

Filter placement is one of the most important outer-join details. Suppose the rule is to preserve every product but match only sale items with a positive amount. Put the optional-side condition in the ANSI ON clause:

SELECT p.product_name,
       si.sale_amount
FROM   product p
LEFT OUTER JOIN sale_item si
       ON si.product_id = p.product_id
      AND si.sale_amount > 0;

A product without a positive sale item remains in the result with null SALE_ITEM expressions. Moving the same condition to WHERE changes the meaning:

SELECT p.product_name,
       si.sale_amount
FROM   product p
LEFT OUTER JOIN sale_item si
       ON si.product_id = p.product_id
WHERE  si.sale_amount > 0;

For a null-extended row, NULL > 0 is not true, so the WHERE clause removes the preserved product. The query behaves like an inner join for this requirement. In the legacy form, each applicable optional-side condition must carry the marker:

SELECT p.product_name,
       si.sale_amount
FROM   product p,
       sale_item si
WHERE  p.product_id = si.product_id(+)
AND    si.sale_amount(+) > 0;

Do not convert a legacy query by moving predicates mechanically. First determine whether each condition defines a match or filters the completed joined result. Then place it in ON or WHERE to preserve the original business rule.

Aggregate functions can make an outer-join mistake less obvious. SUM(si.sale_amount) ignores individual nulls but returns null when a product group contains no non-null amount. Applying COALESCE or NVL to that aggregate displays zero without changing which rows the join preserves.

Counting requires another distinction. COUNT(*) counts the null-extended result row, so a product with no matching sale item can have a count of one. To count actual matches, count a non-null column from the optional table, such as COUNT(si.sales_id). It evaluates to zero when no sale-item row matched.

Treat a legacy outer-join conversion as a semantic change that requires verification, even when the final ANSI query looks straightforward. Use a deliberate sequence:

1. Identify every table alias and every condition that connects the same pair of tables.
2. Locate each (+) marker and name the optional table before rewriting any clause.
3. Choose the preserved input for the ANSI LEFT or RIGHT OUTER JOIN.
4. Move join predicates and optional-side match restrictions into ON.
5. Leave result-level filters in WHERE, recognizing that some filters intentionally remove null-extended rows.
6. Compare row counts, unmatched keys, duplicate patterns, null values, and aggregate totals between the old and new statements.

Test with data that includes all important cases: a preserved row with no match, one match, and several matches; null join keys; and optional rows that fail an additional match restriction. A test set containing only successful one-to-one matches cannot reveal whether outer-join semantics were preserved.

Do not mix a partial ANSI rewrite with remaining (+) predicates in the same query block. Convert the complete query block, qualify ambiguous columns, and then review each predicate according to its role. This produces SQL that is easier to read and gives reviewers a clear basis for comparing the original business rule with the modern statement.

Use an inner join when a missing related row should exclude the primary row. Use an outer join when the report must keep a complete set from one or both inputs. For example, an inner join between customers and recent orders lists only customers who ordered. A customer-to-orders left outer join can also list customers without a recent order.

Before writing the SQL, state the rule in business terms: “Preserve every product,” “preserve every sale item,” or “preserve unmatched rows from both.” That sentence identifies the left, right, or full form more reliably than memorizing the visual position of a plus sign.

• An outer join preserves unmatched rows and supplies nulls for expressions from the missing input.
• A left outer join preserves the left input; a right outer join preserves the right input; a full outer join preserves both.
• In legacy Oracle syntax, (+) marks the optional or null-generated table.
• Write new SQL with ANSI outer joins, but recognize (+) when maintaining older code.
• Keep optional-side match restrictions in ON when preserved rows must remain.
• Use a non-null optional-side column when counting actual matches.
• Validate row counts and null behavior whenever legacy outer joins are converted.

Test whether you can identify preserved rows, optional tables, and the correct join form.

Inner Join vs. Outer Join Quiz