Most JOIN confusion comes down to one question: what happens to rows with no match? Here's how INNER and LEFT JOIN answer it, and how to choose.
Adapted from the SQL Essentials Companion Guide .
Every explanation of SQL joins seems to reach for the same overlapping-circles diagram, and many people still come away unsure which join to use. It helps to drop the diagram and ask one plain question: what happens to a row that has no match in the other table?
That single question is the whole difference between INNER JOIN and LEFT JOIN . Everything else about them is the same.
A tiny example
Take two small tables. customers holds three people, and orders holds the purchases they made:
customers orders id | name id | customer_id | amount ---+----- ---+-------------+------- 1 | Ada 10 | 1 | 50 2 | Ben 11 | 1 | 30 3 | Cy 12 | 2 | 20 13 | 99 | 70
Enter fullscreen mode
Exit fullscreen mode
Ada has two orders, Ben has one, and Cy has none. Order 13 points at customer 99, who doesn't exist.…