INNER JOIN

Combine rows from two or more tables, keeping only the rows that match

Intermediate 22 minJOININNER JOINON

What Is an INNER JOIN in SQL?

INNER JOIN returns only the rows where the join condition finds a match in both tables. Each matching pair of rows becomes one result row, and a row with no match in the other table is left out.

Logically, this is what happens for FROM customers c INNER JOIN orders o ON o.customer_id = c.id (real engines use faster methods, but the result is the same):

1Take one row from customers.

2Find every row in orders where the ON condition is true for that customer.

3Output one combined row per matching order, so a customer with 2 orders produces 2 rows.

4If no order matches, output nothing for that customer.

5Repeat for the next customer.

JOIN means INNER JOIN — The INNER keyword is optional. JOIN on its own is an inner join in PostgreSQL, MySQL, SQL Server, Oracle and SQLite.

Table order does not change the rows — customers JOIN orders and orders JOIN customers return the same 12 rows. With LEFT JOIN, the order decides which table is kept in full.

NULL keys never match — NULL = NULL is not true, so with an = condition a row whose join column is NULL never matches and never appears in the result.

Knowledge Canvas

Core Mechanics / How It Works

Combine rows from two or more tables

Returns one row for every pair of rows where the ON condition is TRUE
A row with no match in the other table is left out
A row with several matches repeats once per match
JOIN without the INNER keyword is still an INNER JOIN

Warnings & Gotchas

Common mistakes and errors to watch out for

One-to-many joins repeat the "one" side, so SUM over its columns double counts
A JOIN without ON runs as a cross join in SQLite and MySQL
NULL keys never match, not even another NULL
Joining on the wrong column can return 0 rows with no error

Power Patterns & Performance Tips

Optimize your joins for faster queries

Index foreign key columns used in joins (primary keys already have an index)
Read the query plan to see how a join will run (EXPLAIN in PostgreSQL and MySQL, EXPLAIN QUERY PLAN in SQLite)
The optimizer may reorder inner joins, so write them in the order that reads best
Choose INNER or LEFT by the rows you need, not by speed

EXPLAINOPTIMIZER

Critical Rules to Remember

Know your join behavior by heart

Qualify columns with table aliases once two tables share a name like id
Always give an INNER JOIN an ON (or USING) condition
Aggregate the "many" side before summing a column from the "one" side
Count rows before and after each join

Relations & Connections

How join types relate to each other

INNER JOIN = matched pairs only (can be more rows than either table)
LEFT JOIN = all left + matched right
RIGHT JOIN = all right + matched left
FULL OUTER JOIN = everything from both sides

Join Type Comparison

When to use which join

INNER: only matches survive

LEFT: all left rows survive

CROSS: every combination (cartesian)

SELF: table joined to itself

Use INNER for linked data

Use LEFT for "find what's missing"

Syntax

Syntax Template

1SELECT

2 a.column1,

3 b.column2

4FROM table_a a

5INNER JOIN table_b b

6 ON b.a_id = a.id

7-- optional: each extra JOIN links one more table

8INNER JOIN table_c c

9 ON c.b_id = b.id;

INNER JOINKeeps only the row pairs where the ON condition is true. INNER is optional: JOIN alone means the same thing.

ONAny true/false condition. Usually a foreign key equals a primary key, such as o.customer_id = c.id; add more conditions with AND.

a, b, cTable aliases. Needed in practice as soon as two tables share a column name such as id: write a.id or b.id.

Extra JOINEach additional JOIN ... ON adds one table and must link it to a table already in the query.

Sample Data

customers

12 rows

id	name	city
C1001	Aarav Sharma	Mumbai
C1002	Sara Chen	Singapore
C1003	James Wilson	London
C1004	Maria Garcia	Madrid
C1005	Yuki Tanaka	Tokyo
C1006	Priya Patel	Delhi
C1007	Alex Johnson	New York
C1008	Chen Wei	Shanghai
C1009	Emma Brown	Sydney
C1010	Omar Hassan	Dubai
C1011	Lena Muller	Berlin
C1012	Ravi Kumar	Bangalore

orders

12 rows

id	customer_id	order_date	status	total
5001	C1001	2024-01-15	delivered	59.98
5002	C1002	2024-02-08	delivered	449
5003	C1001	2024-02-20	shipped	12.5
5004	C1003	2024-03-05	delivered	89.99
5005	C1006	2024-03-15	pending	245
5006	C1005	2024-03-22	delivered	159
5007	C1004	2024-04-02	shipped	45.5
5008	C1009	2024-04-12	cancelled	79
5009	C1012	2024-04-22	delivered	425
5010	C1007	2024-05-05	pending	18.99
5011	C1008	2024-05-18	delivered	134
5012	C1010	2024-06-08	cancelled	65.5

