Multi-Table Joins and Aliases

Real reports rarely stop at two tables, and a query that joins four of them quickly becomes unreadable without help. Chapter 12 of the SQL for Beginners series is about that help: table aliases that name each copy of a table, column aliases that name your results, and the discipline that keeps a long join legible. You will also meet the trick that lets one table play two roles at once — the self join.

Table aliases

An alias declared after a table name replaces it for the rest of the statement. The AS keyword is optional for tables (it is not for columns), and single letters are the tradition because every reference then reads as a qualified name rather than a lookup:

SELECT c.first_name, o.status
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
ORDER BY o.id
LIMIT 3;

Expected output:

first_name  status
----------  ---------
Aarav       paid
Aarav       shipped
Priya       cancelled

Three things are true of every alias. It exists only inside this statement — nothing is stored and the next query must declare it again. It must be used everywhere the table is named, including in the ON condition; after FROM customers AS c, plain customers.first_name is no longer valid. And two aliases may even name the same physical table, which is precisely what makes the self join in a moment possible.

Column aliases

An alias in the SELECT list names the output column, and it is how calculated values get a label a human can read. Concatenate with ||, then name each result:

SELECT first_name || ' ' || last_name AS customer,
       signup_date AS joined,
       SUBSTR(signup_date, 1, 4) AS year_joined
FROM customers
WHERE year_joined = '2025'
ORDER BY joined;

Expected output:

customer       joined      year_joined
-------------  ----------  -----------
Aarav Sharma   2025-11-02  2025
Priya Nair     2025-11-15  2025
Lena Fischer   2025-12-01  2025
Marc Tremblay  2025-12-20  2025

The alias works in ORDER BY everywhere, and SQLite is relaxed enough to accept it in WHERE and GROUP BY too. Do not grow attached: standard SQL and PostgreSQL reject an alias in WHERE (the filter runs before the output exists, which is the honest reading), so repeating the expression there is the portable habit. MySQL and SQLite allow it, SQL Server and Oracle allow it in ORDER BY only.

Ambiguous columns

As soon as two tables are joined, a bare column name can mean two things. SQL’s response is not a guess but an error:

SELECT id FROM customers JOIN orders ON orders.customer_id = customers.id;

Expected output:

Error: ambiguous column name: id

Both tables have an id, so SQLite refuses to choose. Qualify every column that exists in more than one joined table — and once you qualify one, qualify them all, because mixed styles are how the wrong id gets through in a table set you had forgotten about. The join condition itself never needs guesswork either: ON o.customer_id = c.id names both sides explicitly, which is why chapter 10’s joins never tripped over the naming.

Qualification is also a reading aid: oi.quantity says at a glance which table the number came from, which keeps a four-table query legible six months later. MySQL and PostgreSQL raise the same ambiguity error with their own wording, so qualified columns are portable insurance as well as good manners.

Building a four-table join

Now the payoff. One row per item, carrying its order, its customer and its product — write it top down, one join per line, each condition directly under the table it constrains:

SELECT c.first_name || ' ' || c.last_name AS customer,
       p.name AS product,
       oi.quantity,
       oi.unit_price,
       oi.quantity * oi.unit_price AS line_total
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = oi.product_id
ORDER BY o.order_date, oi.id;

Expected output:

customer       product                      quantity  unit_price  line_total
-------------  ---------------------------  --------  ----------  ----------
Aarav Sharma   The Silent Patient           1         499         499
Aarav Sharma   The Very Hungry Caterpillar  2         249         498
Aarav Sharma   Clean Code                   1         899         899
Priya Nair     Python Crash Course          1         749         749
Lena Fischer   The Silent Patient           2         499         998
Lena Fischer   Matilda                      1         349         349
Wei Ling       Robotics for Kids            1         649         649
Amara Ochieng  Clean Code                   2         899         1798

