INNER JOIN SQL: Combine Tables and Return Only Matching Rows

• 29 June 2026
• by: Renata Cardoso
• in: MySQL/MariaDB
• Tags: inner join sql, SQL, sql join
• note: no comments

If you have ever stored your users in one table and their orders in another, you have already met the problem that JOINs solve. The data you care about lives in two places, and you need to bring it back together to make sense of it. The most common tool for that job is the INNER JOIN — and once it clicks, a huge part of SQL suddenly feels approachable.

In this guide I will walk you through what an INNER JOIN actually does, show you real queries with their output, and help you avoid the two mistakes that trip up almost everyone when they start joining tables. Take your time with the examples; type them out yourself if you can. That is how this sticks.

Key Takeaways
• A JOIN combines rows from two or more tables based on a related column (a shared key).
• An INNER JOIN returns only the rows that match in both tables — unmatched rows on either side disappear.
• The ON clause defines the match condition, usually `a.id = b.a_id`.
• Table aliases (`FROM users u`) keep multi-table queries short and readable.
• Forgetting the `ON` clause causes a cartesian product — every row paired with every other row.
• Need to keep unmatched rows (e.g. all users, even with zero orders)? You have outgrown INNER JOIN and want a LEFT JOIN.

What does a JOIN actually do in SQL?

A JOIN combines rows from two or more tables based on a column they have in common. That shared column is the link between the tables — often an ID.

Why split data across tables at all? Because of relational design. Imagine storing every order with the full customer name, email, and address copied into each row. The same customer placing 50 orders means their email is duplicated 50 times. If they change their email, you have 50 rows to update, and one missed row means inconsistent data. Instead, relational databases split the data: customer details live once in a `users` table, and each order in the `orders` table simply references the user by an ID. JOINs are how you recombine that split-apart data when you need to read it together.

Here are the two small tables we will use throughout this tutorial:

“`sql — The users table: one row per person SELECT * FROM users; “`

id	name
1	Amara
2	Bruno
3	Chen

“`sql — The orders table: each order references a user via user_id SELECT * FROM orders; “`

id	user_id	amount
101	1	49.00
102	1	19.50
103	2	99.00
104	5	12.00

Notice two things. Amara (id 1) has two orders. Chen (id 3) has no orders at all. And order 104 references `user_id` 5 — a user who does not exist in our `users` table. Keep those edge cases in mind; they are about to matter.

How does an INNER JOIN work?

An INNER JOIN returns only the rows that have a match in both tables. If a row on either side has no partner on the other side, it simply does not appear in the result.

Here is the basic shape of an INNER JOIN:

“`sql SELECT columns FROM table_a INNER JOIN table_b ON table_a.id = table_b.a_id; “`

Now let us apply it to our example. We want to see each order alongside the name of the user who placed it:

“`sql SELECT orders.id, users.name, orders.amount FROM orders INNER JOIN users ON orders.user_id = users.id; “`

Output:

id	name	amount
101	Amara	49.00
102	Amara	19.50
103	Bruno	99.00

Look closely at what came back — and what did not.

• Amara appears twice, once per order. The INNER JOIN matched her single `users` row to both of her `orders` rows.
• Bruno appears once, for his single order.
• Chen is gone. Chen has no orders, so there was no matching row in `orders`. INNER JOIN dropped him.
• Order 104 is gone. It points to `user_id` 5, who does not exist in `users`. No match, so it vanished too.

That is the entire personality of the INNER JOIN in one result set. It kept only the rows where a match existed on both sides.

What is the ON clause and why does it matter?

The ON clause is the join condition — it tells the database which column in one table corresponds to which column in the other. It is the rule that decides whether two rows “match.”

“`sql … INNER JOIN users ON orders.user_id = users.id “`

Read that out loud: “join an order to a user when the order’s `user_id` equals the user’s `id`.” That comparison is the heart of the join. Without it, the database has no idea how the tables relate, and you get the cartesian-product disaster I will show you shortly.

