LEFT, RIGHT & FULL OUTER JOIN

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

Why Outer Joins Exist

INNER JOIN only returns rows that have a match on both sides. But real data is never perfectly complete — customers who have never placed an order, products that have never been sold, employees not yet assigned to a department. If you use INNER JOIN on incomplete data, those rows vanish silently. Outer joins solve this by preserving unmatched rows from one or both tables, filling the missing side with NULL.

Oracle supports three outer join types. A LEFT JOIN keeps every row from the left table regardless of whether it has a match on the right. A RIGHT JOIN keeps every row from the right table. A FULL OUTER JOIN keeps all rows from both tables. In each case, columns from the side that had no match come back as NULL.

• LEFT JOIN — all rows from the left table; NULLs for unmatched right-table columns
• RIGHT JOIN — all rows from the right table; NULLs for unmatched left-table columns
• FULL OUTER JOIN — all rows from both tables; NULLs wherever a match was missing
• LEFT OUTER JOIN and LEFT JOIN are identical — the OUTER keyword is optional in Oracle
• The unmatched rows always show NULL for every column coming from the side that had no match

LEFT JOIN — Keep All Left Rows

LEFT JOIN is the most commonly used outer join. It starts with every row from the left table and brings in matching rows from the right. Where no match exists, the right-side columns come back as NULL. Use it whenever you need a complete list from one table with optional detail from another — all customers whether or not they have orders, all products whether or not they have been reviewed.

-- All customers and their order count -- including customers with no orders.
-- INNER JOIN would silently drop customers who have never ordered.
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name  AS customer_name,
    c.country,
    COUNT(o.order_id)                   AS total_orders,   -- 0 when no orders exist
    NVL(SUM(o.total_amount), 0)         AS total_spent     -- NVL converts NULL sum to 0
FROM   dataplexa_store.customers  c
LEFT JOIN dataplexa_store.orders  o ON c.customer_id = o.customer_id
GROUP  BY c.customer_id, c.first_name, c.last_name, c.country
ORDER  BY total_spent DESC NULLS LAST;

• Anna Kovac has never placed an order — LEFT JOIN preserves her row and returns NULL for all orders columns
• COUNT(o.order_id) returns 0 for Anna because COUNT skips NULLs — a clean way to count matches
• NVL(SUM(o.total_amount), 0) converts the NULL sum to 0 — without NVL Anna's total would display as NULL
• An INNER JOIN here would have silently dropped Anna entirely

Finding Unmatched Rows with LEFT JOIN

One of the most useful LEFT JOIN patterns is finding rows that have no match at all — customers who have never ordered, products never added to any order, employees with no assigned department. The technique is to LEFT JOIN and then filter WHERE right_table.id IS NULL. The rows that come back are exactly those that INNER JOIN would have dropped.

-- Which products have never appeared in any order?
-- LEFT JOIN keeps all products; WHERE filters to only those with no order_items match.
SELECT
    p.product_id,
    p.product_name,
    p.category,
    p.price
FROM   dataplexa_store.products     p
LEFT JOIN dataplexa_store.order_items oi ON p.product_id = oi.product_id
WHERE  oi.product_id IS NULL        -- NULL here means no match was found in order_items
ORDER  BY p.category, p.product_name;

• Only two products have never been ordered — this pattern surfaces them without a subquery
• WHERE oi.product_id IS NULL is the key — it keeps only the rows where LEFT JOIN found no match on the right side
• This is faster and cleaner than NOT IN (SELECT product_id FROM order_items), especially when the subquery might contain NULLs

RIGHT JOIN — Keep All Right Rows

RIGHT JOIN is the mirror of LEFT JOIN — it keeps every row from the right table and brings in matching rows from the left. In practice most developers rewrite RIGHT JOINs as LEFT JOINs by swapping the table order, which is easier to read. But RIGHT JOIN is useful when you want to keep the table structure of an existing query and just swap which side is preserved.

