Joins and Subqueries

ANSI Joins in Oracle

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

SELECT c.name, o.total
FROM customers c
LEFT OUTER JOIN orders o ON o.customer_id = c.id;

Legacy (+) Outer Join Syntax

Older Oracle code uses (+) on the outer side of a join:

SELECT c.name, o.total
FROM customers c, orders o
WHERE o.customer_id(+) = c.id;

Prefer the modern ANSI syntax, but be ready to read legacy code that uses (+).

Subqueries

SELECT name FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders);

SELECT name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Correlated Subqueries

SELECT c.name,
       (SELECT MAX(o.total) FROM orders o WHERE o.customer_id = c.id) AS best_order
FROM customers c;

Key Points

• ANSI JOIN ... ON is the modern, recommended style.
• (+) marks the outer side in legacy Oracle joins.
• IN with a subquery or EXISTS both filter by a set of values.
• Correlated subqueries run per outer row.