Lesson 14 of 26 · see the practice database

Joining two tables

So far every query read one table. But the facts you want are usually spread over several. The orders table says that order 7 was placed by customer 2. It does not say who customer 2 is. That is in customers. A join brings the two together.

Why the data is split up

A good database stores each fact once. A customer's name and city are kept in one row of customers. Each order carries only the customer's number. If the name were copied into every order, a change of name would have to be made in many places, and sooner or later one would be missed.

The column orders.customer_id holds a value from customers.id. Such a column is called a foreign key: it points to the primary key of another table. Joins follow these pointers.

The first join

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

Read it like this:

• FROM orders starts with the orders.
• JOIN customers brings in the customers table.
• ON orders.customer_id = customers.id is the matching rule. For each order, find the customer whose id equals the order's customer_id.

Each row of the result is an order with its customer's details glued on. JOIN on its own means INNER JOIN. You can write either.

Table names in front of columns

Both tables have a column called id. Writing just id would be unclear, and the database would refuse.

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

Error: ambiguous column name: id

Put the table name and a dot in front: orders.id. It is only required when the name exists in both tables, but in a join it is good manners to do it for every column, so that a reader can see where each one comes from.

Short names for tables

Typing full table names gets long. Give each table a short alias after its name and use that instead.

SELECT o.id, o.order_date, o.status, c.name, c.city
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id;

The word AS is optional here, and most people leave it out: FROM orders o JOIN customers c. One- or two-letter aliases taken from the table name are the custom.

Adding WHERE and ORDER BY

A joined result behaves like one wide table. Everything you already know works on it.

SELECT o.id, o.order_date, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.city = 'Delhi'
ORDER BY o.order_date;

The WHERE here tests a column from customers, and the result shows orders. That is the whole point of joining: filter by one table, show from another.

Another pair of tables

order_items links to products in the same way.

SELECT oi.order_id, p.name, oi.quantity, p.price,
       oi.quantity * p.price AS line_total
FROM order_items oi
JOIN products p ON oi.product_id = p.id
WHERE oi.order_id = 7;

The calculation uses a column from each table. This is a bill for order 7.

Joining and grouping

Join first, then group. Lesson 12 counted orders for each customer_id. Now the names can be shown.

SELECT c.name, COUNT(*) AS orders
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY orders DESC, c.name;

Grouping by c.id as well as c.name is a careful habit. Two different customers could have the same name, and grouping by name alone would merge them into one.

Units sold of each product, by name:

SELECT p.name, SUM(oi.quantity) AS units
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.id, p.name
ORDER BY units DESC
LIMIT 5;

What an inner join leaves out

An inner join returns only the rows that have a match on both sides. Customers who never ordered do not appear in a join of orders and customers. A product that nobody bought does not appear in a join of order items and products.

SELECT COUNT(DISTINCT c.id) AS customers_in_the_join
FROM orders o
JOIN customers c ON o.customer_id = c.id;

There are twelve customers in the table and fewer in the join. Sometimes that is what you want. When it is not, you need a LEFT JOIN, the next lesson.

Forgetting the ON

Without a matching rule, every row of the first table is paired with every row of the second. Twenty orders times twelve customers gives 240 rows of nonsense.

SELECT COUNT(*) AS rows_without_on
FROM orders, customers;

This is called a cross join or a Cartesian product. On two large tables it produces millions of rows and can stall a server. If a join returns far more rows than you expected, look at the ON first.

The old comma style

You will meet older queries that list the tables with commas and put the matching rule in WHERE:

SELECT o.id, c.name
FROM orders o, customers c
WHERE o.customer_id = c.id
LIMIT 3;

It gives the same result. The JOIN ... ON style is better, because the matching rule sits next to the table it belongs to and cannot be forgotten among the other conditions.

How many rows will a join return?

Think about it before running the query. Each order has exactly one customer, so joining orders to customers returns one row per order: twenty. Each order has several items, so joining orders to order items returns one row per item. Knowing what to expect is how you notice a wrong join.

Try it yourself

SELECT o.id, o.status, c.city FROM orders o JOIN customers c ON o.customer_id = c.id;

SELECT p.name, oi.quantity FROM order_items oi JOIN products p ON oi.product_id = p.id WHERE oi.order_id = 1;

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

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.