The orders table has no customer name — only a customer_id. That's deliberate: the name is stored once in customers, and orders merely reference it. But a report of "order — name — amount" needs data from both tables at once. The operation that glues tables together along a reference is called JOIN — the most important skill after SELECT.

INNER JOIN: only the matched

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

Read it as: take the orders, find each one's customer whose customers.id equals orders.customer_id, and output the rows in pairs. The condition after ON is the heart of the join: it says which columns relate the tables (almost always a foreign key to a primary key).

A bare JOIN means INNER JOIN — the "inner" join: only rows that found a match make it into the result. An order with a nonexistent customer, or a customer with no orders, silently drops out.

To avoid typing full table names, give them short aliases:

SELECT o.id, c.name, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'paid';

Everything you know about WHERE, ORDER BY, and LIMIT works here too — the filter applies to the already-glued rows.

LEFT JOIN: keep everyone on the left

What if you need all customers, including those who never ordered? INNER JOIN throws them away. You need LEFT JOIN — "keep every row of the left table, even without a match":

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

Vera has no orders — she still appears in the result, with the order columns empty (NULL — it gets its own article). The same trick finds "orphans": customers with no orders —

SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

The logic: LEFT JOIN kept all customers, and those without a match got NULL in the order fields; the filter keeps only them. It's the classic query for data integrity checks.

The "left" table is the one written before JOIN. There's also RIGHT JOIN (keeps the right one), but in practice everyone writes LEFT and just swaps the tables.

Two traps: losses and duplication

  • Rows disappeared. You ran an INNER JOIN — and the report has fewer orders than the table. Some orders had no match (say, an empty customer_id). INNER JOIN drops unmatched rows silently — if losing them is unacceptable, use LEFT JOIN.
  • Rows doubled. JOIN outputs a row for every pair: Anna has two orders — she appears twice in the result. That's fine for "a list of orders with names" but dangerous for arithmetic: sum something on the customer side after such a join and the totals multiply. Rule: seeing more rows than expected — check whether you're joining "one-to-many."

Where this applies

Real questions to a database are almost always cross-table: "show the orders of the customer with this email," "which products are in order 101," "are there payments without an order." One habitual move — find the id behind a human-readable field in one table and pull the related data from another via JOIN — replaces dozens of UI clicks. And LEFT JOIN with IS NULL is the main tool for finding lost data: orders without items, payments without orders, users without profiles.

Where beginners stumble:

  • They forget the ON condition or write it wrong. A JOIN without a correct ON glues every row to every row — a million rows out of two small tables and a meaningless result.
  • They don't notice INNER JOIN dropped rows. The report "adds up," but orders with an empty customer are silently missing. In doubt — start with LEFT JOIN and look at the NULL rows.
  • They compute totals over duplicated rows. After a one-to-many join, aggregates on the "one" side get multiplied by the number of matches.

What to learn next. The next step is not staring at rows but counting them: COUNT, GROUP BY and HAVING. And what that NULL from LEFT JOIN really is — in the NULL and types article.