JOINs
18 min
Real schemas split data across multiple tables — a JOIN combines rows
from two tables based on a matching column, usually a foreign key.
SELECT employees.name, departments.name AS department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;
JOIN (equivalent to INNER JOIN) only keeps rows where the ON
condition matches on BOTH sides — an employee with no matching department
row simply doesn't appear in the result at all.
Prefixing columns with the table name (employees.name vs departments.name) avoids ambiguity whenever both tables have a column with the same name.
LEFT JOIN keeps every row from the table on the left, even when nothing
matches on the right — unmatched columns from the right table come back
as NULL instead of the row disappearing.
SELECT employees.name, departments.name AS department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
-- an employee with department_id = NULL still appears, with
-- department_name = NULL, instead of being dropped
Reach for LEFT JOIN any time "employees with no department" (or orders
with no shipment, users with no posts, etc.) is itself part of what you
want to see — INNER JOIN would silently hide exactly those rows.
Two tables: `departments(id, name)` and `employees(id, name, department_id)`. Write a query using an INNER JOIN that returns each employee's `name` alongside their department's name as `department_name`, ordered by employee name.
Same two tables — but one employee has no department (`department_id` is NULL). Write a query using a LEFT JOIN that returns EVERY employee's `name` alongside their department's name as `department_name` (NULL if they have none), ordered by employee name.
What's the key difference between INNER JOIN and LEFT JOIN?
You can join two tables on a foreign key, and choose between INNER JOIN (matches only) and LEFT JOIN (keep everything from the left side) based on whether unmatched rows should still appear.