MySQL LEFT JOIN

LEFT JOIN Key Features

LEFT JOIN keeps all left table data

Right columns = NULL if no match

WHERE right_table.id IS NULL

LEFT JOIN = LEFT OUTER JOIN

🔹 Basic LEFT JOIN

LEFT JOIN ensures all records from the left table appear in results, even if they have no matching records in the right table. This is perfect for finding customers who haven't placed orders, products without sales, or any scenario where you need to preserve all records from your primary table while showing related data where it exists.

-- Get all customers and their orders (if any)
SELECT 
    c.customer_id,
    c.name,
    c.email,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

🔹 Finding Records Without Matches

One powerful use of LEFT JOIN is finding records that don't have corresponding entries in the related table. By checking for NULL values in the right table's columns, you can identify orphaned records. This is extremely useful for data quality checks, finding inactive customers, or identifying products that have never been ordered.

-- Find customers who have never placed an order
SELECT 
    c.customer_id,
    c.name,
    c.email,
    c.registration_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

🔹 LEFT JOIN with COUNT

Combining LEFT JOIN with COUNT allows you to count related records for each row in the left table, including those with zero matches. This is perfect for generating reports showing how many orders each customer has placed, including customers with no orders. Using COUNT with the right table's primary key ensures accurate counts even when NULLs are present.

-- Count orders per customer (including customers with 0 orders)
SELECT 
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY order_count DESC;

🔹 Multiple LEFT JOINs

You can chain multiple LEFT JOINs to include data from several related tables while preserving all rows from the leftmost table. Each subsequent LEFT JOIN is evaluated independently, so a row from the first table will appear even if it has no matches in any of the joined tables. This creates comprehensive reports with optional related data from multiple sources.

-- Get customers with orders and payments (all optional)
SELECT 
    c.name AS customer,
    o.order_id,
    o.order_date,
    p.payment_date,
    p.amount AS payment_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN payments p ON o.order_id = p.order_id;