LEFT JOIN and Joining Many Tables

Introduction

An inner join only keeps matches. Often the missing matches are exactly what you want to find: customers who never ordered, products nobody bought. LEFT JOIN keeps every row from the left table, matched or not.

LEFT JOIN

Farid now appears, with NULL in the order columns. Where there was no match, the right side is filled with NULL.

"Left" means the table written before LEFT JOIN. Swap the order of the tables and you get a different result.

Find rows with no match

Because unmatched rows contain NULL, you can filter for them. This finds customers who have never ordered:

And this finds products that appear in no order at all (none, in our data, since all eight sold):

Try deleting WHERE ... to see the whole join first. Seeing the full join is a great way to understand what the filter is doing.

Counting with LEFT JOIN

Use COUNT(column) on the right side to count real matches, and zero for unmatched rows. COUNT(*) would wrongly count the lone NULL row as 1.

Joining three or more tables

You can chain joins. Each one adds a table. To list what was bought in each order we need orders, order_items and products:

Now we can calculate how much each order is worth, by multiplying inside the query and summing per order:

And one more join gets the customer's name, so we can total spending per customer, ignoring cancelled orders:

Try it yourself

Show each product category with the total number of units sold of products in that category (the sum of quantity). Join products and order_items.

Summary

• LEFT JOIN keeps every row of the left table and fills missing matches with NULL.
• LEFT JOIN ... WHERE right.id IS NULL finds rows with no match.
• Use COUNT(right_column) to count matches, not COUNT(*).
• Chain JOINs to combine many tables, and calculate with columns from each.