order_items

16 rows

id	order_id	product_id	quantity	unit_price
6001	5001	P101	2	24.99
6002	5001	P104	1	12.99
6003	5002	P109	2	159
6004	5002	P102	1	189
6005	5003	P108	1	8.5
6006	5004	P107	1	89.99
6007	5005	P106	1	95
6008	5005	P102	1	189
6009	5006	P109	1	159
6010	5007	P103	1	45.5
6011	5008	P105	1	79
6012	5009	P110	1	425
6013	5010	P101	1	24.99
6014	5011	P105	1	79
6015	5011	P103	1	45.5
6016	5012	P107	1	89.99

products

10 rows

id	name	category	price
P101	Wireless Mouse	Electronics	24.99
P102	Office Desk	Furniture	189
P103	LED Desk Lamp	Furniture	45.5
P104	USB-C Cable	Electronics	12.99
P105	Bluetooth Speaker	Electronics	79
P106	Office Chair	Furniture	95
P107	Coffee Maker	Appliances	89.99
P108	Notebook Set	Stationery	8.5
P109	Wireless Headphones	Electronics	159
P110	Standing Desk	Furniture	425

reviews

12 rows

id	product_id	customer_id	rating	comment
7001	P101	C1001	5	Excellent mouse, very comfy
7002	P101	C1003	4	NULL
7003	P101	C1005	3	Good but battery drains fast
7004	P102	C1002	5	Solid build, worth every rupee
7005	P102	C1006	4	Spacious — fits two monitors
7006	P103	C1004	4	NULL
7007	P104	C1007	5	Just works, durable braided cable
7008	P105	C1009	5	Great sound for the price
7009	P105	C1012	4	Battery could be better
7010	P107	C1008	3	NULL
7011	P108	C1011	5	Good notebooks, smooth paper
7012	P109	C1001	5	Premium feel, noise cancellation is real

Match Each Order to Its Customer

List every order with the name of the customer who placed it, sorted by customer name.

SQL

1SELECT

2 c.name,

3 o.id AS order_id,

4 o.order_date,

5 o.total

6FROM customers c

7INNER JOIN orders o

8 ON o.customer_id = c.id

9ORDER BY

10 c.name,

11 o.id;

The ON clause pairs each order with the customer whose id equals the order's customer_id. customers has 12 rows, orders has 12 rows, and the result has 12 rows too, but the equal count hides two changes. Lena Muller (C1011) has no orders, so she is missing. Aarav Sharma has two orders (5001 and 5003), so he appears twice. Only 11 different customers are in the result. To keep Lena, with NULL in the order columns, use LEFT JOIN, which returns 13 rows here.

Output

12 rows

name	order_id	order_date	total
Aarav Sharma	5001	2024-01-15	59.98
Aarav Sharma	5003	2024-02-20	12.5
Alex Johnson	5010	2024-05-05	18.99
Chen Wei	5011	2024-05-18	134
Emma Brown	5008	2024-04-12	79
James Wilson	5004	2024-03-05	89.99
Maria Garcia	5007	2024-04-02	45.5
Omar Hassan	5012	2024-06-08	65.5
Priya Patel	5005	2024-03-15	245
Ravi Kumar	5009	2024-04-22	425
Sara Chen	5002	2024-02-08	449
Yuki Tanaka	5006	2024-03-22	159

Why Rows Disappear or Repeat After an INNER JOIN

An INNER JOIN can return fewer rows than either table, more rows than either table, or the same number while still losing some. Four rules explain almost every surprise:

No match, no row — Lena Muller has no orders, Omar Hassan has never written a review, and Office Chair and Standing Desk have no reviews. Each of them silently drops out of the matching inner join.

One-to-many repeats the "one" side — Aarav Sharma has 2 orders, so his name appears on 2 rows of customers JOIN orders. That is correct, because each row is one order. It becomes a bug only when you add up a column from the "one" side, as the customer totals example below shows.

Many-to-many multiplies — Join orders to reviews on customer_id and you get 13 rows from two 12-row tables: Aarav's 2 orders × 2 reviews alone make 4 rows. So INNER JOIN is not the "intersection" of two tables, despite the usual Venn diagram. The diagram shows which keys match, not how many rows come out.

