How Inner Joins Connect Data From Two Tables in SQL

An inner join combines rows from two tables when they match on a shared column

An inner join is a way to pull data from two different tables at the same time, but only for rows where both tables have matching information. If you have a customers table and an orders table, an inner join lets you see customer names alongside their order details — but only for customers who actually placed orders. Rows with no match get left out entirely.

The word "inner" means you are keeping only the overlapping part. Think of two overlapping circles: an inner join returns only what is in both circles at once. This is different from other join types that might include unmatched rows from one table or the other.

Key Takeaways

An inner join returns only rows where the joining column has the same value in both tables.
You specify which column to match on using the ON keyword, like ON customers.id = orders.customer_id.
Inner joins are the most common join type because they filter out incomplete or orphaned data automatically.
The order of the tables in an inner join does not change the result — joining A to B gives the same rows as joining B to A.

The basic syntax and how to read it

The structure of an inner join looks like this:

SELECT columnsFROM table1INNER JOIN table2ON table1.column = table2.column

The SELECT part names which columns you want to see. The FROM part names the first table. INNER JOIN names the second table. The ON part tells the database which columns to match — it compares the value in table1.column against the value in table2.column, and includes a row only when they are equal.

For example, if you have a customers table with an id column and an orders table with a customer_id column, you would write:

SELECT customers.name, orders.order_date, orders.amountFROM customersINNER JOIN ordersON customers.id = orders.customer_id

This pulls the customer name from the customers table and the order date and amount from the orders table, but only for rows where the customer id matches. A customer with no orders does not appear. An order with no matching customer does not appear either.

When to use an inner join instead of other join types

An inner join is the right choice when you only care about complete pairs of data. If you are building a report of which customers placed orders, an inner join is perfect — it automatically filters out customers who never ordered. If you are calculating total sales by customer, an inner join ensures you are only counting customers with actual orders.

Other join types exist for different situations. A LEFT JOIN would keep all customers even if they never ordered, showing NULL for their order columns. A RIGHT JOIN would keep all orders even if the customer record is missing. A FULL OUTER JOIN would keep everything from both tables. But if you want only the matching rows, an inner join is simpler and faster.

Inner joins are also the most forgiving to write. Because they only return matches, you are less likely to accidentally include garbage data or orphaned records that should not be there.

How the database finds matching rows

When you run an inner join, the database does not just scan both tables once. It uses the ON condition to build a comparison. For each row in the first table, it looks for rows in the second table where the joining column matches. When it finds a match, it combines those two rows into one output row with columns from both tables.

The database is smart about this. If the joining column is indexed — meaning the database has built a fast lookup table for it — the join runs much faster. If you are joining on a column that is not indexed and the tables are large, the join can be slow. This is why database designers often index the columns used in joins.

The order of the tables matters for performance but not for the result. Joining customers to orders is logically the same as joining orders to customers, but the database might choose different strategies depending on which table is listed first and how large each table is.

A real example: matching products to inventory

Suppose you run an online store with a products table and an inventory table. The products table has product_id, name, and price. The inventory table has product_id, warehouse_location, and quantity_on_hand. You want to see which products are in stock and where.

SELECT products.name, products.price, inventory.warehouse_location, inventory.quantity_on_handFROM productsINNER JOIN inventoryON products.product_id = inventory.product_id

This query returns one row for each product that has at least one inventory record. If a product has never been stocked, it does not appear. If an inventory record exists for a product_id that was deleted from the products table, it does not appear either. You get only the clean matches.

If you wanted to see all products, including ones with no inventory, you would use a LEFT JOIN instead. But for a report of what is actually in stock, an inner join is what you need.

Common mistakes when writing inner joins

The most common mistake is getting the ON condition wrong. If you write ON products.product_id = inventory.product_name instead of ON products.product_id = inventory.product_id, the join will not work correctly because you are comparing a number to text. The database might return no rows at all, or rows that happen to match by accident.

Another mistake is forgetting to specify which table a column comes from when the same column name exists in both tables. If both tables have an id column and you write SELECT id instead of SELECT products.id, the database will not know which one you mean and will throw an error. Using the table name as a prefix — products.id and inventory.id — makes it clear.

A third mistake is assuming an inner join will catch data quality problems. If your customer_id values are misspelled or inconsistent in one table, the join will silently skip those rows instead of warning you. Always check your data before joining.

How inner joins compare to filtering with WHERE

A beginner sometimes tries to get the same result by selecting from one table and then filtering with a WHERE clause that checks if a value exists in another table. This works but is slower and harder to read than an inner join.

For example, instead of using an inner join, you might write:

SELECT customers.nameFROM customersWHERE customers.id IN (SELECT customer_id FROM orders)

This returns the same result as an inner join on customer id, but it requires the database to run a subquery and check each customer against it. An inner join does the same work more efficiently and is clearer to anyone reading the code later.

Frequently Asked Questions

What happens if a value appears multiple times in the second table?

The inner join creates one output row for each match. If a customer has three orders, the inner join returns three rows for that customer — one for each order. This is correct behavior, but it means you need to be careful when counting or summing. If you want one row per customer with a count of orders, you need to add a GROUP BY clause.

Can I inner join more than two tables at once?

Yes. You can chain multiple INNER JOIN clauses together. For example: FROM table1 INNER JOIN table2 ON table1.id = table2.id INNER JOIN table3 ON table2.id = table3.id. The database processes them left to right, joining the result of the first join to the third table.

Is an inner join faster than a left join?

Not always. The speed depends on the size of the tables, whether the joining columns are indexed, and how many rows match. An inner join can be slightly faster because it has fewer rows to return, but the difference is usually small. Write the join that matches your logic, not the one you think is faster.

What if the joining columns have different names?

That is fine. You can join customers.customer_id to orders.cust_id by writing ON customers.customer_id = orders.cust_id. The column names do not have to match — only the values they contain need to match.

Do I need to use the word INNER, or can I just write JOIN?

In most databases, writing JOIN without the word INNER defaults to an inner join anyway. So SELECT * FROM a JOIN b ON a.id = b.id works the same as SELECT * FROM a INNER JOIN b ON a.id = b.id. Using INNER makes your intention clearer to anyone reading the code.

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.