INNER JOIN: Matching Rows Across Tables

INNER JOIN is the most commonly used join type—it returns only the rows where matches exist in both tables. Think of it as the intersection of two datasets: "Show me customers who have orders" or "List products that have been sold." When you write JOIN without specifying the type, you're using an INNER JOIN. In this lesson, you'll master the syntax, understand when INNER JOINs are appropriate, learn to join on multiple conditions, and practice solving real-world data problems that require connecting related tables.

📚 What You'll Learn

• What INNER JOIN does and when to use it
• Complete INNER JOIN syntax and variations
• How to join tables on single and multiple conditions
• How to use table aliases effectively
• How to filter results with WHERE after joining
• How to sort and limit joined results
• Common INNER JOIN patterns and best practices
• How to troubleshoot join problems
• Performance considerations for joins

1. What INNER JOIN Does

Visual Representation

Table A          Table B
┌───────┐        ┌───────┐
│   A   │        │   B   │
│ ┌─────┼────────┼─────┐ │
│ │  INNER JOIN  │     │ │
│ └─────┼────────┼─────┘ │
│       │        │       │
└───────┘        └───────┘

INNER JOIN = Only the overlapping area (matching rows)

Example Dataset

customers
+-------------+---------------+------------------+
| customer_id | name          | email            |
+-------------+---------------+------------------+
| 1           | Alice Johnson | alice@email.com  |
| 2           | Bob Smith     | bob@email.com    |
| 3           | Carol Davis   | carol@email.com  |  ← No orders
+-------------+---------------+------------------+

orders
+----------+-------------+------------+-------------+
| order_id | customer_id | order_date | total       |
+----------+-------------+------------+-------------+
| 101      | 1           | 2024-01-15 | 299.99      |
| 102      | 1           | 2024-02-20 | 49.99       |
| 103      | 2           | 2024-03-10 | 199.99      |
+----------+-------------+------------+-------------+

INNER JOIN Result (only customers with orders):
+-------------+---------------+----------+------------+
| customer_id | name          | order_id | total      |
+-------------+---------------+----------+------------+
| 1           | Alice Johnson | 101      | 299.99     |
| 1           | Alice Johnson | 102      | 49.99      |
| 2           | Bob Smith     | 103      | 199.99     |
+-------------+---------------+----------+------------+

Carol is excluded because she has no orders (no match)

2. Basic INNER JOIN Syntax

Standard Form

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

Shorthand (JOIN is INNER JOIN by default)

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

-- "JOIN" alone means INNER JOIN

Complete Example

-- Get orders with customer information
SELECT 
    c.customer_id,
    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;

-- Result: Only customers who have placed orders

3. Using Table Aliases Effectively

Table aliases make join queries readable. Use short, meaningful aliases consistently throughout your query.

Without Aliases (Verbose)

-- ❌ Hard to read and maintain
SELECT 
    customers.customer_id,
    customers.name,
    orders.order_id,
    orders.total
FROM customers
INNER JOIN orders 
    ON customers.customer_id = orders.customer_id
WHERE customers.name LIKE 'A%'
ORDER BY orders.order_date DESC;

With Aliases (Clean)

-- ✅ Clean and readable
SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.name LIKE 'A%'
ORDER BY o.order_date DESC;

Alias Best Practices

• ✅ Use meaningful abbreviations (c for customers, o for orders)
• ✅ Be consistent across your query
• ✅ Use aliases for all tables in joins
• ✅ Qualify all column names with aliases
• ❌ Don't use single letters for multiple similar tables (u1, u2 is confusing)

4. Joining on Multiple Conditions

Sometimes you need multiple conditions in your join. Use AND to combine conditions in the ON clause.

Multiple Join Conditions

-- Join on multiple columns
SELECT 
    s.sale_id,
    s.product_code,
    s.region,
    p.product_name
FROM sales s
INNER JOIN products p 
    ON s.product_code = p.product_code
    AND s.region = p.available_region;

-- Both conditions must be true for rows to match

Join Condition vs WHERE Clause

-- Join condition: How tables relate
SELECT 
    c.name,
    o.order_id,
    o.total
FROM customers c
INNER JOIN orders o 
    ON c.customer_id = o.customer_id  -- JOIN condition
WHERE o.total > 100                   -- FILTER condition
    AND c.name LIKE 'A%';             -- FILTER condition

-- ON: Defines relationship between tables
-- WHERE: Filters the joined result

5. Selecting Columns from Joined Tables

Select Specific Columns

