Oracle Database – Joins

Joins in Oracle are the joins you already know. Two things are specific enough to be worth a post: the (+) outer-join operator you will meet in every codebase written before 2005, and the NOT IN null trap that returns zero rows without an error.

ANSI joins, which is what you should write

SELECT c.last_name, o.id, o.total
FROM   customers c
JOIN   orders    o ON o.customer_id = c.id;             -- INNER

SELECT c.last_name, o.id
FROM   customers c
LEFT   JOIN orders o ON o.customer_id = c.id;           -- keep customers with no orders

SELECT c.last_name, o.id
FROM   customers c
FULL   OUTER JOIN orders o ON o.customer_id = c.id;     -- unmatched from both sides

SELECT c.last_name, r.region_name
FROM   customers c CROSS JOIN regions r;                -- cartesian, on purpose

Oracle supports USING and NATURAL JOIN as well. Use USING sparingly and NATURAL JOIN never — it joins on every same-named column, so the day somebody adds a created_at to both tables the query silently changes meaning.

One USING quirk: the joined column becomes unqualified, and prefixing it is a syntax error.

SELECT customer_id           -- correct
FROM   orders JOIN customers USING (customer_id);

SELECT o.customer_id         -- ORA-25154: column part of USING clause cannot have qualifier
FROM   orders o JOIN customers c USING (customer_id);

The (+) operator

Before Oracle 9i there was no ANSI join syntax. Outer joins were written by marking the optional side of a predicate with (+), in the WHERE clause:

-- Legacy: LEFT JOIN orders. The (+) goes on the side that may be missing.
SELECT c.last_name, o.id
FROM   customers c, orders o
WHERE  o.customer_id(+) = c.id;

-- Equivalent, and what you should write instead
SELECT c.last_name, o.id
FROM   customers c
LEFT   JOIN orders o ON o.customer_id = c.id;

You need to be able to read it, and you need to know why not to write it:

• There is no full outer join. (+) on both sides is an error.
• Every predicate on the outer table needs its own (+). Miss one and the outer join collapses into an inner join, silently: WHERE o.customer_id(+) = c.id AND o.status = 'NEW' drops every customer without an order, because NULL = 'NEW' is not true. It has to be AND o.status(+) = 'NEW'.
• It cannot be combined with ANSI joins in the same FROM clause.

That second bullet is the entire reason ANSI syntax exists: it puts the join condition somewhere structurally distinct from the filter, so the two cannot be confused.

Semi-joins: does a match exist?

When you want rows from one table based on the existence of a match, but no columns from the other table, don't join — Oracle can stop at the first match instead of building the whole result.

-- Customers who have ordered
SELECT * FROM customers c
WHERE  EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Same thing with IN
SELECT * FROM customers
WHERE  id IN (SELECT customer_id FROM orders);

A plain JOIN here would duplicate a customer once per order and then need a DISTINCT to clean up — slower and easy to forget.

The optimiser treats EXISTS and IN as the same semi-join and usually produces an identical plan, so pick on readability. EXISTS is the safer habit because it never surprises you on the anti-join side, which is next.

The NOT IN trap

This is the one that costs an afternoon. These two queries look equivalent and are not:

-- If ANY orders.customer_id is NULL, this returns ZERO rows. Always.
SELECT * FROM customers
WHERE  id NOT IN (SELECT customer_id FROM orders);

-- Correct, and unaffected by NULLs
SELECT * FROM customers c
WHERE  NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

The reason is three-valued logic. x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. That last term is UNKNOWN, never TRUE, so the whole conjunction can never be true and no row qualifies. No error, no warning, just an empty result.

Use NOT EXISTS for anti-joins. It is null-safe by construction. If you must keep NOT IN, filter the subquery: WHERE customer_id IS NOT NULL. Interestingly, a NOT NULL constraint on the column also fixes it — the optimiser can then prove the null case away — which means the same query works in one environment and breaks in another where the constraint was never applied.

Self-joins

SELECT e.last_name AS employee, m.last_name AS manager
FROM   employees e
LEFT   JOIN employees m ON m.employee_id = e.manager_id;   -- LEFT so the CEO survives

For a whole hierarchy rather than one level, use CONNECT BY or a recursive CTE — see the previous post.

The correlated join a plain JOIN cannot do

Everything above joins on a condition. What none of it can do is let the right-hand side see the row it is being joined to — which is what you need for "the three most recent orders per customer", where the filter depends on the customer. From 12c, CROSS APPLY and OUTER APPLY allow exactly that:

SELECT c.last_name, o.id, o.order_date
FROM   customers c
CROSS  APPLY (SELECT * FROM orders o
              WHERE  o.customer_id = c.id      -- legal: c is in scope
              ORDER  BY o.order_date DESC
              FETCH  FIRST 3 ROWS ONLY) o;

That is a big enough subject to have its own post, which is the next one. The short version: CROSS APPLY behaves like an inner join and drops the outer row when the subquery is empty, OUTER APPLY behaves like a left join and keeps it.

How Oracle actually executes a join

Three algorithms, and recognising them in a plan tells you whether the plan is sensible.

EXPLAIN PLAN FOR
SELECT c.last_name, sum(o.total)
FROM   customers c JOIN orders o ON o.customer_id = c.id
GROUP  BY c.last_name;

SELECT * FROM dbms_xplan.display(format => 'BASIC +ROWS +COST');

The failure mode to look for: nested loops over a large row source. If the plan says NESTED LOOPS and the estimated row count on the outer side is 12 but the table holds five million rows, the optimiser's estimate is wrong — usually stale statistics or a predicate it cannot evaluate — and the query will take a hundred times longer than the plan suggests. That is the subject of the indexes and execution plans post.

Next

CROSS APPLY and OUTER APPLY in full: top-N per group, several aggregates in one pass, and how to tell from the plan whether it is helping or hurting.