Lesson 11 of 20
In progress
INNER JOIN: combining tables
Join two tables on a key, qualify columns with aliases, and recognise the row multiplication that a one-to-many join causes.
Real questions rarely live in one table. "Which customer placed the largest order" needs customers and orders together, and JOIN is how SQL combines them.
What you will learn
- Write an
INNER JOINwith a clearONcondition. - Use aliases and qualified column names.
- Recognise when a join multiplies rows.
The setup
Editable. Runs in your browser.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount_paise INTEGER NOT NULL,
placed_on TEXT NOT NULL
);
INSERT INTO customers (id, name, city) VALUES
(1, 'Ana', 'Pune'),
(2, 'Ravi', 'Delhi'),
(3, 'Meera', 'Mumbai'),
(4, 'Sam', 'Pune');
INSERT INTO orders (id, customer_id, amount_paise, placed_on) VALUES
(1, 1, 125000, '2025-02-10'),
(2, 1, 240000, '2025-02-12'),
(3, 2, 89000, '2025-02-13'),
(4, 5, 50000, '2025-02-14');
SELECT c.name, o.amount_paise
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
ORDER BY o.amount_paise DESC;
Press Run to see the output.
Three rows. Ana appears twice because she has two orders, Ravi once, and Sam and Meera do not appear at all because they have no orders. The order with customer_id = 5 is dropped because no customer 5 exists.
That result describes INNER JOIN exactly: only pairs that match on both sides.
Anatomy of the clause
FROM customers AS c names the left table and gives it an alias. INNER JOIN orders AS o names the right table. ON o.customer_id = c.id states the condition each pair must satisfy.
JOIN alone means INNER JOIN in every common database, and writing INNER makes the intent explicit. The alias is not required, but it shortens every column reference and becomes essential once two tables share a column name.
Qualify every column: c.name rather than name. It documents where the value came from and prevents the "ambiguous column" error when both tables have an id.
Join keys and types
The two columns must be comparable. Joining an integer id to a text column works in SQLite through implicit conversion and fails or scans in stricter databases, so keep the types aligned.
A join on a non-unique column is valid and often correct: joining orders to a products table on category pairs every order with every matching product. That is rarely what you mean, and it is the usual explanation for an unexpectedly huge result.
The reliable mental test is cardinality. One customer to many orders means a customer’s row repeats once per order. If you join that result to a third table, the multiplication compounds.
Joining three tables
Editable. Runs in your browser.
Press Run to see the output.
Each JOIN adds one table and one condition. Reading a multi-table query from top to bottom works: start with customers, keep matching orders, then filter.
When a result has more rows than you expected, remove joins one at a time until the count matches the base table. The join that changes the count is the one multiplying rows.
Aggregating after a join
Editable. Runs in your browser.
Press Run to see the output.
Two things are worth noticing. COUNT(o.id) counts matched orders and would count nulls as zero; COUNT(*) would count rows, which is the same here because a join row implies an order. And the group includes c.id even though it is not selected: two customers can share a name, and grouping by the key keeps them apart.
The GROUP BY must include every plain selected column. SQLite tolerates omissions, PostgreSQL does not, and the portable habit is to group by the key plus the columns you display.
Duplicate-looking rows
Editable. Runs in your browser.
Press Run to see the output.
Ana’s name and city repeat because they are attached to two different orders. That is not a bug and not something DISTINCT should fix: the rows are genuinely different orders. If you only want the customer list, query customers.
The temptation to add DISTINCT here is a good signal that the question is about one table and the join was unnecessary.
Inner join as a filter
INNER JOIN also filters. "Customers who have ordered" is the query above with the amount removed. That is often clearer than a subquery:
Editable. Runs in your browser.
Press Run to see the output.
Here DISTINCT is appropriate, because the question really is "which customers", and Ana should appear once. Compare with the previous query, where duplicates carried information.
Joining on a column that is not the key, such as ON o.customer_id = c.id OR o.customer_id = 0, which matches far more rows than intended. Keep the condition to equality on the key unless you can explain the extra matches.
List each order with the customer’s name and city, including only orders above 100000 paise, then produce a per-city count of such orders.
Show solution
Editable. Runs in your browser.
SELECT c.city, COUNT(*) AS big_orders
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.amount_paise > 100000
GROUP BY c.city;
Press Run to see the output.
Two of Ana’s orders qualify, so Pune appears twice in the detail and twice in the count. WHERE filters the joined rows before grouping, which keeps the count consistent with the detail.
Recap
INNER JOINkeeps only pairs that match on both sides; unmatched rows disappear.- Qualify columns with aliases and join on keys of compatible types.
- A one-to-many join repeats the parent row, and aggregating afterwards is the normal fix.
Next, LEFT JOIN keeps the rows an inner join drops, which is how "customers with no orders" becomes answerable.
Does join order matter?
Can I join more than two tables?
Progress is saved in this browser only. No account, no email.