Data · SQL

How SQL joins work

Published

3 customers, one order each: how many rows does the join return?

From one order per customer to a join that repeats rows, in five steps:

  1. Match two rows by their id
  2. Spot the customer with no orders
  3. Keep the left table with LEFT JOIN
  4. Count one row per pair
  5. Practice

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
customers
idname
1Emma
2Noah
3Mia
orders
idcustomer_idamount
1011$40
1022$25
1033$60
result
nameamount
Emma$40
Noah$25
Mia$60
  1. Which order has Emma’s id? Tap it in the table.

    Emma has id 1. Look for that 1 in the customer_id column.

  2. Do the same for Noah, then for Mia: each order you find gives one row.

    rows

  1. SELECT name, amount
    FROM customers
    JOIN orders
      ON orders.customer_id = customers.id
    customers
    idname
    1Emma
    2Noah
    3Mia
    4Liam
    orders
    idcustomer_idamount
    1011$40
    1022$25
    1033$60
    1044$15

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
customers
idname
1Emma
2Noah
3Mia
orders
idcustomer_idamount
1011$40
1023$25
result
nameamount
Emma$40
Mia$25
  1. Which order has Noah’s id?

    Noah has id 2. Look for that 2 in the customer_id column.

  2. Do the same for Emma and for Mia. A customer with no orders gives no rows.

    rows

  1. SELECT name, amount
    FROM customers
    JOIN orders
      ON orders.customer_id = customers.id
    customers
    idname
    1Emma
    2Noah
    3Mia
    4Liam
    orders
    idcustomer_idamount
    1011$40
    1022$25
    1034$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
customers
idname
1Emma
2Noah
3Mia
orders
idcustomer_idamount
1011$40
1023$25
result
nameamount
Emma$40
NoahNULL
Mia$25
  1. The left table is customers. Every one of its rows comes out, with or without an order.

    rows

  2. What’s in the amount column on Noah’s row?

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    customers
    idname
    1Emma
    2Noah
    3Mia
    4Liam
    orders
    idcustomer_idamount
    1011$40
    1022$25
    1034$60

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
customers
idname
1Emma
2Noah
3Mia
orders
idcustomer_idamount
1011$40
1021$25
1032$60
1043$15
result
nameamount
Emma$40
Emma$25
Noah$60
Mia$15
  1. Which orders have Emma’s id?

    Emma has id 1. That 1 can appear more than once in the customer_id column.

  2. Emma comes out once per order. Noah and Mia have one order each.

    rows

  3. If you count the rows of this result to count the customers, who gets counted twice?

  1. SELECT name, amount
    FROM customers
    JOIN orders
      ON orders.customer_id = customers.id
    customers
    idname
    1Emma
    2Noah
    3Mia
    orders
    idcustomer_idamount
    1011$40
    1022$25
    1032$60
    1042$15
    1053$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.

  1. SELECT name, amount
    FROM customers
    JOIN orders
      ON orders.customer_id = customers.id
    customers
    idname
    1Emma
    2Noah
    3Mia
    4Liam
    orders
    idcustomer_idamount
    1011$40
    1023$25
    1034$60