INNER JOIN

Why it matters

Splitting data into tables keeps it correct, but questions usually span tables: "show each order with the customer's name", "list every enrolment with the course title". A join puts related rows back together for the length of one query. INNER JOIN is the one you will use most.

The idea

Take these two tables:

customers

orders

An inner join pairs each order with the customer whose id equals the order's customer_id:

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

Chen has no orders, so Chen does not appear. That is the defining rule: an inner join keeps only rows that have a match on both sides. (Finding the unmatched rows is the next lesson.)

The ON clause

ON says how rows match. Almost always it is "foreign key equals primary key": customers.id = orders.customer_id. Get it wrong and the result is quietly wrong. For example, ON customers.id = orders.id compares a customer id with an order id and pairs unrelated rows.

If you leave out the condition entirely (for example with a comma between tables and no WHERE), every customer is paired with every order: 3 × 3 = 9 rows. That is a cross join or Cartesian product, and it is a classic accidental bug that can also make queries very slow on large tables.

JOIN on its own means INNER JOIN. Many people write INNER JOIN anyway because it is explicit.

Table aliases and qualified columns

Both tables have an id column, so you must say which one you mean: orders.id or customers.id. A column used without a table name when it exists in both tables gives an "ambiguous column name" error.

Writing full table names gets long, so give each table a short alias:

SELECT o.id AS order_id, c.name, o.total
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;

The AS before a table alias is optional (FROM orders o works too). Aliasing output columns, as with order_id, avoids two columns both called id.

Filtering, sorting and grouping joined rows

After the join, the combined rows behave like one table. Everything you have learned still applies:

-- Orders over 40, biggest first
SELECT o.id AS order_id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.total > 40
ORDER BY o.total DESC;

-- Revenue per customer
SELECT c.name, COUNT(*) AS orders, SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.id, c.name;

Grouping by c.id as well as c.name keeps two different customers who share a name apart.

Joining more than two tables

Chain joins one at a time. With an order_items table (order_id, product_id, quantity) and a products table (id, name, price):

SELECT o.id AS order_id, p.name AS product, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id;

Each JOIN adds one table and needs its own ON. Follow the foreign keys you found when reading the schema.

Watch the row count

A join can multiply rows. Joining customers to orders gives one row per order, not per customer, so Ana appears twice. If you then SUM a customer column (say, a credit limit), Ana's limit is counted twice. Before aggregating joined data, ask: "what does one row represent now?" A quick SELECT COUNT(*) before and after a join is a cheap sanity check.

Common mistakes

• Wrong ON columns, such as joining id to id.
• Missing ON, which produces a Cartesian product.
• Unqualified column names that exist in both tables.
• Forgetting that inner joins drop unmatched rows. If customers "disappear", you probably need a LEFT JOIN.
• Double counting after a join multiplies rows.

Quick recap

• INNER JOIN ... ON pairs rows whose key values match and drops rows with no match.
• The ON condition is usually foreign key = primary key.
• Use table aliases and qualify columns that appear in both tables.
• WHERE, GROUP BY and ORDER BY work on the joined rows.
• Check what one row represents before aggregating.

Try it

Open the Do tab and join a shop's orders to its customers to list the larger orders.