SQL - INNER JOIN with Examples
INNER JOIN is the workhorse of relational database queries. It combines rows from two or more tables based on a related column, returning only the rows where the join condition finds a match in both...
Key Insights
- INNER JOIN returns only rows where matching values exist in both tables—unmatched rows are excluded entirely, making it ideal for queries where you need complete data relationships
- Always use table aliases and fully qualify column names to avoid ambiguity, improve readability, and prevent subtle bugs when joining tables with similarly named columns
- Index your join columns; without proper indexes, INNER JOINs on large tables will force full table scans and destroy query performance
Introduction to INNER JOIN
INNER JOIN is the workhorse of relational database queries. It combines rows from two or more tables based on a related column, returning only the rows where the join condition finds a match in both tables. No match? That row doesn’t appear in your results.
This behavior makes INNER JOIN the right choice when you need complete, related data. If you’re building an order report and need customer names alongside order details, you want INNER JOIN. Customers without orders and orders without customers (orphaned records) won’t pollute your results.
Compare this to LEFT JOIN, which returns all rows from the left table regardless of matches, or FULL OUTER JOIN, which returns everything from both tables. INNER JOIN is more restrictive by design. That restriction is a feature, not a limitation—it guarantees every row in your result set has valid data from all joined tables.
INNER JOIN Syntax
The syntax follows a predictable pattern:
SELECT
columns
FROM
table1
INNER JOIN
table2
ON
table1.column = table2.column;
Breaking this down:
- SELECT: The columns you want in your result set. Can be from either table.
- FROM: Your primary table (the “left” table in the join).
- INNER JOIN: Declares the second table and the join type.
- ON: The condition that determines which rows match. Usually compares a foreign key to a primary key.
The INNER keyword is technically optional in most database systems—JOIN alone defaults to INNER JOIN. But I recommend being explicit. When someone reads your query six months from now, they shouldn’t have to guess your intent.
-- These are equivalent, but the first is clearer
SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id;
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
Basic INNER JOIN Example
Let’s work with a realistic scenario. You have a customers table and an orders table:
-- customers table
-- id | name | email
-- 1 | Alice Chen | alice@example.com
-- 2 | Bob Martinez | bob@example.com
-- 3 | Carol White | carol@example.com
-- orders table
-- id | customer_id | order_date | total
-- 1 | 1 | 2024-01-15 | 150.00
-- 2 | 1 | 2024-02-20 | 89.99
-- 3 | 2 | 2024-01-22 | 245.50
To get customer names with their order details:
SELECT
customers.name,
customers.email,
orders.id AS order_id,
orders.order_date,
orders.total
FROM
customers
INNER JOIN
orders
ON
customers.id = orders.customer_id;
Result:
name | email | order_id | order_date | total
-------------|-------------------|----------|------------|-------
Alice Chen | alice@example.com | 1 | 2024-01-15 | 150.00
Alice Chen | alice@example.com | 2 | 2024-02-20 | 89.99
Bob Martinez | bob@example.com | 3 | 2024-01-22 | 245.50
Notice Carol White doesn’t appear. She has no orders, so there’s no match. That’s INNER JOIN doing exactly what it should.
INNER JOIN with Multiple Conditions
Sometimes a single column match isn’t enough. You can add multiple conditions to the ON clause using AND or OR operators.
Consider a scenario where you have regional warehouses and need to match orders to customers within the same region:
-- customers table now includes region
-- id | name | region
-- 1 | Alice Chen | west
-- 2 | Bob Martinez | east
-- 3 | Carol White | west
-- orders table includes fulfillment_region
-- id | customer_id | fulfillment_region | total
-- 1 | 1 | west | 150.00
-- 2 | 1 | east | 89.99
-- 3 | 2 | east | 245.50
To find orders fulfilled in the customer’s home region:
SELECT
c.name,
c.region AS customer_region,
o.id AS order_id,
o.fulfillment_region,
o.total
FROM
customers c
INNER JOIN
orders o
ON
c.id = o.customer_id
AND c.region = o.fulfillment_region;
Result:
name | customer_region | order_id | fulfillment_region | total
-------------|-----------------|----------|--------------------|-------
Alice Chen | west | 1 | west | 150.00
Bob Martinez | east | 3 | east | 245.50
Alice’s second order (fulfilled in east) doesn’t appear because it fails the region match. Both conditions must be true for a row to be included.
You can also use OR for more permissive matching, though this is less common:
-- Match if customer_id matches OR if it's a VIP order (total > 200)
SELECT *
FROM customers c
INNER JOIN orders o
ON c.id = o.customer_id
OR o.total > 200;
Be careful with OR conditions—they can produce unexpected row multiplication and performance issues.
Joining Multiple Tables
Real applications rarely stop at two tables. You can chain INNER JOINs to pull data from as many tables as needed.
Let’s add a products table and an order_items table:
-- products table
-- id | name | price
-- 1 | Widget Pro | 49.99
-- 2 | Gadget Plus | 79.99
-- 3 | Thingamajig | 29.99
-- order_items table
-- id | order_id | product_id | quantity
-- 1 | 1 | 1 | 2
-- 2 | 1 | 3 | 1
-- 3 | 2 | 2 | 1
-- 4 | 3 | 1 | 3
To build a complete order summary with customer name, product name, and quantities:
SELECT
c.name AS customer_name,
o.id AS order_id,
o.order_date,
p.name AS product_name,
oi.quantity,
p.price,
(oi.quantity * p.price) AS line_total
FROM
customers c
INNER JOIN
orders o ON c.id = o.customer_id
INNER JOIN
order_items oi ON o.id = oi.order_id
INNER JOIN
products p ON oi.product_id = p.id
ORDER BY
o.id, p.name;
Result:
customer_name | order_id | order_date | product_name | quantity | price | line_total
--------------|----------|------------|--------------|----------|-------|------------
Alice Chen | 1 | 2024-01-15 | Thingamajig | 1 | 29.99 | 29.99
Alice Chen | 1 | 2024-01-15 | Widget Pro | 2 | 49.99 | 99.98
Alice Chen | 2 | 2024-02-20 | Gadget Plus | 1 | 79.99 | 79.99
Bob Martinez | 3 | 2024-01-22 | Widget Pro | 3 | 49.99 | 149.97
Each INNER JOIN further filters the result set. If any link in the chain breaks (missing order item, missing product), that row disappears from results.
INNER JOIN with WHERE and ORDER BY
The ON clause defines how tables relate. The WHERE clause filters the joined results. Keep these concerns separate.
SELECT
c.name,
o.id AS order_id,
o.order_date,
o.total
FROM
customers c
INNER JOIN
orders o ON c.id = o.customer_id
WHERE
o.total > 100
AND o.order_date >= '2024-01-01'
ORDER BY
o.total DESC,
c.name ASC;
This query joins customers to orders, then filters to show only orders over $100 from 2024, sorted by total descending.
A common mistake is putting filter conditions in the ON clause. For INNER JOINs, this technically works but muddies your intent:
-- Don't do this
SELECT *
FROM customers c
INNER JOIN orders o
ON c.id = o.customer_id
AND o.total > 100; -- This is a filter, not a join condition
-- Do this instead
SELECT *
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.total > 100;
With INNER JOIN, both produce identical results. But with LEFT JOIN, the behavior differs significantly. Build good habits now.
Common Mistakes and Best Practices
Always use table aliases. Without them, queries become unreadable fast:
-- Hard to read
SELECT
customers.name,
orders.id,
orders.order_date,
order_items.quantity,
products.name
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;
-- Much better
SELECT
c.name AS customer_name,
o.id AS order_id,
o.order_date,
oi.quantity,
p.name AS product_name
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;
Qualify all column names. If both tables have an id column and you just write SELECT id, you’ll get an ambiguous column error. Always prefix with the table alias.
Index your join columns. This is non-negotiable for production databases. Without an index on orders.customer_id, the database must scan every row in the orders table for each customer. That’s O(n×m) complexity.
-- Create indexes on foreign key columns
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
Check your execution plan. Use EXPLAIN (PostgreSQL/MySQL) or EXPLAIN PLAN (Oracle) to verify your joins are using indexes. If you see “Seq Scan” or “Full Table Scan” on large tables, investigate.
INNER JOIN is fundamental. Master it, and you’ll write cleaner queries that perform well at scale. Get sloppy with it, and you’ll spend hours debugging missing data and slow reports.