INNER JOIN in SQL: Syntax, Examples, and Multi-Table Joins

INNER JOIN returns only the rows where there's a match in both tables. It's the most common join type and the default when you just write JOIN.

What Does INNER JOIN Do?

INNER JOIN pairs up rows from two tables based on a condition — usually equality between a foreign key and a primary key — and returns only the pairs that match. A customer with no orders produces no rows; an order with no customer produces no rows. If one customer has five orders, that customer appears five times, once per match.

Basic Syntax

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

Simple Example

Join customers with their orders:

SELECT
    customers.name,
    customers.email,
    orders.order_id,
    orders.total
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

This returns only customers who have placed orders. Customers without orders are excluded. To keep them (with NULLs for order columns), use a LEFT JOIN instead.

INNER JOIN vs WHERE Clause Join — Any Difference?

Functionally, no. The old "implicit join" — comma-separated tables filtered in WHERE — produces the same rows and typically the same execution plan as an explicit INNER JOIN. The difference is readability and safety: with explicit JOIN ... ON, forgetting the condition is a syntax error, while forgetting the WHERE filter silently produces a huge cartesian product.

-- Explicit INNER JOIN (modern, preferred)
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- Implicit WHERE-clause join (same result, older style)
SELECT c.name, o.total
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;

-- Danger: drop the WHERE and you get every customer x every order

Joining Multiple Tables

You can chain multiple INNER JOINs together. Each JOIN attaches one more table; the engine decides the actual execution order — see optimizing SQL joins for how to keep multi-join queries fast:

SELECT
    customers.name,
    orders.order_id,
    products.product_name,
    order_items.quantity,
    order_items.price
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id
WHERE orders.order_date >= '2024-01-01';

Using Table Aliases

Aliases make queries cleaner and easier to read:

SELECT
    c.name,
    c.email,
    o.order_id,
    o.order_date,
    o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total > 100
ORDER BY o.order_date DESC;

Self Joins

Join a table to itself to find relationships within the same table — more patterns in SQL self joins explained:

-- Find employees and their managers
SELECT
    emp.name as employee_name,
    mgr.name as manager_name
FROM employees emp
INNER JOIN employees mgr ON emp.manager_id = mgr.employee_id;

Common Use Cases

Find Active Users with Purchases

SELECT
    u.user_id,
    u.username,
    COUNT(p.purchase_id) as purchase_count,
    SUM(p.amount) as total_spent
FROM users u
INNER JOIN purchases p ON u.user_id = p.user_id
WHERE p.purchase_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY u.user_id, u.username;

Match Products with Categories

SELECT
    cat.category_name,
    prod.product_name,
    prod.price,
    prod.stock_quantity
FROM categories cat
INNER JOIN products prod ON cat.category_id = prod.category_id
WHERE prod.stock_quantity > 0
ORDER BY cat.category_name, prod.product_name;