JOINs — combining data across tables

Real data almost never lives in one table — orders reference customers, posts reference authors. JOIN is the single operation that combines rows from related tables into one result, and INNER vs. LEFT is the one decision that matters most.

Beginner

4 min read

Why data is split across tables in the first place

-- Instead of repeating customer info on every single order row...
orders: id | customer_name | customer_email | product | amount

-- ...split into two tables, linked by an id:
customers: id | name  | email
orders:    id | customer_id | product | amount

Repeating a customer's name and email on every one of their orders wastes space and creates a real correctness problem: if that customer's email changes, every single order row referencing them would need updating too, or the data quietly goes inconsistent. Splitting into separate tables, linked by a foreign key (orders.customer_id pointing at customers.id), means each fact is stored exactly once — this is the same normalization idea Django's ForeignKey field (from the models lesson) is built on, just at the raw SQL level instead of through an ORM.

INNER JOIN: only rows that match on both sides

SELECT orders.id, customers.name, orders.amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

id | name   | amount
---+--------+-------
1  | Ada    | 50
2  | Grace  | 75

INNER JOIN table_b ON condition combines rows from two tables where the condition matches — here, every order row is matched with the customer row whose id equals that order's customer_id. Critically, an order whose customer_id doesn't match any row in customers (a data integrity problem, or a customer that was deleted) is simply excluded from the result entirely — INNER JOIN only returns rows that have a match on both sides, silently dropping everything else.

LEFT JOIN: every row from the left table, matched or not

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id;

name   | amount
-------+-------
Ada    | 50
Grace  | 75
Alan   | NULL     <- Alan has no orders, but still appears, with NULL for order fields

LEFT JOIN keeps every row from the left table (customers here), whether or not it has a matching row in the right table — when there's no match, the right table's columns simply come back as NULL instead of the row being dropped. This is the right choice for "show me every customer, including ones with zero orders" — a question INNER JOIN structurally cannot answer, since it would silently exclude exactly the customers being asked about.

The decision that matters most: INNER vs. LEFT

This single question is the most common real mistake in SQL joins: using INNER JOIN when the actual question was "show me every X, including ones with no related Y" silently produces a result that's missing exactly the rows the question was asking about — customers with no orders vanish entirely, with no error, no warning, just a result set that's quietly wrong for the question actually being asked.

Joining more than two tables

SELECT orders.id, customers.name, products.title
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN products ON orders.product_id = products.id;

Joins chain naturally — each additional JOIN clause combines in one more related table, using its own ON condition. This is exactly how a real query pulling together "which customer ordered which product" from three separate, properly normalized tables actually gets written; there's no upper limit on how many tables a single query can join, though very long join chains are a common real-world signal that a schema or query might be worth simplifying.

What this looks like from Django's side

Order.objects.select_related("customer")

The select_related lesson from the Django domain covers exactly this: select_related("customer") tells Django to generate a SQL JOIN (specifically, an INNER JOIN by default) instead of issuing a separate query per order to fetch its customer — the N+1 problem that lesson covers is, underneath, exactly the difference between doing this JOIN in one SQL query versus never joining at all and paying for it with many extra round trips.