INNER JOIN

Combine rows from two tables where a condition matches

Intermediate 16 minJOININNER JOIN

Joining Tables Together

INNER JOIN combines rows from two tables where a join condition matches. Databases split data across tables to avoid repetition — INNER JOIN is how you reassemble it. Here's how it works:

The ON clause defines the link — Typically matches a foreign key to a primary key: ON orders.customer_id = customers.id. This tells the database which rows from each table belong together in the combined result.

Both sides must match — Rows without a match in either table are silently excluded. Customer with no orders? Not in the result. Order with a bad customer_id? Also excluded. Only matching pairs survive.

Use table aliases — Short names like c for customers and o for orders keep multi-table queries clean and readable. Aliases become required when both tables share a column name: c.id vs o.id.

Knowledge Canvas

Core Mechanics / How It Works

Combine rows from two or more tables

Matches rows where the ON condition is TRUE
No match on either side → row excluded from result
JOIN without INNER keyword = still INNER JOIN
Always use table aliases when joining

Warnings & Gotchas

Common mistakes and errors to watch out for

Unexpected duplicate rows from one-to-many relationships
Accidental cross joins without ON clause
NULL handling issues — NULLs never match in ON conditions
Unequal matching columns produce wrong or inflated results

Power Patterns & Performance Tips

Optimize your joins for faster queries

Index the join columns on both tables
Use EXPLAIN plans to learn query intent
Prefer INNER JOINs over OUTER when possible
Join smaller datasets first to reduce intermediate size

EXPLAINOPTIMIZER

Critical Rules to Remember

Know your join behavior by heart

Specify join types explicitly — don't rely on defaults
Always use an ON clause — never accidental cartesian products
NULL never equals NULL in join conditions
Join on indexed columns for best performance

Relations & Connections

How join types relate to each other

INNER JOIN = intersection of both tables
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.col,

3 b.col

4FROM table_a a

5INNER JOIN table_b b

6 ON a.key = b.key;

INNER JOINOnly returns rows with matches in BOTH tables

ONThe condition that links the two tables

Aliases (a, b)Short names for tables — required when column names overlap

Sample Data

orders

12 rows

id	customer_id	total
5001	C1001	59.98
5002	C1002	449
5003	C1001	12.5
5004	C1003	89.99
5005	C1006	245
5006	C1005	159
5007	C1004	45.5
5008	C1009	79
5009	C1012	425
5010	C1007	18.99
5011	C1008	134
5012	C1010	65.5

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

products

10 rows

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

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

Worked Example

Show each order with the customer name and city.

SQL

1SELECT

2 o.id AS order_id,

3 c.name AS customer,

4 c.city,

5 o.total

6FROM orders o

7INNER JOIN customers c

8 ON o.customer_id = c.id

9ORDER BY

10 o.id;

For each order, the database finds the customer row where customers.id matches orders.customer_id. The result combines columns from both tables into a single row. Aliases o and c keep the query short.

Output

4 rows

order_id	customer	city	total
5001	Aarav Sharma	Mumbai	59.98
5002	Sara Chen	Singapore	449
5003	Aarav Sharma	Mumbai	12.5
5004	James Wilson	London	89.99

Key Concepts

1INNER JOIN = only rows with matches in BOTH tables survive

2No match on either side → row silently excluded

3Writing JOIN without INNER is the same — INNER is the default

4Always use table aliases when joining to keep queries readable

Pro Tip

Real data lives in separate tables. INNER JOIN is how you reassemble it — and it's the default when you write just JOIN.

When to Use

Linking orders to customer names, products to reviews, employees to departments, invoices to payments.

Challenge

Solve the problem below

Show each review with the product name. Display product name, rating, and comment. Order by rating descending.

Your Query

NULLs in Aggregations

LEFT JOIN