MySQL INNER JOIN

Return only matching rows from both tables

🔵 What is INNER JOIN?

INNER JOIN returns only the rows that have matching values in both tables. It's the most common type of join, filtering out any rows that don't have corresponding matches in the joined table. 


-- INNER JOIN example
SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

Output:

name order_date
John Doe 2024-01-15
Jane Smith 2024-01-20

INNER JOIN Key Features

✅

Matching Only

Returns rows with matches in both tables 

INNER JOIN table2 ON condition
🎯

Most Common

Default join type in most databases 

JOIN is same as INNER JOIN
🔗

Multiple Conditions

Can use AND/OR in ON clause 

ON a.id = b.id AND a.status = 'active'
📊

Reduces Results

Filters out non-matching rows 

Only matched rows appear

🔹 Basic INNER JOIN

The basic INNER JOIN syntax combines two tables and returns only rows where the join condition is met. This is perfect when you only want to see records that have relationships in both tables. For example, showing only customers who have placed orders, excluding customers without any orders from the results. 

-- Get customers and their orders
SELECT 
    c.customer_id,
    c.name,
    c.email,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Output:

customer_id name email order_id order_date total_amount
1 Alice Brown [email protected] 101 2024-01-15 250.00
2 Bob Wilson [email protected] 102 2024-01-20 180.50

🔹 INNER JOIN with Three Tables

You can chain multiple INNER JOINs to combine data from three or more tables. Each join must have its own ON condition specifying how that table relates to the others. This is useful for complex queries where data is normalized across multiple tables, like getting customer names, order details, and product information all in one query. 

-- Join customers, orders, and order_items
SELECT 
    c.name AS customer,
    o.order_date,
    oi.product_name,
    oi.quantity,
    oi.price
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id;

Output:

customer order_date product_name quantity price
Alice Brown 2024-01-15 Laptop 1 999.99
Bob Wilson 2024-01-20 Mouse 2 29.99

🔹 INNER JOIN with WHERE

Combining INNER JOIN with WHERE clauses allows you to filter the joined results based on specific criteria. The WHERE clause is applied after the join is performed, so you're filtering the combined result set. This gives you precise control over which matched rows appear in your final output, making your queries more targeted and efficient. 

-- Get high-value orders with customer details
SELECT 
    c.name,
    c.city,
    o.order_id,
    o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > 200
ORDER BY o.total_amount DESC;

Output:

name city order_id total_amount
Alice Brown New York 101 1250.00
Charlie Davis Chicago 103 450.75

🔹 INNER JOIN with Aggregate Functions

INNER JOIN works seamlessly with aggregate functions like COUNT, SUM, and AVG. When combined with GROUP BY, you can generate powerful summary reports from joined data. This is perfect for analytics queries like calculating total sales per customer, counting orders per product, or finding average order values by region from related tables. 

-- Count orders per customer
SELECT 
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

Output:

customer_id name total_orders total_spent
1 Alice Brown 5 2450.00
2 Bob Wilson 3 890.50

🧠 Test Your Knowledge

What does INNER JOIN return?