NULL never matches — NULL = NULL evaluates to NULL, not true, so a row with a NULL join key matches nothing. comment is the only column in these tables that holds NULLs, so as a demonstration, join reviews to itself (a self join) on comment: only 9 rows come back, not 12, because the 3 reviews without a comment do not even match themselves. If NULLs should match each other, write IS NOT DISTINCT FROM instead of = (PostgreSQL, SQL Server 2022 and later, and the SQLite on this site accept it; in MySQL write a <=> b).

The habit that catches all four: count the rows before and after each join and ask whether the change makes sense (the next example shows this with four tables). And if you only need to know whether a match exists, such as which customers have ordered, a join lists Aarav twice. Use SELECT DISTINCT or EXISTS for that kind of question.

Join Three or More Tables: What Each Order Contained

List every item in every order, with the customer's name, the product name and the quantity.

SQL

1SELECT

2 o.id AS order_id,

3 c.name AS customer,

4 p.name AS product,

5 oi.quantity

6FROM orders o

7INNER JOIN customers c

8 ON c.id = o.customer_id

9INNER JOIN order_items oi

10 ON oi.order_id = o.id

11INNER JOIN products p

12 ON p.id = oi.product_id

13ORDER BY

14 o.id,

15 p.name;

Read the joins from top to bottom. Each JOIN adds one table, and its ON clause links it to a table that is already in the query: customers and order_items link to orders, and products links to order_items. The result has one row per order item, 16 rows, the same as order_items itself, because every item has exactly one order and one product. Order 5001 takes two rows because it contains two products (USB-C Cable and Wireless Mouse), and Aarav Sharma appears on three rows because his two orders hold three items between them. Aliases matter here: all four tables have an id column, and two of them have a name column.

Output

First 6 of 16 rows

order_id	customer	product	quantity
5001	Aarav Sharma	USB-C Cable	1
5001	Aarav Sharma	Wireless Mouse	2
5002	Sara Chen	Office Desk	1
5002	Sara Chen	Wireless Headphones	2
5003	Aarav Sharma	Notebook Set	1
5004	James Wilson	Coffee Maker	1

Revenue by Category: INNER JOIN with GROUP BY

How many units did each product category sell, and how much revenue did it bring in?

SQL

1SELECT

2 p.category,

3 SUM(oi.quantity) AS units_sold,

4 ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue

5FROM order_items oi

6INNER JOIN products p

7 ON p.id = oi.product_id

8GROUP BY

9 p.category

10ORDER BY

11 revenue DESC;

order_items has quantities and prices but no categories; products has the categories. The join runs first and gives each of the 16 order lines its category, then GROUP BY collapses those lines into 4 groups. Furniture leads with 989 from 6 units, ahead of Electronics with 722.96 from 9 units. The four revenues add up to 1,900.44, the value of all 16 lines, so no line was lost or counted twice. Revenue is calculated per line from quantity * unit_price; orders.total would be the wrong column here, because it belongs to the whole order and would repeat on every line of that order. In this dataset an order's total also differs from the sum of its lines in 7 of the 12 orders (order 5002: total 449, lines 2 × 159 + 189 = 507), so line revenue (1,900.44) and order totals (1,783.46) are different measures. Use the one that matches the question.

Output

4 rows

category	units_sold	revenue
Furniture	6	989
Electronics	9	722.96
Appliances	2	179.98
Stationery	1	8.5

Customer Totals Without Double Counting

For each customer, show how many orders they placed, how much they spent, and how many units they bought.

SQL