The ON clause does not have to use IDs, but in well-designed databases it almost always matches a foreign key in one table (`orders.user_id`) to the primary key in another (`users.id`). That relationship is the backbone of relational data.

Here is the mental model that makes the whole join family finally click: an INNER JOIN finds the intersection. It returns only the rows where a match exists in *both* tables, and everything unmatched on either side simply vanishes. That is its defining behavior — and its single most common surprise. When you INNER JOIN `users` and `orders`, you get every order that *has* a user and every user that *has* an order. But a user with no orders disappears entirely (poor Chen), and an order with no matching user disappears too (order 104). People sit down expecting to “see all my users with their orders” and are baffled when the customers who never bought anything are missing. That is not a bug. It is the INNER JOIN doing exactly what it promises: intersection only. The instant you decide you *do* want to keep the unmatched rows — all users, even those with zero orders — you have outgrown INNER JOIN and need a LEFT JOIN. So the entire join family reduces to one question: *do I want only the matches (INNER), or do I want to keep one side’s rows even when there is no match (LEFT/RIGHT)?* Choosing INNER JOIN means you have decided unmatched rows should not appear.

How do table aliases make joins readable?

Once you join two or three tables, writing the full table name in front of every column gets exhausting. Table aliases are short nicknames you assign right after the table name, and then use everywhere else in the query.

“`sql SELECT o.id, u.name, o.amount FROM orders o INNER JOIN users u ON o.user_id = u.id; “`

Here `orders` becomes `o` and `users` becomes `u`. The query does exactly the same thing as before, but it is shorter and easier to scan. You can write `INNER JOIN` or just `JOIN` — in SQL, `JOIN` on its own means `INNER JOIN` by default. I recommend writing `INNER JOIN` explicitly while you are learning, so your intent is obvious to anyone reading the query later (including future you).

Aliases also become essential when you join a table to itself, or when two tables share a column name — which brings us to one of the classic beginner traps.

What are the most common INNER JOIN mistakes?

Two mistakes account for the majority of join frustration. Both are easy to fix once you have seen them.

Mistake 1: Forgetting the ON clause (the cartesian product). If you join two tables without telling the database how they relate, it does the only thing it can — it pairs *every* row in the first table with *every* row in the second. This is called a cartesian product (or cross join), and it explodes fast.

“`sql — DANGER: no ON clause SELECT o.id, u.name FROM orders o INNER JOIN users u; — (in MySQL/MariaDB this becomes a cross join) “`

With 4 orders and 3 users, you get 4 × 3 = 12 meaningless rows: every order paired with every user, including pairings that make no sense. On real tables with millions of rows, this can return billions of rows and bring a server to its knees. Always include your `ON` clause.

Mistake 2: Ambiguous column names. When both tables contain a column with the same name (here, both have an `id`), the database does not know which one you mean:

“`sql — ERROR: which “id”? SELECT id, name FROM orders o INNER JOIN users u ON o.user_id = u.id; — ERROR 1052 (23000): Column ‘id’ in field list is ambiguous “`

The fix is simply to prefix the column with its table or alias so there is no doubt:

“`sql SELECT o.id AS order_id, u.id AS user_id, u.name FROM orders o INNER JOIN users u ON o.user_id = u.id; “`

When in doubt, qualify every column with its alias. It is a tiny bit more typing and it removes a whole category of errors.

How do you join more than two tables?

Real schemas rarely stop at two tables. Say each order also has rows in an `order_items` table. You chain JOINs one after another, each with its own `ON` clause:

“`sql SELECT u.name, o.id AS order_id, i.product, i.quantity FROM users u INNER JOIN orders o ON o.user_id = u.id INNER JOIN order_items i ON i.order_id = o.id; “`

Output:

name	order_id	product	quantity
Amara	101	Keyboard	1
Amara	102	Mouse	2
Bruno	103	Monitor	1

