What Inner Join Does in SQL and When You'll Use It

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

An inner join is a way to pull data from two different tables in a database by matching rows where a column value is the same in both tables. If a row in one table has no matching row in the other table, that row does not appear in your result. You get back only the rows where both tables have something in common.

Think of it like a Venn diagram where only the overlapping middle section shows up. If you have a customers table and an orders table, an inner join on customer ID will show you only the customers who have placed at least one order — not customers with no orders, and not orders with no matching customer record.

Key Takeaways

An inner join returns only rows where the join column has a matching value in both tables.
Rows that exist in one table but not the other are excluded from the result.
You specify which column to match on using the ON keyword in your SQL statement.
Inner join is the most common type of join because it filters out incomplete or orphaned data automatically.

How the syntax works in a real query

The basic structure is straightforward. You write SELECT, list the columns you want, then FROM the first table, then INNER JOIN the second table, then ON the column that matches between them.

Here is an actual example. Say you have a customers table with columns id, name, and email, and an orders table with columns id, customer_id, and order_date. To see each customer's name alongside their orders, you would write:

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

This query returns one row for each order, paired with the customer name. If a customer has three orders, that customer's name appears three times in the result. If a customer has never ordered, they do not appear at all.

When an inner join filters out data you might not expect

The filtering behavior of inner join is powerful but can hide problems. If you join a customers table to an orders table on customer ID, and a customer record has a typo in the ID field, that customer will vanish from your result even though they exist in the database. You will not get a warning — the row straightforward will not match.

Similarly, if you join employees to departments on department_id, and someone's department_id is NULL or points to a department that was deleted, that employee will not appear in your inner join result. This is often the right behavior, but it is worth knowing it happens silently.

If you need to see all rows from one table even when there is no match, you would use a LEFT JOIN or RIGHT JOIN instead. But if you only care about complete pairs — customers with orders, employees with valid departments — inner join is what you want.

Joining on multiple columns

Sometimes a single column is not enough to uniquely identify a match. You can join on two or more columns by adding AND to your ON clause.

For example, if you have a sales table and a commissions table, and both use year and region to identify a record, you would write:

SELECT sales.amount, commissions.rate FROM sales INNER JOIN commissions ON sales.year = commissions.year AND sales.region = commissions.region

Now a row from sales only matches a row from commissions if both the year and the region are the same. This is common in financial or regional data where a single ID is not enough.

Inner join versus other join types

SQL offers several join types, each with different behavior. An inner join returns only matching rows. A LEFT JOIN returns all rows from the left table, plus matches from the right table, filling in NULL for non-matches. A RIGHT JOIN does the opposite. A FULL OUTER JOIN returns all rows from both tables, with NULL where there is no match.

The choice depends on what you need. If you are analyzing customer behavior and only care about customers who have orders, inner join is correct. If you are checking for customers who have never ordered, you need a left join to see those rows with NULL in the order columns. If you are auditing data and need to find orphaned records — orders with no matching customer — you would use a left join and filter for NULL values.

Performance and large tables

Inner joins are generally fast because databases can use indexes on the join columns to find matches quickly. If you are joining a customers table with 100,000 rows to an orders table with 5 million rows on customer_id, the database will use an index on customer_id to find the matching orders without scanning every row.

The speed depends on whether the join column is indexed. If you frequently join two tables on a column that has no index, adding an index to that column can make the query much faster. Most databases automatically index primary keys, so joining on primary keys is usually efficient.

Common mistakes when writing inner joins

The most common mistake is forgetting the ON clause or writing it incorrectly. If you write INNER JOIN orders but forget to specify which column to match on, the database will either return an error or perform a cross join, which multiplies every row in the first table by every row in the second table — usually not what you want.

Another mistake is joining on the wrong column. If you have two ID columns that look similar but mean different things, joining on the wrong one will produce results that look correct but are actually wrong. Always double-check that the column you are joining on actually represents the same thing in both tables.

A third mistake is not considering NULL values. If the join column contains NULL in either table, those rows will not match anything, even if you expect them to. NULL does not equal NULL in SQL. If you need to match NULL values, you have to use a special condition like COALESCE or handle NULLs explicitly.

Frequently Asked Questions

What is the difference between INNER JOIN and just writing WHERE?

You can sometimes achieve the same result by listing both tables in FROM and using WHERE to filter, but INNER JOIN is clearer and often faster. INNER JOIN explicitly states that you are matching rows between tables, while WHERE filtering can be harder to read and may not use indexes as efficiently.

Can I inner join a table to itself?

Yes, you can join a table to itself by giving it two different aliases. For example, to find all pairs of employees in the same department, you would write SELECT e1.name, e2.name FROM employees e1 INNER JOIN employees e2 ON e1.department_id = e2.department_id AND e1.id < e2.id. This is called a self-join.

Why did some rows disappear when I used INNER JOIN?

Rows disappear because they have no match in the other table. If a customer has no orders, they will not appear in an inner join of customers and orders. If you need to see those rows, use a LEFT JOIN instead to keep all rows from the left table even when there is no match.

Does the order of tables matter in an INNER JOIN?

No. INNER JOIN is symmetric — joining table A to table B produces the same rows as joining table B to table A. The order matters for LEFT JOIN and RIGHT JOIN, which treat the tables differently, but not for INNER JOIN.

How do I know if my join is working correctly?

Count the rows in your result and compare to what you expect. If you are joining customers to orders and you get fewer rows than the number of orders in the orders table, some orders have no matching customer. If you get more rows than expected, you may have duplicate matches — for example, if the join column is not unique in one of the tables.

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.