LEFT JOIN in SQL

A left join keeps every left row, filling the right side with NULL when nothing matches.

SELECT
    c.id,
    c.name,
    COALESCE(SUM(o.total), 0) AS spent
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id
   AND o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY spent DESC;

How it works

1. Use it to find rows that have no partner at all.
2. IS NULL on a right-hand column isolates the unmatched rows.
3. COALESCE turns a missing number into a zero.

Keywords and builtins used here

• AND
• AS
• BY
• COALESCE
• DESC
• FROM
• GROUP
• JOIN
• LEFT
• ON
• ORDER
• SELECT
• SUM
• c

The run, in numbers

Lines

10

Characters to type

186

Tokens

59

Three-star pace

85 tpm

At the three-star pace of 85 tokens a minute, this run takes about 42 seconds.

Type this snippet

Step 2 of 9 in Joins, step 10 of 22 in Joins & aggregation.

← Previous Next →