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.

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.

Joining Multiple Tables

You can chain multiple INNER JOINs together:

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:

-- 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;