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)
Keep all of A, but filter B	Put the filter in `ON`, not `WHERE` (why)
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-side row once per match. That can inflate counts, or sums of values that came from the repeated side, if you intended one contribution per original row. Count the entity you mean with a key such as count(distinct o.order_id), and check the grain before summing.

Lesson

Joining Customers to Orders in SQL

Join two related tables on a key.

Reference

SQL JOIN Types

INNER, LEFT, RIGHT, FULL, and CROSS joins.

Interview

SQL JOIN Interview Questions

Practice join keys, left joins, duplicate rows, and debugging.

Mistake

COUNT or SUM Too High After a JOIN

One-to-many joins can inflate totals.