SQL INNER JOIN for Beginners: Connect Customers and Orders

A customer name belongs in the customers table. An order amount belongs in orders. SQL INNER JOIN brings those related facts into one result without asking you to copy customer names into every order record. The key is understanding which rows are supposed to match.

Practice setup: These lessons use SQLite and the fictional shop database from the QueryFun SQL practice lab. Open that lab, run its setup once in a fresh practice database, then return here. Do not run the INSERT statements again for each lesson. You can use SQLite Fiddle without installing a database server.

The customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional currency units. Each order belongs to one customer. A customer can have several orders.

Read the relationship first

customers.customer_id identifies one customer. orders.customer_id points to that customer. Customer 1 has two orders, so the relationship is one customer to many orders. That tells us a joined detail report can legitimately show Amina twice.

Do not join columns just because they contain numbers. order_id and customer_id are both integers, but they identify different things. The relationship is between customers.customer_id and orders.customer_id.

Write the join in small parts

SELECT o.order_id,
       c.name,
       c.city,
       o.amount
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.customer_id
ORDER BY o.order_id;

AS o and AS c give short names to the tables for this statement. ON explains which pairs of rows belong together. Prefixing each column with its table alias makes the source clear and avoids ambiguity when both tables contain customer_id.

The result is 101, Amina, Nairobi, 1200; 102, Brian, Mombasa, 800; 103, Amina, Nairobi, 600; and 104, Chao, Nairobi, 1500. Each row represents one order plus information about its matching customer.

Understand what INNER means

An INNER JOIN keeps only pairs that match the ON condition. In our starting data, every order has a matching customer. If an order referred to an absent customer, that order would not appear in this inner-joined report. That can hide data-quality problems unless you check for them separately.

Similarly, a customer with no orders has no matching order pair and does not appear. This query answers “which orders have matching customer details?” It does not answer “show every registered customer.” A LEFT JOIN is useful for that second question.

Filter the joined detail

To show only orders from Nairobi customers:

SELECT o.order_id, c.name, o.amount
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.customer_id
WHERE c.city = 'Nairobi'
ORDER BY o.order_id;

You get orders 101, 103 and 104. Notice that city comes from c, while amount comes from o. Keeping those prefixes makes it easier to extend the query without forgetting where a condition belongs.

Watch for accidental multiplication

The customers table has 3 rows and orders has 4. Pairing every customer with every order would produce 12 combinations. A missing or incorrect join condition can therefore inflate a count or total dramatically.

Repeated Amina rows are expected because she has two orders. Repeating each order three times would be a warning. Before aggregating a join, inspect a few order IDs and check the number of result rows. Adding DISTINCT after a faulty join can conceal symptoms without fixing the relationship.

If the supposed customer lookup table contains duplicate customer IDs, one order could match several customer rows. Our primary key prevents that in customers. In an unfamiliar database, check the keys and uniqueness rather than assuming them.

Try it yourself

Write an inner join that returns only orders worth at least 1000. Show order_id, customer name labelled customer_name, and amount. Predict the number of rows and their combined amount before running it.

SELECT o.order_id,
       c.name AS customer_name,
       o.amount
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.customer_id
WHERE o.amount >= 1000
ORDER BY o.order_id;

Keep learning: Return to the SQL learning path to choose your next lesson.

Further reading: SQLite SELECT documentation.