SQL / JOINS
Joining more than two tables in one query
Chain four tables in one query, aim each ON at the right earlier table, and stop a later join from cancelling an earlier LEFT JOIN.
What you will learn
- Read a multi-join FROM left to right: each JOIN attaches to the result so far
- Aim each ON at whichever earlier table holds the key, not just the previous one
- Keep every join after a LEFT JOIN outer, or the rows it preserved disappear
- Alias every table reference so the same table can appear twice unambiguously
Understanding Joining more than two tables in one query
A FROM clause with several joins is read strictly left to right as a running result. FROM a JOIN b ON ... produces an intermediate table; the next JOIN c ON ... joins that whole intermediate table with c, and a fourth join works on the result of that. The practical consequence is that by the time you write the third ON, every column of every earlier table is already in scope, so the new table can be matched against any of them.
That is why a walk like customers to orders to order_items to products works one hop at a time: each ON only has to tie the new table to a key that already exists in the accumulated result, and products has no column that appears in orders. It also explains why written order matters for outer joins. LEFT JOIN preserves the rows of the accumulated left side at the step where it is written, and a plain JOIN placed after it compares against those NULL-extended keys, which is never true, so the preserved rows are filtered out again. Inner joins are commutative and associative, so the planner may execute them in any order it likes; outer joins are not, so their sequence is part of the query's meaning rather than a hint.
The result has one row per surviving combination, which means a chain of one-to-many hops multiplies rows and repeats the columns from the earliest tables. One customer with two orders of three items each yields six rows with the customer name printed six times. The multiplication turns into a real bug when two independent children hang off the same parent: join order_items and payments to orders and each item pairs with each payment, so SUM(qty) is counted once per payment. Add joins one at a time and watch the row count after each one, because that is the cheapest way to see a fan-out the moment it appears.
CREATE TABLE customers (customer_id INTEGER, customer_name TEXT);
CREATE TABLE orders (order_id INTEGER, customer_id INTEGER);
CREATE TABLE order_items(order_id INTEGER, product_id TEXT, qty INTEGER);
CREATE TABLE products (product_id TEXT, product_name TEXT, unit_price REAL);
INSERT INTO customers VALUES (1,'Ada'), (2,'Grace'), (3,'Linus');
INSERT INTO orders VALUES (101,1), (102,2);
INSERT INTO order_items VALUES (101,'P1',2), (101,'P2',1), (102,'P1',3);
INSERT INTO products VALUES ('P1','Keyboard',40.0), ('P2','Mouse',15.5);
SELECT c.customer_name,
p.product_name,
i.qty,
i.qty * p.unit_price AS line_total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
ORDER BY c.customer_name, p.product_name;Every join after the first one joins the new table to the accumulated result of everything to its left, not to the single table written beside it.
Worked examples
An inner join undoing an earlier LEFT JOIN
Shows that a plain JOIN written after a LEFT JOIN removes exactly the rows the LEFT JOIN kept.
CREATE TABLE authors(author_id INTEGER, name TEXT);
CREATE TABLE books (book_id INTEGER, author_id INTEGER, title TEXT);
CREATE TABLE sales (book_id INTEGER, copies INTEGER);
INSERT INTO authors VALUES (1,'Woolf'), (2,'Borges');
INSERT INTO books VALUES (10,1,'The Waves'), (11,1,'Orlando');
INSERT INTO sales VALUES (10,3);
SELECT a.name, b.title, s.copies
FROM authors a
LEFT JOIN books b ON b.author_id = a.author_id
JOIN sales s ON s.book_id = b.book_id
ORDER BY a.name, b.title;
SELECT a.name, b.title, s.copies
FROM authors a
LEFT JOIN books b ON b.author_id = a.author_id
LEFT JOIN sales s ON s.book_id = b.book_id
ORDER BY a.name, b.title;Example explained
Line 1The LEFT JOIN to books builds a row for Borges with b.book_id set to NULL.
Line 2In the first query s.book_id = b.book_id is then NULL-compared, never true, so Borges is discarded.
Line 3'Orlando' is lost the same way: it has no sales row and the third join is inner.
Line 4The two queries differ only in the third join's type, and that single word changes the count from 1 to 3.
Three table references, one table used twice
Demonstrates the third join reaching back to the first table in the chain, with the same table aliased twice.
CREATE TABLE employees (emp_id INTEGER, name TEXT, dept_id INTEGER, manager_id INTEGER);
CREATE TABLE departments(dept_id INTEGER, dept_name TEXT);
INSERT INTO employees VALUES (1,'Ada',10,NULL), (2,'Grace',10,1), (3,'Linus',20,1);
INSERT INTO departments VALUES (10,'Engineering'), (20,'Kernel');
SELECT e.name AS employee, d.dept_name, m.name AS manager
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
LEFT JOIN employees m ON m.emp_id = e.manager_id
ORDER BY e.emp_id;Example explained
Line 1m is a second, independent reference to employees; without the two aliases every column name would be ambiguous.
Line 2m.emp_id = e.manager_id reaches back to e, the first table, not to d which was joined immediately before.
Line 3Ada's manager_id is NULL and NULL = emp_id is never true, so only the LEFT JOIN keeps her row.
Line 4ORDER BY e.emp_id fixes the row order; a join's output order is otherwise unspecified.
Important notes
More than two tables means more than two table references: the same table joined twice needs two aliases, and then every column must be qualified.
Written join order is not execution order for inner joins, since the planner reorders them freely, but for outer joins the sequence is semantics and cannot be shuffled.
Common mistakes
Following a LEFT JOIN with a plain JOIN: the later ON compares against NULL keys, so the preserved rows vanish and the outer join quietly behaves as an inner one.
Joining products straight to orders because orders is the previous table, skipping order_items: there is no shared column, so you either get an unknown-column error or invent a condition that matches unrelated rows.
Hanging two one-to-many children such as order_items and payments off the same orders row and then using SUM(qty): each item is repeated once per payment and the total comes out too high.
Try it yourself
Change, predict, then run
Extend the four-table query with a shipments table holding only (101,'DHL'), joining it so all three original rows still appear with an empty carrier for order 102, then confirm the row count is still 3.
Open the SQL workspaceCheck your understanding
A query reads FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id JOIN shipments s ON s.order_id = o.order_id. Which rows does it return?
- Every customer, with NULL order and shipment columns where nothing matched
- Every customer and every order, because the LEFT JOIN is written first
- Only customers that have at least one order with a matching shipment row
- Customers with no orders, plus all orders that were shipped
Show answer
The LEFT JOIN creates NULL-extended rows for order-less customers, but the next join then evaluates s.order_id = o.order_id with o.order_id NULL, which is never true, so those rows are eliminated along with any order that has no shipment. Option 0 is tempting because LEFT JOIN looks like a promise about the whole query; it only applies at the step where it is written, and a later inner join can filter its output again.