Joins

10.1 ANSI Join Syntax

-- INNER
SELECT e.last_name, d.department_name
FROM employees e JOIN departments d ON d.department_id = e.department_id;

-- USING and NATURAL
SELECT last_name, department_name FROM employees JOIN departments USING (department_id);
SELECT * FROM employees NATURAL JOIN departments;     -- avoid: joins on all same-named columns

-- OUTER
SELECT e.last_name, d.department_name FROM employees e LEFT  JOIN departments d ON d.department_id = e.department_id;
SELECT e.last_name, d.department_name FROM employees e RIGHT JOIN departments d ON d.department_id = e.department_id;
SELECT e.last_name, d.department_name FROM employees e FULL  JOIN departments d ON d.department_id = e.department_id;

-- CROSS
SELECT * FROM sizes CROSS JOIN colors;

-- SELF
SELECT e.last_name employee, m.last_name manager
FROM employees e LEFT JOIN employees m ON m.employee_id = e.manager_id;

-- Non-equi
SELECT e.last_name, e.salary, j.grade_level
FROM employees e JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

-- Anti-join
SELECT d.* FROM departments d LEFT JOIN employees e ON e.department_id = d.department_id WHERE e.employee_id IS NULL;

10.2 Oracle Legacy Join Syntax

You'll see (+) in legacy code. Prefer ANSI syntax. The (+) operator can't express a FULL OUTER JOIN.

SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);    -- (+) marks the optional side: LEFT OUTER JOIN

10.3 Lateral Inline Views, CROSS APPLY and OUTER APPLY (12c)

SELECT c.customer_id, o.order_id, o.order_date
FROM customers c
CROSS APPLY (SELECT order_id, order_date FROM orders o
             WHERE o.customer_id = c.customer_id
             ORDER BY order_date DESC FETCH FIRST 3 ROWS ONLY) o;

SELECT d.department_name, x.cnt
FROM departments d,
     LATERAL (SELECT COUNT(*) cnt FROM employees e WHERE e.department_id = d.department_id) x;

10.4 Physical Join Methods (Preview of Module 48)

Nested loops, hash join, sort-merge join, Cartesian join, plus semi-joins and anti-joins. Adaptive plans can switch between NL and hash at runtime.