Eight rows for eight order items, every column labelled with an alias a reader of the report can use. Two details repay attention. The line_total column is an expression, not a stored value — quantities and prices are multiplied per row, so the total is always current. And the FROM order is chosen deliberately: starting from order_items (the most granular table) and attaching outward follows the foreign keys, so each ON condition reads like the previous chapter’s schema. Reverse the order and you get the same rows with harder-to-check conditions.

One caveat about the expressions: concatenation propagates NULL, so a customer with a missing last_name would render as no customer value at all rather than a half-name. COALESCE(last_name, '') is the usual guard, and chapter 17 covers it alongside the rest of the string toolkit.

Joining a table to itself

An alias does more than shorten names: it lets one table play two roles. Ask which customers share a country by joining customers to customers under two names, pairing each person with everyone “behind” them by id:

SELECT a.first_name AS customer_a, b.first_name AS customer_b, a.country
FROM customers a
JOIN customers b ON a.country = b.country AND a.id < b.id
ORDER BY a.id;

Expected output:

customer_a  customer_b  country
----------  ----------  -------
Aarav       Priya       India

The condition a.id < b.id is doing two jobs. It makes the comparison one-directional, so the pair appears once instead of twice (without it you would also get Priya, Aarav), and it stops every row from joining to itself. This is the classic shape for “find duplicates”, “compare each row to its neighbour” and “who else lives in my city” queries — with six customers in six cities, country is the only field with a match to find.

Note also what the join condition refuses to do: a customer whose country were NULL would match nobody, because NULL = NULL is never true. That is the same three-valued logic chapter 11 leaned on, and it is why self joins over optional columns quietly under-report.

Keeping long joins readable

The difference between a query you can maintain and one you rewrite is mostly layout. Five habits cover most of it:

• One join per line — each JOIN starts a line and its ON follows immediately, so adding a table is a one-line edit.
• Qualify everything — oi.quantity rather than quantity; ambiguity becomes impossible and greps stay useful.
• Alias early, alias consistently — c for customers, o for orders, oi for items, p for products, used the same way in every query you write.
• Name the outputs — AS line_total costs nothing and saves the reader from reverse-engineering an expression.
• Test incrementally — run the query with one join, add the next, and check the row count each time; a join that suddenly triples your rows is a wrong condition, not bad luck.

There is one shorthand for tables that share a column name: JOIN orders USING (customer_id) works only when both tables have that exact column, which our schema does not (customers.id against orders.customer_id), so ON is what you will write. When a query outgrows a single statement — a join built from the result of another join — you have arrived at the subject of the next chapter.

Common mistakes

• Referring to a table by its real name after aliasing it — once FROM customers AS c appears, customers.first_name raises an error in SQLite; use the alias everywhere.
• Leaving a column unqualified in a join — id exists in every table here; SQL reports ambiguous column name rather than picking one for you.
• Using a column alias in WHERE in portable code — SQLite and MySQL accept it, PostgreSQL does not; repeat the expression instead.
• Repeating rows in a self join — without a.id < b.id you get each pair twice plus every row joined to itself.
• Choosing a FROM order that hides a mistake — start from the finest-grained table and check the row count after every join you add.
• Alias names that collide with table names — FROM orders AS orders or a stray AS c reused for two tables makes every later reference ambiguous.

Practice checklist

1. Rewrite chapter 10’s customer/order join using single-letter aliases and confirm the output is unchanged.
2. Add three column aliases to a totals query so the report can be read without the SELECT list in view.
3. Trigger the ambiguous-column error on purpose, then fix it by qualifying both tables.
4. Build the four-table query one join at a time, printing COUNT(*) after each step.
5. Write a self join that pairs every product with a cheaper product in the same category, and explain the a.id < b.id trick in your own words.

What's next?

You can now assemble any number of tables into one readable statement — but every condition still lives inside that statement. Chapter 13 gives queries their own names and lets one query feed another in Subqueries.