The database joins `users` to `orders` first, then joins that combined result to `order_items`. Because every join here is an INNER JOIN, a row only survives if it matches across *all three* tables. One missing link anywhere in the chain and the row drops out. This is exactly how you reconstruct a complete picture from data that relational design deliberately spread across separate tables. (For more on why tables are structured this way, see the sibling guide on database relationships below.)

How is INNER JOIN different from LEFT JOIN?

This is the comparison that matters most in day-to-day work. The difference is entirely about what happens to unmatched rows.

• INNER JOIN keeps only rows that match in both tables. Unmatched rows on either side disappear.
• LEFT JOIN keeps *every* row from the left table, matched or not. Where there is no match on the right, the right-side columns come back as `NULL`.

Run a LEFT JOIN on our data and watch Chen reappear:

“`sql SELECT u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON o.user_id = u.id; “`

Output:

name	order_id	amount
Amara	101	49.00
Amara	102	19.50
Bruno	103	99.00
Chen	NULL	NULL

Chen is back, with `NULL` in the order columns because he has no orders. That single row is the practical difference between the two joins. Here is a quick reference for the main join types and what each returns:

Join type	Returns
INNER JOIN	Only rows with a match in both tables (the intersection)
LEFT JOIN	All rows from the left table + matches from the right
RIGHT JOIN	All rows from the right table + matches from the left
CROSS JOIN	Every row of one table paired with every row of the other

If your question is “show me only the records that have a relationship,” reach for INNER JOIN. If it is “show me everything from this side, with related data where it exists,” reach for LEFT JOIN. For a deeper dive on keeping unmatched rows, see the LEFT JOIN sibling guide.

Putting it all together

Let me close with a single, realistic query that uses everything we covered — aliases, a multi-table INNER JOIN, qualified columns, and a clear ON clause — to answer a genuine business question: *which products did each paying customer buy, and how much did they spend?*

“`sql SELECT u.name AS customer, i.product AS product, o.amount AS order_total FROM users u INNER JOIN orders o ON o.user_id = u.id INNER JOIN order_items i ON i.order_id = o.id ORDER BY u.name; “`

Output:

customer	product	order_total
Amara	Keyboard	49.00
Amara	Mouse	19.50
Bruno	Monitor	99.00

Customers with no orders never appear, because we chose INNER JOIN — and that is exactly the behavior we want for a report about *paying* customers. If you wanted a report covering *all* customers including non-buyers, you would switch the first join to a LEFT JOIN. That single decision is the whole game.

INNER JOIN is one of the most useful tools in SQL, and you now understand its defining trait: it returns the intersection, only the matching rows. Practice it on your own tables, watch which rows appear and which vanish, and it will become second nature. To see how JOINs fit into the bigger picture of running and managing databases for your site, read our complete guide below.

Want the full context on databases for website owners? Read the pillar: MySQL & MariaDB Databases: The Complete Guide for Website Owners.

Frequently asked questions

Is JOIN the same as INNER JOIN in SQL? Yes. In SQL, writing `JOIN` on its own is treated as `INNER JOIN` — they are identical. While learning, it is worth writing `INNER JOIN` in full so your intent is unmistakable to anyone reading the query.

What happens if there is no matching row in an INNER JOIN? The row is excluded from the result entirely. INNER JOIN only returns rows that match in both tables, so a row with no partner on the other side simply does not appear in the output.

Why is my INNER JOIN returning too many rows? Usually one of two reasons. Either you forgot the `ON` clause, producing a cartesian product (every row paired with every other), or your join key is not unique, so one row matches many. Check that your `ON` condition links the correct columns and that the key is what you expect.

Can I INNER JOIN more than two tables? Yes. Chain additional `INNER JOIN … ON …` clauses, one per table. Because every join is inner, a row survives only if it has a match across all joined tables.

When should I use LEFT JOIN instead of INNER JOIN? Use LEFT JOIN when you need to keep all rows from one table even if they have no match — for example, listing every user including those who have never placed an order. INNER JOIN would drop those non-matching users.