Lesson 9: LEFT JOIN and Outer Joins

In Lesson 8, we connected two tables with INNER JOIN. However, INNER JOIN only returns rows where data exists on both sides. What if you need to find 'customers with no orders' or 'products with no reviews'? That's where LEFT JOIN comes in.

LEFT JOIN returns all rows from the left table and brings matching rows from the right table. If there is no match, the right-side columns are filled with NULL. This is an essential technique for finding rows without related records and is used very frequently in practice.

LEFT JOIN keeps all rows from the left table. If there's no match on the right, the values are filled with NULL.

Basic LEFT JOIN

-- Query all products regardless of whether they have reviews
SELECT
    p.name          AS product_name,
    p.price,
    r.rating,
    r.created_at    AS reviewed_at
FROM products AS p
LEFT JOIN reviews AS r ON p.id = r.product_id
ORDER BY p.name
LIMIT 8;

Result:

Products like ASUS TUF Gaming Laptop and Belkin USB-C Hub have no reviews, so rating and reviewed_at are NULL.

Finding Unmatched Rows

Anti-join pattern: add WHERE right_table.id IS NULL after a LEFT JOIN. This finds rows in the left table that have no corresponding row in the right table.

-- Products that have never received a review
SELECT
    p.id,
    p.name,
    p.price
FROM products AS p
LEFT JOIN reviews AS r ON p.id = r.product_id
WHERE r.id IS NULL
ORDER BY p.name;

Result:

-- Customers who have never placed an order
SELECT
    c.id,
    c.name,
    c.email,
    c.created_at
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
WHERE o.id IS NULL
ORDER BY c.created_at DESC
LIMIT 10;

Result:

These customers likely signed up recently and haven't made a purchase yet.

LEFT JOIN with Aggregation

To count only matched rows, use COUNT(right_table.id) instead of COUNT(*) -- NULLs are not included in the count.

-- Review count and average rating for all products
SELECT
    p.name          AS product_name,
    p.price,
    COUNT(r.id)     AS review_count,
    ROUND(AVG(r.rating), 2) AS avg_rating
FROM products AS p
LEFT JOIN reviews AS r ON p.id = r.product_id
WHERE p.is_active = 1
GROUP BY p.id, p.name, p.price
ORDER BY review_count DESC
LIMIT 10;

Result:

-- Per-customer order statistics including customers with 0 orders
SELECT
    c.name,
    c.grade,
    COUNT(o.id)         AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS lifetime_value
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
    AND o.status NOT IN ('cancelled', 'returned')
GROUP BY c.id, c.name, c.grade
ORDER BY lifetime_value DESC
LIMIT 8;

Notice that the additional AND condition is placed in the ON clause rather than WHERE. Placing it in WHERE would exclude customers with no orders from the result.

Result:

Chaining Multiple LEFT JOINs

-- Query orders with optional shipping and payment info
SELECT
    o.order_number,
    o.status,
    o.total_amount,
    s.carrier,
    s.tracking_number,
    p.method         AS payment_method
FROM orders AS o
LEFT JOIN shipping AS s ON s.order_id = o.id
LEFT JOIN payments AS p ON p.order_id = o.id
WHERE o.ordered_at LIKE '2024-12%'
LIMIT 5;

RIGHT JOIN

RIGHT JOIN is the opposite of LEFT JOIN. It keeps all rows from the right table and fills with NULL when there's no match on the left.

-- RIGHT JOIN: Include customers with no orders
SELECT
    c.name,
    c.email,
    o.order_number,
    o.total_amount
FROM orders AS o
RIGHT JOIN customers AS c ON c.id = o.customer_id
ORDER BY c.name
LIMIT 10;

In practice, RIGHT JOIN is rarely used. You can get the same result by swapping the table order and using LEFT JOIN:

-- Same result with LEFT JOIN
SELECT
    c.name,
    c.email,
    o.order_number,
    o.total_amount
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
ORDER BY c.name
LIMIT 10;

Both queries return the same result. LEFT JOIN is more intuitive, so most teams prefer it.

FULL OUTER JOIN

FULL OUTER JOIN keeps all rows from both tables. If there's no match on either side, the values are filled with NULL. This is useful when you need to simultaneously find customers without orders and orders without customer information.

Support for FULL OUTER JOIN varies by database:

-- SQLite 3.39+ : Use FULL OUTER JOIN directly
SELECT
    c.name,
    c.email,
    o.order_number,
    o.total_amount
FROM customers AS c
FULL OUTER JOIN orders AS o ON c.id = o.customer_id
ORDER BY c.name
LIMIT 15;

-- SQLite 3.38 and below: LEFT JOIN UNION ALL
SELECT
    c.name,
    c.email,
    o.order_number,
    o.total_amount
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id

UNION ALL

SELECT
    NULL    AS name,
    NULL    AS email,
    o.order_number,
    o.total_amount
FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL
ORDER BY name
LIMIT 15;

-- MySQL: LEFT JOIN UNION RIGHT JOIN
SELECT
    c.name,
    c.email,
    o.order_number,
    o.total_amount
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id

UNION

SELECT
    c.name,
    c.email,
    o.order_number,
    o.total_amount
FROM customers AS c
RIGHT JOIN orders AS o ON c.id = o.customer_id
ORDER BY name
LIMIT 15;

-- PostgreSQL: FULL OUTER JOIN supported directly
SELECT
    c.name,
    c.email,
    o.order_number,
    o.total_amount
FROM customers AS c
FULL OUTER JOIN orders AS o ON c.id = o.customer_id
ORDER BY c.name
LIMIT 15;

Summary

Practice Problems

Problem 1

