How Inner Join Works in SQL

An inner join returns only rows that match in both tables

An inner join is a SQL command that combines rows from two tables based on a condition you set. It returns only the rows where the condition is true in both tables. If a row in one table has no matching row in the other table, that row does not appear in the result.

Think of it like comparing two lists. You have a list of customers and a list of orders. An inner join shows you only the customers who have placed orders, paired with their order information. Customers with no orders disappear from the result, and orders with no matching customer also disappear.

Key Takeaways

An inner join combines two tables and shows only rows where both tables have matching data based on your condition.
The basic syntax is SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column.
Inner join is the most restrictive join type because it filters out non-matching rows from both tables.
You specify the matching condition in the ON clause, usually by comparing a column from each table.

The basic syntax and how to read it

The structure of an inner join looks like this:

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

Breaking this down: SELECT * means "get all columns". FROM table1 names your first table. INNER JOIN table2 adds the second table. ON table1.id = table2.id sets the matching condition — in this case, rows match when the id column is the same in both tables.

You can also write this without the word INNER. JOIN alone means inner join by default in most SQL systems. So SELECT * FROM table1 JOIN table2 ON table1.id = table2.id does the same thing.

If you want specific columns instead of all columns, replace the asterisk with column names: SELECT table1.name, table2.order_date FROM table1 INNER JOIN table2 ON table1.id = table2.id.

A real example with customers and orders

Suppose you have a customers table with columns id, name, and city. You also have an orders table with columns id, customer_id, and amount. You want to see each customer's name alongside their order amounts.

The query would be:

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

This returns only customers who have at least one order. If a customer has never ordered, they do not appear. If an order somehow exists with no matching customer id, that order does not appear either. The result shows pairs of customer names and order amounts, one row per order.

If you had 100 customers but only 75 have placed orders, your result would have rows only for those 75 customers and their orders.

How inner join differs from other join types

SQL has four main join types, and inner join is the most restrictive. A left join keeps all rows from the first table even if they have no match in the second table. A right join keeps all rows from the second table. A full outer join keeps all rows from both tables. An inner join keeps only rows that match in both.

If you used a left join in the customer and order example above, you would see all 100 customers, with NULL values in the amount column for customers who have never ordered. With an inner join, those 25 customers simply do not appear.

Inner join is useful when you only care about records that have a relationship. If you need to see all records regardless of whether they match, you would choose a different join type.

The ON clause and how to set your matching condition

The ON clause is where you tell SQL which rows count as a match. Most often, you compare a primary key from one table to a foreign key in another table. In the customer example, customers.id is the primary key, and orders.customer_id is the foreign key that points back to it.

You can also match on other columns. For example, if you had a products table and a sales table, both with a product_code column, you could write ON products.product_code = sales.product_code. The column names do not have to be identical — only the values have to match.

You can even use multiple conditions in the ON clause with AND or OR. For example: ON customers.id = orders.customer_id AND orders.amount > 100 would return only orders over 100 dollars. However, this is less common than a single matching condition.

When to use inner join versus other approaches

Use inner join when you want to see only records that exist in both tables. This is common when you are analyzing related data — customers with orders, employees with departments, products with sales.

If you need to see all records from one table even if they have no match, use a left or right join instead. If you need to see all records from both tables, use a full outer join. If you just need data from one table with no joining, you do not need a join at all.

Inner join is also faster than some other join types because it filters out non-matching rows early. For large datasets, this can make a noticeable difference in query speed.

Common mistakes and how to avoid them

The most common mistake is forgetting the ON clause entirely. Without it, SQL performs a cross join — it pairs every row from the first table with every row from the second table, creating a huge result set with no meaningful relationships. Always include ON and set a real matching condition.

Another mistake is using the wrong column in the ON clause. If you accidentally match on a column that does not represent the relationship you want, your results will be wrong or empty. Double-check that the columns you are comparing actually represent the same thing in both tables.

A third mistake is forgetting to prefix column names with the table name when the same column name appears in both tables. If both tables have an id column and you write ON id = id, SQL will not know which id you mean. Write ON table1.id = table2.id instead.

Frequently Asked Questions

What happens if multiple rows in one table match a single row in the other table?

The inner join returns multiple result rows — one for each match. If a customer has placed five orders, the inner join returns five rows, one for each order paired with that customer's information. This is expected behavior and not an error.

Can I inner join more than two tables at once?

Yes. You can chain multiple inner joins together: SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id INNER JOIN table3 ON table2.id = table3.id. Each join adds another table to the result, keeping only rows that match across all tables.

Is INNER JOIN the same as JOIN?

Yes, in most SQL systems. Writing JOIN alone defaults to inner join. You can write either JOIN or INNER JOIN and get the same result. Some people write INNER JOIN to be explicit about what they mean.

What if the matching columns have different data types?

SQL will attempt to convert the data types to compare them, but this can cause errors or unexpected results. It is best practice to make sure the columns you are joining on have the same data type — both integers, both text, both dates, and so on.

How do I know if my inner join is working correctly?

Run the query and check that the number of result rows makes sense. If you expected to see customers with orders and you see far fewer rows than you have customers, that is correct — inner join filters out customers with no orders. If you see zero rows, check that your ON condition is actually matching any data.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.