Joining Tables with INNER JOIN

Introduction

Look at the orders table in our shop. It stores customer_id, not the customer's name. Why? Because storing a name on every order would repeat it again and again, and if the customer changed their name you would need to fix it in many places. Instead the name lives once in customers, and each order points to it. A join follows that pointer and brings the two together.

Keys: how tables are linked

• A primary key uniquely identifies each row in a table. In customers it is id.
• A foreign key is a column that holds another table's primary key. In orders, customer_id refers to customers.id.

Look at both tables:

Order 1 has customer_id 1, which is Asha. A join does that lookup for every row.

INNER JOIN

Read it as: take each order, and match it to the customer whose id equals the order's customer_id. The ON clause is the matching rule.

INNER JOIN keeps only rows that have a match on both sides. An order with no matching customer would be left out, and so would a customer with no orders. (The next lesson shows how to keep them.)

Write table.column whenever a name exists in more than one table, such as id, so the database knows which one you mean.

Aliases make joins shorter

Long table names get tiring. Give each table a short alias after its name.

JOIN on its own means INNER JOIN, and AS is optional for table aliases (FROM orders o).

Filtering and summarising joined data

Joined rows behave like any other rows, so WHERE, GROUP BY and the rest still work.

How many orders does each customer have?

Farid is missing from that list, because he has no orders and an inner join drops unmatched rows.

Try it yourself

List the customers' name together with the status of each of their orders, but only for orders that are shipped or delivered. Then add a count to see how many such orders each customer has.

Summary

• A primary key identifies a row. A foreign key points to another table's primary key.
• INNER JOIN ... ON matches rows from two tables using a rule.
• Use aliases and table.column names to keep joins readable and unambiguous.
• An inner join drops rows that have no match.