Skip to content
BENIBIX Learn

Left Join

Development🗃️ DatabasesRelational Databases

Also known as LEFT JOIN, Left Outer Join

A join that keeps each row from the first table, with a match or not.

Think of it like

Think of it like a class photo. All the kids are in it, even if their mum did not come.

Example

Lucy wants to email people who have never ordered. A left join shows all customers, and the empty totals are her list.

Why it matters

Finding what is missing is a common need. A left join is how you see it.

Definition

A left join returns every row from the first table, plus any matches from the second. Where there is no match, the second table's columns come back as NULL. So a customer with no orders still appears, with an empty total. Use it when you want to see what is missing as well as what is there. It is also called a left outer join.

Example code

sql
SELECT customers.name, orders.total
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id;
-- Emma | 25.0
-- Emma | 40.0
-- Jack | 15.5
-- Lucy | NULL
Lucy stays in the result, with NULL where an order would be.

Common mistake

Filtering the second table in WHERE. That throws away the NULL rows and turns it back into an inner join.

Key takeaways

  1. 1
    Keep the left side. Every row from the first table.
  2. 2
    NULL for no match. Empty columns show the gap.
  3. 3
    Find what is missing. Great for gaps in data.

Compare