Why data lives in several tables — Joining Tables

Objective

By the end of this lesson you can explain why an order stores a customer_id instead of a customer name, and write a JOIN … ON query that looks up the matching row in another table.

Why it matters: Almost every real question crosses tables: orders need customer names, sales need product categories, shipments need carriers. Joins are how you combine them, and they are the single most used technique after SELECT itself.

Dataset: Starter shop. Examples run in the editor on the right.

Open the orders table of the Corner Market database. Each order tells you who bought (customer_id) and what they bought (product_id), but only as numbers. The customer's name, city and email are not there. They live in customers, once per customer.

This is deliberate. A well-designed database stores each fact in exactly one place:

If Noura changes her email, the shop updates one row in customers, not every order she ever placed.
Orders stay small: a number is cheaper to store than a name, a city and an email repeated on every row.
Nobody can type a customer's name slightly differently on two orders, because the name is never typed on an order at all.

The price of this design is that answering a question like “which customer placed order 2?” needs two tables.

ExampleOrders on their own

SELECT order_id, customer_id, product_id, order_date
FROM orders
ORDER BY order_id;

Returns the 24 orders. The customer_id and product_id columns are only numbers: to know who customer 8 is, you have to look them up in the customers table.

Matching by hand

Take order 2. Its customer_id is 8 in the public data. To find the customer, you scan customers for the row whose customer_id is also 8, and read the name from that row. That is all a join does: for each row on one side, find the rows on the other side whose key values match, and glue the matching rows together side by side.

The two columns that make the match possible have names you met in Track 1:

customers.customer_id is the primary key of customers: it identifies exactly one customer.
orders.customer_id is a foreign key: a column that points at the primary key of another table.

Nearly every join you will write follows a foreign key to the primary key it points to. Try it in the visual below: pick a customer and watch which orders match.

Select a customer to see which orders match o.customer_id = c.id.

customers

orders

#101customer_id 1120
#102customer_id 180
#103customer_id 2200
#104customer_id 560

Noura matches 2 orders, so the join produces 2 rows for this customer.

A join pairs each row with the rows whose key matches. Customers with several orders match several times; a customer with no orders matches nothing.

SELECT columns
FROM first_table
JOIN second_table
  ON second_table.key_column = first_table.key_column;

JOIN names the second table; ON states the rule that decides which rows belong together.

ExampleEvery order with the customer's name

SELECT orders.order_id, orders.order_date, customers.customer_name
FROM orders
JOIN customers
  ON customers.customer_id = orders.customer_id
ORDER BY orders.order_id;

Still 24 rows: each order's customer_id matches exactly one customer, so every order appears once, now with a readable name next to it. Customers who placed several orders appear several times.

Reading the query

FROM orders JOIN customers says which two tables are combined.
ON customers.customer_id = orders.customer_id is the join condition. PostgreSQL keeps a pair of rows only when this condition is true.
orders.order_id is a qualified column name: table name, a dot, column name. Both tables have a customer_id column, so you must say which one you mean whenever a name exists in both. Qualifying every column is a good habit even when it isn't required: a reader can see where each value comes from.

Typing full table names gets long, so people give each table a short table alias with AS (or just a space). The alias then replaces the table name everywhere in the query.

ExampleThe same join with table aliases

SELECT o.order_id, o.order_date, c.customer_name, c.city
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
ORDER BY o.order_id;

Identical rows to the previous example, plus the customer's city. o stands for orders and c for customers; short, meaningful aliases keep multi-table queries readable.

Quick check

The orders table has 24 rows and every order's customer_id exists in customers. How many rows does orders JOIN customers ON c.customer_id = o.customer_id return?

What can go wrong

The database does not know which columns should match. It does exactly what the ON clause says. If you write ON c.customer_id = o.order_id, PostgreSQL happily pairs order 3 with customer 3, even though customer 3 never placed it, and returns a confident, wrong answer with no error. Always check that the two sides of = are a foreign key and the key it refers to.

The next lesson looks closely at what happens to rows that find no match, such as the customer who has never ordered.

Now practise

4 exercises on real data, checked in your browser (practice).