Include all customers without orders and orders without customer information. Query customer_name, order_number, total_amount. Display '(unknown)' for missing customers and '(no order)' for missing orders. Sort by customer_name ascending and return up to 15 rows.

-- SQLite 3.39+
SELECT
    COALESCE(c.name, '(unknown)')       AS customer_name,
    COALESCE(o.order_number, '(no order)') AS order_number,
    o.total_amount
FROM customers AS c
FULL OUTER JOIN orders AS o ON c.id = o.customer_id
ORDER BY customer_name
LIMIT 15;

SELECT
    COALESCE(c.name, '(unknown)')       AS customer_name,
    COALESCE(o.order_number, '(no order)') AS order_number,
    o.total_amount
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id

UNION

SELECT
    COALESCE(c.name, '(unknown)')       AS customer_name,
    COALESCE(o.order_number, '(no order)') AS order_number,
    o.total_amount
FROM customers AS c
RIGHT JOIN orders AS o ON c.id = o.customer_id
ORDER BY customer_name
LIMIT 15;

SELECT
    COALESCE(c.name, '(unknown)')       AS customer_name,
    COALESCE(o.order_number, '(no order)') AS order_number,
    o.total_amount
FROM customers AS c
FULL OUTER JOIN orders AS o ON c.id = o.customer_id
ORDER BY customer_name
LIMIT 15;

Problem 2

Find the number of customers who have never left a review. Return a single value named no_review_customers.

SELECT COUNT(*) AS no_review_customers
FROM customers AS c
LEFT JOIN reviews AS r ON c.id = r.customer_id
WHERE r.id IS NULL;

Problem 3

Find all active products that have no inventory transactions in the inventory_transactions table. Return product_id, name, stock_qty.

SELECT
    p.id        AS product_id,
    p.name,
    p.stock_qty
FROM products AS p
LEFT JOIN inventory_transactions AS it ON p.id = it.product_id
WHERE p.is_active = 1
  AND it.id IS NULL
ORDER BY p.name;

Problem 4

For all categories, find the category name and the number of products (product_count) in each category. Include categories with zero products and display 0 for them. Sort by product_count descending, then by category name ascending.

SELECT
    cat.name        AS category_name,
    COUNT(p.id)     AS product_count
FROM categories AS cat
LEFT JOIN products AS p ON cat.id = p.category_id
GROUP BY cat.id, cat.name
ORDER BY product_count DESC, category_name ASC;

Problem 5

Using a RIGHT JOIN with the orders table as the left side, find all customers' names (name) and order counts (order_count). Include customers with no orders, and sort by order count descending, limited to 10 rows.

SELECT
    c.name,
    COUNT(o.id) AS order_count
FROM orders AS o
RIGHT JOIN customers AS c ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY order_count DESC
LIMIT 10;

Problem 6

Find the number of active products (product_count) and total stock (total_stock) per supplier. Include suppliers with no products and display 0 for those values. Sort by total_stock descending.

SELECT
    sup.company_name,
    COUNT(p.id)                     AS product_count,
    COALESCE(SUM(p.stock_qty), 0)   AS total_stock
FROM suppliers AS sup
LEFT JOIN products AS p ON sup.id = p.supplier_id
    AND p.is_active = 1
GROUP BY sup.id, sup.company_name
ORDER BY total_stock DESC;

Problem 7

For all products, show the product name, price, total units sold (SUM(order_items.quantity)), and the number of orders the product appeared in. Include products never ordered and display 0 in that case. Sort by units sold descending, limited to 20 rows.

SELECT
    p.name              AS product_name,
    p.price,
    COALESCE(SUM(oi.quantity), 0)    AS units_sold,
    COUNT(DISTINCT oi.order_id)       AS order_appearances
FROM products AS p
LEFT JOIN order_items AS oi ON p.id = oi.product_id
GROUP BY p.id, p.name, p.price
ORDER BY units_sold DESC
LIMIT 20;

Problem 8

For all orders, show the order number, total amount, payment method (payments.method), and shipping carrier (shipping.carrier). Include orders without payment or shipping info, using COALESCE to display 'unpaid' and 'unshipped' respectively. Sort by total amount descending, limited to 10 rows.

SELECT
    o.order_number,
    o.total_amount,
    COALESCE(p.method, 'unpaid')   AS payment_method,
    COALESCE(s.carrier, 'unshipped')  AS carrier
FROM orders AS o
LEFT JOIN payments AS p ON o.id = p.order_id
LEFT JOIN shipping AS s ON o.id = s.order_id
ORDER BY o.total_amount DESC
LIMIT 10;

Problem 9

Query all customers' names, emails, and their most recent order status. Display 'no orders' for customers without orders. Use COALESCE, sort by customer name ascending, and return up to 15 rows.

SELECT
    c.name,
    c.email,
    COALESCE(o.status, 'no orders') AS last_order_status
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
    AND o.ordered_at = (
        SELECT MAX(o2.ordered_at)
        FROM orders AS o2
        WHERE o2.customer_id = c.id
    )
ORDER BY c.name
LIMIT 15;

Problem 10

Find all customers who added products to their wishlist but never placed an order. Return customer_name, email, wishlist_items (number of wishlist items), sorted by wishlist_items descending.

SELECT
    c.name  AS customer_name,
    c.email,
    COUNT(w.id) AS wishlist_items
FROM customers AS c
LEFT JOIN orders    AS o ON c.id = o.customer_id
INNER JOIN wishlists AS w ON c.id = w.customer_id
WHERE o.id IS NULL
GROUP BY c.id, c.name, c.email
ORDER BY wishlist_items DESC;

Scoring Guide

Problem Areas:

Next: Lesson 10: Subqueries