INNER JOIN

Your data lives in separate tables on purpose — customers in one, orders in another. The JOIN is how you reunite them, and it's the single most important move in SQL. Master this lesson and half of every real-world query you'll ever write is already in your hands.

Two tables, one question

In lesson 5.1 you saw why the shop's data is split: each order stores a customer_id — a foreign key pointing at the customer's primary key — instead of copying the customer's name onto every order. Here are the two tables we'll work with:

SELECT * FROM customers;

 id |   name   |  city
----+----------+--------
  1 | Ada      | Berlin
  2 | Grace    | Lisbon
  3 | Linus    | Berlin
  4 | Margaret | Austin
  5 | Edsger   | Lisbon
  6 | Barbara  | Oslo
(6 rows)

SELECT * FROM orders;

 id  | customer_id |     product      | amount
-----+-------------+------------------+--------
 101 |           1 | Espresso Machine | 220.00
 102 |           1 | Grinder          |  92.00
 103 |           2 | Kettle           |  45.00
 104 |           3 | Espresso Machine | 220.00
 105 |           3 | Filter Papers    |   8.00
 106 |           5 | Kettle           |  45.00
 107 |           5 | Scale            |  60.00
 108 |        NULL | Gift Card        |  25.00
(8 rows)

Now the question a shop owner actually asks: "who bought what?" The names live in one table, the products in the other. Neither table can answer alone. That's a job for a join.

The JOIN, spelled out

An INNER JOIN combines rows from two tables wherever a condition — the ON clause — is true:

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

  name  |     product      | amount
--------+------------------+--------
 Ada    | Espresso Machine | 220.00
 Ada    | Grinder          |  92.00
 Grace  | Kettle           |  45.00
 Linus  | Espresso Machine | 220.00
 Linus  | Filter Papers    |   8.00
 Edsger | Kettle           |  45.00
 Edsger | Scale            |  60.00
(7 rows)

FROM orders is the starting table, JOIN customers brings in the second, and ON says how rows pair up. The word INNER is optional — plain JOIN means the same thing.

Try it — a real database in your browser

This playground has the two tables above already loaded. Edit the query, hit Run, and read the result grid. Then try your own queries at the sql> prompt — the tables stay loaded.

Things worth trying: change ORDER BY to c.name · add WHERE c.city = 'Berlin' · swap the table order (FROM customers c JOIN orders o …) and notice the result is the same rows.

Qualify your columns

Both tables have a column called id. Ask for a bare id and the database refuses — it has no way to know which one you mean:

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

ERROR:  column reference "id" is ambiguous
LINE 1: SELECT id, name, product
               ^

Qualified, the same query is unambiguous and runs:

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

 id  |  name  |     product
-----+--------+------------------
 101 | Ada    | Espresso Machine
 102 | Ada    | Grinder
 103 | Grace  | Kettle
 104 | Linus  | Espresso Machine
 105 | Linus  | Filter Papers
 106 | Edsger | Kettle
 107 | Edsger | Scale
(7 rows)

What "INNER" really means

Look back at the join result and count who's missing. Margaret and Barbara never placed an order — no orders row matches them, so they simply don't appear. And order 108, the gift card with customer_id = NULL, vanished too: NULL never equals anything (3.2's rule, back on stage), so the ON condition is never true for it.

• INNER JOIN keeps only the rows that find a partner — the intersection of the match.
• Unmatched rows on either side are dropped, silently.
• Rows whose join key is NULL can never match — they're dropped too.