Cheatsheet

SQL Joins Cheatsheet

INNER, LEFT, RIGHT, FULL, and CROSS, with what each one keeps.

The skeleton

Join two tables

select o.order_id, c.first_name
from   orders   as o
join   customers as c
  on   c.customer_id = o.customer_id

What each join keeps

Join	Keeps
`[INNER] JOIN`	Only rows that match on both sides.
`LEFT JOIN`	All left rows; unmatched right columns are `NULL`.
`RIGHT JOIN`	All right rows; unmatched left columns are `NULL`.
`FULL JOIN`	All rows from both sides; non-matches padded with `NULL`.
`CROSS JOIN`	Every left row paired with every right row (no `ON`).

Common patterns

Anti-join: rows with no match

select c.customer_id
from   customers as c
left join orders as o
  on   o.customer_id = c.customer_id
where  o.order_id is null   -- customers who never ordered

Three or more tables

from   orders      as o
join   order_items as oi on oi.order_id   = o.order_id
join   products    as p  on p.product_id  = oi.product_id

Quick guidance

You want…	Use
Only matching rows	`INNER JOIN`
Keep all of table A	`LEFT JOIN` (put A on the left)
Find the non-matches	`LEFT JOIN … WHERE b.id IS NULL`
Compare a table to itself	Self-join with two aliases

Watch out

Joining a one-to-many table (like order_items) repeats each left row once per match, so count(*) and sum() can inflate. Count the thing you mean with count(distinct o.order_id).

Lesson

Joining Customers to Orders in SQL

Join two related tables on a key.

Lesson

Joining Orders to Products in SQL

Join through a bridge table.

Lesson

INNER JOIN vs LEFT JOIN in SQL

Choose whether unmatched rows stay or drop.

Lesson

SQL Self-Joins

Join a table to itself to compare its own rows.