Lesson 11 of 20

In progress

Course progress

SQL

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 JOIN with a clear ON condition.
  • Use aliases and qualified column names.
  • Recognise when a join multiplies rows.

The setup

SQL
Editable. Runs in your browser.





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

SQL
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

SQL
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

SQL
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:

SQL
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
SQL
Editable. Runs in your browser.





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 JOIN keeps 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?
For correctness, no. For performance, the planner chooses the order; write the query so it reads clearly.
Can I join more than two tables?
Yes, each with its own `JOIN … ON`. Chain them and check the row count after each addition.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.