LEFT JOIN

An INNER JOIN answers "which rows pair up". A LEFT JOIN answers "everything on the left, plus its partner if it has one". Every row of the left table survives; where the ON condition finds no matching right-hand row, MySQL generates one anyway with every right-hand column set to NULL. That generated row is the whole point — it is how a customer with no orders still appears in a customer report.

Shorthand and longhand

LEFT JOIN and LEFT OUTER JOIN are the same statement; OUTER is optional and carries no meaning. USING (customer_id) again abbreviates ON a.customer_id = b.customer_id, but note that with an outer join USING gives you the coalesced column — COALESCE(a.customer_id, b.customer_id) — so the join column is never NULL even when the right side is missing.

Reading the NULLs

NULL in a right-hand column means one of two things: the row did not match, or it matched and that column was genuinely NULL. Test a column declared NOT NULL — usually the right table's primary or foreign key — when you want to distinguish them. Wrap display values in COALESCE(o.total_cents, 0) so the report shows a zero rather than a blank.

What trips people up

Aggregates count the generated rows. COUNT(*) returns 1 for a customer with no orders because there is one NULL-filled row; COUNT(o.id) returns 0, because COUNT over an expression skips NULLs.

-- Every customer, with their orders where they have any.
-- A customer who has never ordered still gets one row, with NULL
-- in every o.* column.
SELECT  c.id,
        c.email,
        o.id          AS order_id,
        o.placed_at,
        o.total_cents
FROM    customers AS c
LEFT JOIN orders AS o
        ON o.customer_id = c.id
ORDER BY c.id, o.placed_at;

-- LEFT OUTER JOIN is the identical statement; OUTER is optional.
-- SELECT ... FROM customers AS c LEFT OUTER JOIN orders AS o ON ...;

SELECT  c.id,
        c.email,
        COUNT(*)                       AS wrong_count,   -- never 0
        COUNT(o.id)                    AS order_count,   -- 0 when unmatched
        COALESCE(SUM(o.total_cents),0) AS revenue_cents  -- SUM of nothing is NULL
FROM    customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.email
ORDER BY order_count;

-- Wanted: every customer, plus their PAID orders only.
-- The status test belongs in ON. In WHERE it would delete every
-- customer who has no paid order (see the ON versus WHERE lesson).
SELECT  c.email,
        COUNT(o.id) AS paid_orders
FROM    customers AS c
LEFT JOIN orders AS o
        ON  o.customer_id = c.id
        AND o.status      = 'paid'    -- part of the match, not a post-filter
GROUP BY c.id, c.email;

-- orders -> shipments -> carriers, where an order may not be shipped
-- and a shipment may not yet have a carrier assigned.
SELECT  o.id AS order_id,
        s.tracking_no,
        COALESCE(ca.name, 'not dispatched') AS carrier
FROM    orders AS o
LEFT JOIN shipments AS s  ON s.order_id   = o.id
LEFT JOIN carriers  AS ca ON ca.id        = s.carrier_id
ORDER BY o.id;