Data · SQL
How SQL joins work
3 customers, one order each: how many rows does the join return?
And if one of the three customers has two orders?
From one order per customer to a join that repeats rows, in five steps:
How does a SQL join connect two tables?
The ON line says which columns to compare. Here, the order whose customer_id is 7 lines up with the customer whose id is 7.
Let’s take three customers and their orders.
SELECT name, amount FROM customers JOIN orders ON orders.customer_id = customers.id
| id | name |
|---|---|
| 1 | Emma |
| 2 | Noah |
| 3 | Mia |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | $40 |
| 102 | 2 | $25 |
| 103 | 3 | $60 |
| name | amount |
|---|---|
| Emma | $40 |
| Noah | $25 |
| Mia | $60 |
Which order has Emma’s id? Tap it in the table.
Emma has
id1. Look for that 1 in thecustomer_idcolumn.
SELECT name, amount FROM customers JOIN orders ON orders.customer_id = customers.id
customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 101 1 $40 102 2 $25 103 3 $60 104 4 $15 SELECT name, amount FROM customers JOIN orders ON orders.customer_id = customers.id
customers id name 1 Emma 2 Noah 3 Mia 4 Liam 5 Ava orders id customer_id amount 104 4 $15 102 2 $25 101 1 $40 103 3 $60 105 5 $80
Why does a JOIN drop rows?
With JOIN, also written INNER JOIN, the customer whose id is 7 is left out if no order has a customer_id of 7.
Let’s take three customers, one of them with no orders.
SELECT name, amount FROM customers JOIN orders ON orders.customer_id = customers.id
| id | name |
|---|---|
| 1 | Emma |
| 2 | Noah |
| 3 | Mia |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | $40 |
| 102 | 3 | $25 |
| name | amount |
|---|---|
| Emma | $40 |
| Mia | $25 |
Which order has Noah’s id?
Noah has
id2. Look for that 2 in thecustomer_idcolumn.
SELECT name, amount FROM customers JOIN orders ON orders.customer_id = customers.id
customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 101 1 $40 102 2 $25 103 4 $60 SELECT name, amount FROM customers JOIN orders ON orders.customer_id = customers.id
customers id name 1 Emma 2 Noah 3 Mia 4 Liam 5 Ava orders id customer_id amount 102 3 $25 101 2 $40 103 5 $60
What is the difference between JOIN and LEFT JOIN?
LEFT JOIN keeps every row of the left table, the one written before it. The customer whose id is 7 comes out, whether or not an order has a customer_id of 7.
Let’s take the same tables, with LEFT JOIN.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id
| id | name |
|---|---|
| 1 | Emma |
| 2 | Noah |
| 3 | Mia |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | $40 |
| 102 | 3 | $25 |
| name | amount |
|---|---|
| Emma | $40 |
| Noah | NULL |
| Mia | $25 |
What’s in the
amountcolumn on Noah’s row?
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id
customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 101 1 $40 102 2 $25 103 4 $60 SELECT name, amount FROM orders LEFT JOIN customers ON orders.customer_id = customers.id
orders id customer_id amount 104 7 $15 102 4 $25 101 2 $40 103 5 $60 customers id name 1 Emma 2 Noah 3 Mia 4 Liam 5 Ava
Why does a SQL join create duplicate rows?
A customer with 5 orders comes out 5 times: the join writes one row per customer-order pair, not one row per customer.
Let’s take three customers, one of them with two orders.
SELECT name, amount FROM customers JOIN orders ON orders.customer_id = customers.id
| id | name |
|---|---|
| 1 | Emma |
| 2 | Noah |
| 3 | Mia |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | $40 |
| 102 | 1 | $25 |
| 103 | 2 | $60 |
| 104 | 3 | $15 |
| name | amount |
|---|---|
| Emma | $40 |
| Emma | $25 |
| Noah | $60 |
| Mia | $15 |
Which orders have Emma’s id?
Emma has
id1. That 1 can appear more than once in thecustomer_idcolumn.If you count the rows of this result to count the customers, who gets counted twice?
SELECT name, amount FROM customers JOIN orders ON orders.customer_id = customers.id
customers id name 1 Emma 2 Noah 3 Mia orders id customer_id amount 101 1 $40 102 2 $25 103 2 $60 104 2 $15 105 3 $80 SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id
customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 104 3 $15 102 1 $25 106 4 $35 101 1 $40 103 3 $60 105 3 $80
How do you read a SQL join quickly?
It goes faster if you first check which table is written before JOIN: go through its rows one at a time, and for each one, find the rows of the other table that carry the same number.
SELECT name, amount FROM customers JOIN orders ON orders.customer_id = customers.id
customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 101 1 $40 102 3 $25 103 4 $60 SELECT name, amount FROM customers JOIN orders ON orders.customer_id = customers.id
customers id name 1 Emma 2 Noah 3 Mia orders id customer_id amount 104 3 $15 102 1 $25 101 1 $40 103 2 $60 105 3 $80 SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id
customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 102 4 $25 101 1 $40 SELECT name, amount FROM orders LEFT JOIN customers ON orders.customer_id = customers.id
orders id customer_id amount 104 3 $15 102 1 $25 106 4 $35 101 1 $40 107 6 $50 103 1 $60 105 4 $80 customers id name 1 Emma 2 Noah 3 Mia 4 Liam