MySQL — Inner Join

Basic INNER JOIN

SELECT * FROM users
INNER JOIN orders ON users.id = orders.user_id;

INNER JOIN with Aliases

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

INNER JOIN Examples

-- Users with orders
SELECT
    u.name,
    COUNT(o.id) as order_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Products in orders
SELECT
    p.name,
    SUM(oi.quantity) as total_sold
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id;

-- Multi-table join
SELECT
    u.name,
    p.name as product,
    oi.quantity,
    o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

INNER JOIN vs WHERE

-- These are equivalent
SELECT * FROM users
INNER JOIN orders ON users.id = orders.user_id;

SELECT * FROM users, orders
WHERE users.id = orders.user_id;

Mini Practice

Write MySQL code that:

1. Uses basic INNER JOIN
2. Joins with aliases
3. Joins multiple tables
4. Groups joined results

Up Next

Next: Learn about Left Join.