-- Choose only needed columns
SELECT 
    c.name,
    o.order_id,
    o.order_date,
    o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Use Column Aliases for Clarity

-- Rename columns for clarity
SELECT 
    c.name AS customer_name,
    c.email AS customer_email,
    o.order_id,
    o.order_date,
    o.total AS order_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Handle Duplicate Column Names

-- Both tables have 'name' column
SELECT 
    c.name AS customer_name,  -- Disambiguate
    p.name AS product_name,   -- Disambiguate
    o.order_id,
    o.quantity
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN products p ON o.product_id = p.product_id;

6. Filtering and Sorting Joined Results

WHERE Clause After JOIN

-- Filter joined results
SELECT 
    c.name,
    o.order_id,
    o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total > 100
    AND o.order_date >= '2024-01-01';

-- Only orders over $100 from 2024

ORDER BY with Joins

-- Sort by columns from either table
SELECT 
    c.name,
    o.order_date,
    o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY 
    c.name ASC,           -- Sort by customer name first
    o.order_date DESC;    -- Then by order date (newest first)

LIMIT with Joins

-- Get top 10 largest orders with customer names
SELECT 
    c.name,
    o.order_id,
    o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.total DESC
LIMIT 10;

7. Common INNER JOIN Patterns

Pattern 1: Product Catalog with Categories

-- List products with their category names
SELECT 
    p.product_id,
    p.name AS product_name,
    p.price,
    c.name AS category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
ORDER BY c.name, p.name;

Pattern 2: Employee-Department Lookup

-- Show employees with department information
SELECT 
    e.employee_id,
    e.name AS employee_name,
    e.position,
    e.salary,
    d.name AS department_name,
    d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 50000
ORDER BY d.name, e.name;

Pattern 3: Order Details with Customer Info

-- Complete order information
SELECT 
    o.order_id,
    o.order_date,
    c.name AS customer_name,
    c.email,
    c.phone,
    o.total,
    o.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'pending'
ORDER BY o.order_date ASC;

Pattern 4: Filter by Related Table Value

-- Find orders from customers in a specific country
SELECT 
    o.order_id,
    o.order_date,
    c.name AS customer_name,
    o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA'
    AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
ORDER BY o.order_date DESC;

8. Calculated Columns in Joins

Calculations Using Both Tables

-- Calculate discount amount
SELECT 
    c.name AS customer_name,
    o.order_id,
    o.subtotal,
    c.discount_percent,
    ROUND(o.subtotal * c.discount_percent / 100, 2) AS discount_amount,
    ROUND(o.subtotal * (1 - c.discount_percent / 100), 2) AS final_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.discount_percent > 0;

String Concatenation

-- Create full display names
SELECT 
    CONCAT(c.first_name, ' ', c.last_name) AS full_name,
    CONCAT(a.street, ', ', a.city, ', ', a.state) AS full_address,
    o.order_id,
    o.total
FROM customers c
INNER JOIN addresses a ON c.customer_id = a.customer_id
INNER JOIN orders o ON c.customer_id = o.customer_id;

Date Calculations

-- Calculate days since order
SELECT 
    c.name,
    o.order_id,
    o.order_date,
    DATEDIFF(CURRENT_DATE, o.order_date) AS days_since_order,
    o.status
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'pending'
    AND DATEDIFF(CURRENT_DATE, o.order_date) > 7;
-- Orders pending for more than a week

9. Troubleshooting Common INNER JOIN Problems

Problem 1: No Results Returned

-- Debug: Check each table separately
SELECT COUNT(*) FROM customers;  -- Has data?
SELECT COUNT(*) FROM orders;     -- Has data?

-- Check for matching values
SELECT DISTINCT customer_id FROM customers;
SELECT DISTINCT customer_id FROM orders;

-- Are there common customer_ids?

Problem 2: Duplicate Rows

-- This returns one row per order
SELECT 
    c.name,
    o.order_id,
    o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- If you want unique customers only, use DISTINCT
SELECT DISTINCT
    c.customer_id,
    c.name,
    c.email
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- Or use GROUP BY (covered in aggregation lesson)

Problem 3: Ambiguous Column Name

-- ❌ ERROR: Which 'name'?
SELECT name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- ✅ SOLUTION: Always qualify columns
SELECT 
    c.name AS customer_name,
    p.name AS product_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN products p ON o.product_id = p.product_id;

Problem 4: Wrong Join Condition

-- ❌ WRONG: Joining on wrong columns
SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON c.customer_id = o.order_id;  -- WRONG!
-- This joins customer IDs to order IDs (meaningless)

