Why joins, and INNER JOIN

Why is the data split up?

In module 1 you met the SHOP tables. An order row does not hold the customer's name and city; it holds only customer_id, a foreign key that points to a row in customers. This is called normalisation: each fact (Asha lives in Pune) is stored in exactly one place. If Asha moves to Mumbai, you change one row, not every order she ever placed.

The price of that tidiness is that a question like "show each order with the customer's name" needs two tables. A join is how SQL answers it. It takes rows from two tables and pairs them up wherever a condition, usually "this key equals that key", is true.

The INNER JOIN syntax

SELECT o.id, o.order_date, c.name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;
-- 101 | 2025-01-05 | Asha Verma
-- 102 | 2025-01-09 | Ravi Kumar
-- 103 | 2025-02-14 | Asha Verma
-- ... 8 rows, one per order

Read it as: for every order, find the customer whose id equals the order's customer_id, and put the two rows side by side. Asha appears three times because she has three orders. Each row of the result is one matching pair.

Table aliases

orders AS o gives the table a short nickname, a table alias, for this query. Then o.id means "the id column of orders" and c.id means "the id column of customers". Both tables have a column called id, so without the prefix the database would not know which you mean. Use short, meaningful aliases (o for orders, c for customers, oi for order_items), and prefix every column once two or more tables are involved.

INNER JOIN keeps matches only

A row with no partner on the other side is dropped. In COMPANY, Deepak Yadav has dept_id NULL. When you join employees to departments, his row finds no department, so he disappears from the result. Legal has no employees, so it disappears too. Ten of the eleven employees come back.

SELECT e.name, d.name AS department
FROM employees AS e
JOIN departments AS d ON d.id = e.dept_id;
-- Meera Iyer     | Engineering
-- ...
-- Lakshmi Pillai | HR
-- 10 rows: Deepak Yadav (no department) is missing

The old comma syntax

Before 1992, joins were written by listing the tables with commas and putting the matching rule in WHERE. It still works in every database, and you will see it in old code.

SELECT o.id, c.name
FROM orders o, customers c
WHERE c.id = o.customer_id;   -- same 8 rows as the JOIN above

Prefer JOIN ... ON. It keeps the matching rule next to the table it belongs to, and leaves WHERE for real filters such as status = 'Delivered'.

A caveat about Venn diagrams

Joins are often drawn as two overlapping circles. That picture helps you remember which side is kept, but it is misleading in one way: a join does not return a set of keys, it returns pairs of rows. If one customer has three orders, the result has three rows for her. A join can return more rows than either table has. Keep that in mind; topic 5 shows how it inflates totals.

Common mistakes

• Joining on the wrong columns, such as ON c.id = o.id. It runs without an error and gives wrong pairs. Always join a foreign key to the key it points to.
• Leaving out the table prefix: SELECT name FROM employees e JOIN departments d ... fails with "ambiguous column name: name". Write e.name or d.name.
• Using the comma syntax and forgetting the WHERE, which pairs every row with every row. Use JOIN ... ON.
• Being surprised that rows vanished. INNER JOIN drops rows with no partner; check for NULL or missing keys, or use a LEFT JOIN.