-- All departments and their employee count -- including departments with no employees yet.
-- departments is on the RIGHT so RIGHT JOIN preserves all departments.
SELECT
    d.department_name,
    d.location,
    COUNT(e.employee_id)    AS headcount,
    NVL(AVG(e.salary), 0)   AS avg_salary
FROM   dataplexa_hr.employees    e
RIGHT JOIN dataplexa_hr.departments d ON e.department_id = d.department_id
GROUP  BY d.department_name, d.location
ORDER  BY headcount DESC;

• Legal has no employees yet — RIGHT JOIN preserves it; COUNT returns 0 and NVL converts the NULL average to 0
• This exact result could be written as FROM dataplexa_hr.departments d LEFT JOIN dataplexa_hr.employees e ON ... — both are equivalent
• Prefer LEFT JOIN when you have a choice — it reads more naturally from left to right

FULL OUTER JOIN — Keep All Rows from Both Sides

FULL OUTER JOIN returns all rows from both tables. Where a match exists the columns from both sides are populated. Where there is no match the columns from the missing side are NULL. Use it when you need a complete picture from two tables that may each have rows the other does not — comparing two datasets, auditing mismatches, or merging partially overlapping data.

-- Compare budgeted headcount against actual headcount per department.
-- budget_plan may have departments not yet in the live system, and vice versa.
SELECT
    NVL(bp.department_name, d.department_name)  AS department,
    bp.budgeted_headcount,
    COUNT(e.employee_id)                         AS actual_headcount,
    COUNT(e.employee_id) - NVL(bp.budgeted_headcount, 0)  AS variance
FROM   dataplexa_hr.budget_plan    bp
FULL OUTER JOIN dataplexa_hr.departments  d  ON bp.department_name = d.department_name
LEFT JOIN       dataplexa_hr.employees    e  ON d.department_id    = e.department_id
GROUP  BY NVL(bp.department_name, d.department_name), bp.budgeted_headcount
ORDER  BY department;

• Legal exists in departments but not in budget_plan — FULL OUTER JOIN preserves it with NULL budgeted_headcount
• Operations exists in budget_plan but not in departments — it appears with NULL actual_headcount
• An INNER JOIN would have dropped both Legal and Operations entirely
• NVL(bp.department_name, d.department_name) coalesces the name from whichever side is not NULL

Choosing the Right Outer Join

The choice between LEFT, RIGHT, and FULL OUTER JOIN comes down to which rows you need to preserve. Ask: "which table must contribute every row to the result?" If it is the first table in the FROM clause, use LEFT JOIN. If it is the second, use RIGHT JOIN — or rewrite it as a LEFT JOIN by swapping the tables. If both tables must contribute every row regardless of matches, use FULL OUTER JOIN.

-- Three equivalent ways to write the same query.
-- All departments with their employee count, preserving departments with no employees.
-- Option 1: LEFT JOIN -- departments is first
SELECT d.department_name, COUNT(e.employee_id) AS headcount
FROM   dataplexa_hr.departments d
LEFT JOIN dataplexa_hr.employees e ON d.department_id = e.department_id
GROUP  BY d.department_name;
-- Option 2: RIGHT JOIN -- departments is second
SELECT d.department_name, COUNT(e.employee_id) AS headcount
FROM   dataplexa_hr.employees e
RIGHT JOIN dataplexa_hr.departments d ON e.department_id = d.department_id
GROUP  BY d.department_name;
-- Option 3: rewrite RIGHT as LEFT by swapping table order -- same result as Option 1
-- Use whichever reads most clearly -- LEFT JOIN is the convention

• Option 1 and Option 2 are logically identical — swap the tables and LEFT becomes RIGHT
• LEFT JOIN is the convention because it reads naturally: "start with this table, optionally bring in that one"
• FULL OUTER JOIN cannot be rewritten as LEFT or RIGHT — it is its own thing when you truly need all rows from both sides

Lesson Summary

Practice Questions

Quiz

Next up — Subqueries — How to nest a SELECT inside another query, when to use a subquery versus a JOIN, and the difference between correlated and non-correlated subqueries in Oracle.