1WITH order_units AS (

2 SELECT

3 order_id,

4 SUM(quantity) AS units

5 FROM order_items

6 GROUP BY

7 order_id

9SELECT

10 c.name,

11 COUNT(*) AS order_count,

12 ROUND(SUM(o.total), 2) AS total_spent,

13 SUM(u.units) AS units_bought

14FROM customers c

15INNER JOIN orders o

16 ON o.customer_id = c.id

17INNER JOIN order_units u

18 ON u.order_id = o.id

19GROUP BY

20 c.id,

21 c.name

22ORDER BY

23 total_spent DESC;

Spending comes from orders.total (one row per order), but units live in order_items (one row per item). The WITH block, a CTE, adds up the items of each order first, so order_units has exactly one row per order and the join cannot repeat an order. Sara Chen's single order of 449 contained 3 units, and Aarav Sharma placed 2 orders worth 72.48 in total and bought 4 units. Lena Muller is absent because she has no orders. Joining order_items directly instead would repeat each order once per item and report 898 for Sara Chen (see Common Mistakes below). ROUND keeps floating-point noise out of money: without it, Aarav's total prints as 72.47999999999999.

Output

11 rows

name	order_count	total_spent	units_bought
Sara Chen	1	449	3
Ravi Kumar	1	425	1
Priya Patel	1	245	2
Yuki Tanaka	1	159	1
Chen Wei	1	134	2
James Wilson	1	89.99	1
Emma Brown	1	79	1
Aarav Sharma	2	72.48	4
Omar Hassan	1	65.5	1
Maria Garcia	1	45.5	1
Alex Johnson	1	18.99	1

ON vs USING vs WHERE: Other Ways to Write a Join

The same inner join can be written several ways. They differ in what they can express and in how they fail when something is wrong.

ON — Takes any true/false condition, so it handles columns with different names (o.customer_id = c.id), several conditions joined with AND, and comparisons other than =. Use it by default.

USING (column) — Shorthand for ON a.column = b.column when both tables use the same column name. orders INNER JOIN reviews USING (customer_id) works, and customer_id appears only once in SELECT *. orders INNER JOIN customers USING (customer_id) fails with cannot join using column customer_id - column not present in both tables, because customers calls that column id.

NATURAL JOIN — Joins on every column name the two tables share, which is risky. orders NATURAL JOIN customers matches on id, the only shared name, so it compares order numbers like 5001 with customer ids like 'C1001'. SQLite returns 0 rows without an error; PostgreSQL stops with operator does not exist: integer = text.

Comma join — FROM orders o, customers c WHERE o.customer_id = c.id is the older style from before SQL-92 added the JOIN keyword, and it returns the same 12 rows. Forget the WHERE line and you get all 12 × 12 = 144 combinations. With JOIN ... ON, each condition sits next to the table it belongs to, so it is harder to lose.

Filter in ON or WHERE? — For an inner join it makes no difference: AND o.status = 'delivered' in the ON clause or WHERE o.status = 'delivered' both return the same 6 rows. For customers c LEFT JOIN orders o ON o.customer_id = c.id it does: with AND o.status = 'delivered' in ON, all 12 customers stay (those without a delivered order get NULLs); in WHERE, only the 6 with a delivered order remain. Put conditions that define the match in ON and filters on the final result in WHERE, and the query stays correct if INNER later becomes LEFT.

Match on Two Columns: Reviews From Verified Buyers

Which reviews were written by a customer who actually ordered the product they reviewed? Cancelled orders do not count.

SQL

1SELECT

2 c.name AS customer,

3 p.name AS product,

4 r.rating

5FROM reviews r

6INNER JOIN orders o

7 ON o.customer_id = r.customer_id

8 AND o.status <> 'cancelled'

9INNER JOIN order_items oi

10 ON oi.order_id = o.id

11 AND oi.product_id = r.product_id

12INNER JOIN customers c

13 ON c.id = r.customer_id

14INNER JOIN products p

15 ON p.id = r.product_id

16ORDER BY

17 c.name;

A review counts as verified only if the reviewer has an order containing the reviewed product, so order_items must match on two columns at once: its order (oi.order_id = o.id) and its product (oi.product_id = r.product_id). The status check sits in the ON clause of orders because it defines which orders count as a match. Only 4 of the 12 reviews pass. Aarav Sharma's Wireless Mouse review stays, but his Wireless Headphones review drops out because he never ordered headphones. Emma Brown's Bluetooth Speaker review drops out because her order 5008 was cancelled, and Lena Muller's Notebook Set review drops out because she has no orders at all. Without the AND oi.product_id = r.product_id line, the query returns 17 rows: every review paired with every item in its author's orders that were not cancelled. One caution: a customer who had ordered the same product twice would get their review listed twice, so check for repeats before counting.

Output

4 rows

customer	product	rating
Aarav Sharma	Wireless Mouse	5
Maria Garcia	LED Desk Lamp	4
Priya Patel	Office Desk	4
Sara Chen	Office Desk	5

Common Mistakes

❌

Ambiguous column name: id

SQL

1SELECT

2 id,

3 name,

4 total

5FROM orders o

6INNER JOIN customers c

7 ON o.customer_id = c.id;

Both tables have an id column, so the database cannot tell which one you mean. SQLite stops with ambiguous column name: id; PostgreSQL says column reference "id" is ambiguous. A related error comes from mixing table names and aliases: after FROM orders o, writing WHERE orders.status = 'delivered' fails with no such column: orders.status in SQLite (invalid reference to FROM-clause entry for table "orders" in PostgreSQL).

Prefix every column with its alias (o.id, c.name, o.total), and once a table has an alias, use only the alias.

❌

Forgetting the ON clause

SQL

1SELECT

2 o.id AS order_id,

3 c.name

4FROM orders o

5INNER JOIN customers c;

Without a condition, every order is paired with every customer. SQLite and MySQL run this without complaint and return 144 rows (12 orders × 12 customers), a CROSS JOIN nobody asked for. PostgreSQL rejects it with syntax error at or near ";", and SQL Server and Oracle also reject it as a syntax error.

Always give INNER JOIN an ON (or USING) condition. If a result has far more rows than either table, check for a missing or incomplete join condition first.

❌

Totals counted twice after a one-to-many join

SQL

1SELECT

2 c.name,

3 COUNT(*) AS order_count,

4 ROUND(SUM(o.total), 2) AS total_spent

5FROM customers c

6INNER JOIN orders o

7 ON o.customer_id = c.id

8INNER JOIN order_items oi

9 ON oi.order_id = o.id

10GROUP BY

11 c.id,

12 c.name

13ORDER BY

14 total_spent DESC;

Joining order_items repeats each order once per item, and o.total repeats with it. Orders 5001, 5002, 5005 and 5011 have two items each, so their totals are added twice: Sara Chen shows 2 orders and 898 instead of 1 order and 449, and Aarav Sharma shows 3 orders instead of 2. Over all orders, the total comes out as 2,671.44 instead of 1,783.46. There is no error; the numbers are simply wrong. COUNT(DISTINCT o.id) would repair the order count, but not the sum.

Aggregate the "many" table per order before joining, as the customer totals example above does with a CTE, or leave order_items out when the query uses nothing from it.

❌

Joining on the wrong column

SQL

1SELECT

2 o.id AS order_id,

3 c.name

4FROM orders o

5INNER JOIN customers c

6 ON o.id = c.id;

Both tables have an id, so o.id = c.id looks reasonable, but it compares order numbers (5001) with customer ids ('C1001'). Nothing matches, and SQLite returns 0 rows without any error. PostgreSQL stops with operator does not exist: integer = text.

Follow the foreign key: ON o.customer_id = c.id. When a join returns 0 rows, check first that the two columns hold the same kind of value.

INNER JOIN in PostgreSQL, MySQL, SQL Server and Oracle

The examples on this page run on SQLite (see the SQLite SELECT documentation), but INNER JOIN ... ON works the same way in every major database, and INNER is optional everywhere. What differs is which shortcuts each database supports and how strictly it checks a wrong query:

PostgreSQL — Supports ON, USING and NATURAL JOIN. It is strict: a JOIN with no condition and a comparison between an integer column and a text column are both errors (see Common Mistakes). See the PostgreSQL SELECT reference for the join syntax.

MySQL — Supports ON, USING and NATURAL JOIN. JOIN, INNER JOIN and CROSS JOIN are treated as equivalents, so a JOIN without ON runs as a cross join, as it does in SQLite. See MySQL JOIN syntax.

SQL Server — ON is required: there is no USING and no NATURAL JOIN. See the SQL Server FROM clause reference.

Oracle — Supports ON, USING and NATURAL JOIN. A column named in USING must stay unqualified everywhere in the query, even in the SELECT list: write customer_id, not o.customer_id, or Oracle stops with ORA-25154. PostgreSQL, MySQL and SQLite accept the qualified form. See the Oracle SELECT reference.

Key Concepts

1INNER JOIN returns one row for every pair of rows where the ON condition is true

2Rows with no match are dropped: Lena Muller has no orders, so she never appears

3A row that matches several rows repeats once per match, so a join can add rows as well as remove them

4JOIN on its own means INNER JOIN in PostgreSQL, MySQL, SQL Server, Oracle and SQLite

5Before summing a column from the "one" side, aggregate the "many" side, or totals get counted more than once

Pro Tip

To see which rows an inner join dropped, switch it to a LEFT JOIN and keep only the rows with no partner: FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL returns Lena Muller, the one customer missing from the inner join. Test a column that is never NULL in a real match, such as the key o.id.

When to Use

Use INNER JOIN when a row is only useful together with its partner: order lines with product names, reviews with the reviewer's name, revenue by product category. When rows without a partner must stay in the result, such as customers who have never ordered, use LEFT JOIN instead.

Challenge

Solve the problem below

For each product that has at least one review, show three columns: product (the product name), review_count and avg_rating (the average rating, rounded to 2 decimals). Sort by avg_rating from highest to lowest, then by product.

Your Query

NULLs in Aggregations

LEFT JOIN