Interpret Oracle Outer JOIN Using (+) Syntax

An outer join^[1] returns matching rows from two tables, plus non-matching rows from one side of the join. Those non-matching rows appear with NULL values for the columns coming from the table that did not match.

In modern SQL, outer joins are expressed with ANSI join keywords such as LEFT OUTER JOIN and RIGHT OUTER JOIN. Oracle also supports a legacy syntax that uses the (+) operator in the WHERE clause. This lesson focuses on reading and interpreting Oracle’s legacy form, while also showing the ANSI equivalent so you can recognize both styles in real systems.

1. Left outer join: keeps every row from the left table; unmatched rows from the right table become NULL.
2. Right outer join: keeps every row from the right table; unmatched rows from the left table become NULL.
3. Inner join: returns only rows that match on both sides; unmatched rows are excluded.

Oracle documentation often refers to “outer join” conceptually, but in ANSI SQL the left/right keywords are explicit and easier to read. Oracle’s (+) syntax encodes left vs. right by the side where (+) appears.

In Oracle’s legacy syntax, the (+) marker is placed on the column(s) from the table that is optional (the side that may fail to match).

Rule of thumb:

• If table2.col(+) appears, then table2 is optional and the query behaves like a LEFT OUTER JOIN from table1 to table2.
• If table1.col(+) appears, then table1 is optional and the query behaves like a RIGHT OUTER JOIN from table1 to table2.

SELECT col1, col2, ...
FROM table1, table2
WHERE table1.key = table2.key(+)
  AND ...

Suppose you have:

• employees: employee attributes including department_id
• departments: department attributes including department_id and department_name

To list all employees (even those not assigned to a department), use a LEFT OUTER JOIN:

SELECT e.name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
  ON e.department_id = d.department_id;

If an employee has no department, department_name appears as NULL.

The following query returns all products, including products that have no rows in SALE_ITEM. Notice the (+) marker on the SALE_ITEM side of the predicate:

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

Inner joins return only matched rows. That is appropriate when a missing match should exclude the record. Outer joins are used when you want a “complete list” from one table and optional data from the other table.

For example, imagine:

• customers contains all customers.
• orders contains only recent orders.

An inner join lists only customers with recent orders. To include customers with no recent orders, use an outer join. In ANSI form that is a LEFT OUTER JOIN. In Oracle (+) form, the (+) marker goes on the orders side of the join predicate.

ANSI form:

SELECT c.lastname, c.firstname, o.order_id
FROM customers c
LEFT OUTER JOIN orders o
  ON c.customer_id = o.customer_id
ORDER BY c.lastname, c.firstname;

Legacy Oracle (+) form:

SELECT c.lastname, c.firstname, o.order_id
FROM customers c, orders o
WHERE c.customer_id = o.customer_id(+)
ORDER BY c.lastname, c.firstname;

Oracle’s (+) operator can be understood as: “if no match exists, Oracle supplies a placeholder row on the optional side with NULL values.” The placeholder row is not stored in any table; it exists only to form the result set.

Click the quiz link below to test your understanding of inner joins versus outer joins.
Inner Join Outer Join - Quiz

[1] Outer join: A join that returns matched rows plus non-matching rows from one side, filling the other side’s columns with NULL.

Left outer join: Keeps every row from the left table; unmatched rows from the right table appear with NULL values.

Right outer join: Keeps every row from the right table; unmatched rows from the left table appear with NULL values.

Inner join: Returns only rows that match on both sides of the join condition.