Quick reference · SQL joins

Join rows by what they share

A join combines rows from two tables using a match rule. Start with the question: should unmatched rows appear?

SELECT c.name, o.id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;
JoinRows keptUnmatched side
INNER JOINOnly pairs that matchDropped from both sides
LEFT JOINAll left rows, plus matchesRight columns become NULL
RIGHT JOINAll right rows, plus matchesLeft columns become NULL
FULL OUTER JOINAll rows from both sidesMissing side becomes NULL
CROSS JOINEvery possible pairNo matching condition

Three checks that prevent common mistakes

  1. Ask what one output row represents. A row can repeat when one row matches several rows.
  2. For a left join, put right-table match restrictions in ON when unmatched left rows must remain. A right-table filter in WHERE removes the NULL rows.
  3. Select named columns and qualify them (c.id) when names may overlap.

USING (id) is concise when both tables share that key name. Avoid NATURAL JOIN in routine queries: a later column-name overlap can silently change the match rule.

For the full walkthrough, see Lesson 1: SQL joins refresher.