-- ✅ CORRECT: Join on foreign key relationship
SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

10. INNER JOIN Performance Considerations

Index Example

-- Create indexes on foreign keys
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_product_id ON orders(product_id);

-- Now joins on these columns will be much faster!

11. 🎯 Practice Exercises

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255),
    category_id INT,
    price DECIMAL(10, 2),
    stock INT
);

-- Task 1: All products with category names
SELECT 
    p.product_id,
    p.name AS product_name,
    p.price,
    p.stock,
    c.name AS category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
ORDER BY c.name, p.name;


-- Task 2: Only in-stock products
SELECT 
    p.product_id,
    p.name AS product_name,
    p.price,
    p.stock,
    c.name AS category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
WHERE p.stock > 0
ORDER BY c.name, p.name;


-- Task 3: Expensive products (over $100)
SELECT 
    p.product_id,
    p.name AS product_name,
    p.price,
    c.name AS category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
WHERE p.price > 100
ORDER BY p.price DESC;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255),
    country VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total DECIMAL(10, 2),
    status VARCHAR(20)
);

-- Task 1: Orders with customer full names
SELECT 
    o.order_id,
    o.order_date,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    o.total,
    o.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;


-- Task 2: USA customers, 2024 orders
SELECT 
    o.order_id,
    o.order_date,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    c.country,
    o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA'
    AND o.order_date >= '2024-01-01'
    AND o.order_date < '2025-01-01'
ORDER BY o.order_date DESC;


-- Task 3: Top 5 largest orders with emails
SELECT 
    o.order_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    c.email,
    o.order_date,
    o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.total DESC
LIMIT 5;

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    name VARCHAR(100),
    location VARCHAR(100),
    budget DECIMAL(12, 2)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(255),
    department_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE
);

-- Task 1: All employees with department info
SELECT 
    e.employee_id,
    e.name AS employee_name,
    e.salary,
    e.hire_date,
    d.name AS department_name,
    d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
ORDER BY d.name, e.name;


-- Task 2: High earners in well-funded departments
SELECT 
    e.employee_id,
    e.name AS employee_name,
    e.salary,
    d.name AS department_name,
    d.budget AS department_budget
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 80000
    AND d.budget > 500000
ORDER BY e.salary DESC;


-- Task 3: Recent hires (last 2 years)
SELECT 
    e.employee_id,
    e.name AS employee_name,
    e.hire_date,
    DATEDIFF(CURRENT_DATE, e.hire_date) AS days_employed,
    d.name AS department_name,
    d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.hire_date >= DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR)
ORDER BY e.hire_date DESC;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    loyalty_tier VARCHAR(20),  -- bronze, silver, gold
    country VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    subtotal DECIMAL(10, 2),
    tax DECIMAL(10, 2),
    shipping DECIMAL(10, 2),
    total DECIMAL(10, 2)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10, 2)
);

-- Challenge 1: Orders with customer info and item count
SELECT 
    o.order_id,
    o.order_date,
    c.name AS customer_name,
    c.email,
    c.loyalty_tier,
    COUNT(oi.order_item_id) AS total_items,
    o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.order_date, c.name, c.email, 
         c.loyalty_tier, o.total
ORDER BY o.order_date DESC;


-- Challenge 2: Gold tier customers, last 90 days
SELECT 
    o.order_id,
    o.order_date,
    c.name AS customer_name,
    c.email,
    o.subtotal,
    o.tax,
    o.shipping,
    o.total,
    DATEDIFF(CURRENT_DATE, o.order_date) AS days_ago
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.loyalty_tier = 'gold'
    AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
ORDER BY o.order_date DESC;


-- Challenge 3: Top 10 customers by spending
SELECT 
    c.customer_id,
    c.name AS customer_name,
    c.email,
    c.loyalty_tier,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(o.total) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email, c.loyalty_tier
ORDER BY total_spent DESC
LIMIT 10;

📝 Key Takeaways

• INNER JOIN returns only rows with matches in both tables
• JOIN without a type defaults to INNER JOIN
• Use table aliases to keep queries clean and readable
• The ON clause defines how tables relate
• WHERE filters the joined result set
• Always qualify column names with table aliases
• Use column aliases to clarify output
• INNER JOIN excludes non-matching rows from results
• Join on foreign key → primary key relationships
• You can join on multiple conditions with AND
• Index foreign keys for better join performance
• ORDER BY and LIMIT work with joined results
• Check for ambiguous column names when both tables have same column
• Use calculated columns with data from both tables
• INNER JOIN is the most common join type in practice