INNER JOIN

INNER JOIN Syntax

The INNER JOIN returns only rows that have matching values in both tables. It's the most common type of join.

Basic Syntax

SELECT column1, column2
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

Sample Schema

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    city VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers (name, email, city)
VALUES
    ('Alice Smith', 'alice@email.com', 'New York'),
    ('Bob Johnson', 'bob@email.com', 'London'),
    ('Charlie Brown', 'charlie@email.com', 'Paris'),
    ('Diana Lee', 'diana@email.com', 'Tokyo');

INSERT INTO orders (customer_id, order_date, total)
VALUES
    (1, '2024-01-15', 250.00),
    (1, '2024-02-20', 175.50),
    (2, '2024-01-18', 320.00),
    (3, '2024-02-01', 89.99);

INNER JOIN Examples

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

-- Result:
-- Only customers with orders appear
-- Diana Lee is excluded (no orders)

How INNER JOIN Works

customers                    orders
+----+--------+             +----+----+--------+
| id | name   |             | id | cid| total  |
+----+--------+             +----+----+--------+
| 1  | Alice  | ----------> | 1  | 1  | 250.00 |
| 2  | Bob    | ----------> | 3  | 2  | 320.00 |
| 3  | Charlie| ----------> | 4  | 3  | 89.99  |
| 4  | Diana  |    (no match)  | 2  | 1  | 175.50 |
+----+--------+             +----+----+--------+

Result: Alice (2 rows), Bob (1 row), Charlie (1 row)
Diana excluded: no matching orders

Multiple JOIN Conditions

-- Join on multiple columns
SELECT 
    c.name,
    o.order_id,
    o.order_date
FROM customers c
INNER JOIN orders o 
    ON c.customer_id = o.customer_id
    AND o.order_date >= '2024-01-01';

-- Join with additional WHERE conditions
SELECT 
    c.name,
    o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total > 100;

INNER JOIN Best Practices

1. Always specify the join condition
2. Use table aliases for readability
3. Be explicit about which columns you need
4. Consider performance with large tables

-- Good: Clear and explicit
SELECT 
    c.name AS customer_name,
    c.email,
    o.order_id,
    o.total AS order_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC;

-- Bad: SELECT * with no conditions
SELECT * FROM customers INNER JOIN orders;

INNER JOIN is fundamental for combining data from multiple related tables.

Join Conditions

The ON clause specifies how tables are related. It defines the matching criteria between the tables.

Join Condition Types

-- Equality join (most common)
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- Non-equality join
SELECT e.name, e.salary, g.grade
FROM employees e
INNER JOIN salary_grades g 
    ON e.salary BETWEEN g.min_salary AND g.max_salary;

-- Self-referencing join
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id;

Multiple Join Conditions

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

-- OR conditions (use parentheses)
SELECT 
    c.name,
    o.order_id
FROM customers c
INNER JOIN orders o 
    ON c.customer_id = o.customer_id
    AND (o.total > 200 OR o.order_date >= '2024-02-01');

Join with Expressions

-- Join on calculated values
SELECT 
    c.name,
    o.order_id,
    o.total * 0.1 AS tax
FROM customers c
INNER JOIN orders o 
    ON c.customer_id = o.customer_id;

-- Join on date functions
SELECT 
    c.name,
    o.order_id
FROM customers c
INNER JOIN orders o 
    ON c.customer_id = o.customer_id
    AND YEAR(o.order_date) = 2024;

Join Conditions vs WHERE

-- Join condition in ON
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total > 100;

-- Additional condition in WHERE
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total > 100 AND c.city = 'New York';

-- For INNER JOIN, ON and WHERE are equivalent for filtering
-- But for LEFT/RIGHT JOIN, they behave differently

Join Condition Best Practices

1. Use primary key - foreign key relationships
2. Keep conditions in ON for outer joins
3. Use WHERE for additional filtering
4. Index join columns for performance

-- Good: Clear join condition
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total > 100;

-- Also good: Additional conditions in ON (for outer joins)
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o 
    ON c.customer_id = o.customer_id
    AND o.total > 100;  -- Keeps all customers, filters orders

Proper join conditions ensure correct results and optimal performance.

Table and Column Aliases

Aliases provide temporary names for tables and columns. They make queries shorter and more readable.

Table Aliases

-- Without aliases (verbose)
SELECT customers.name, customers.email, orders.total
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

-- With aliases (concise)
SELECT c.name, c.email, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- AS keyword (optional)
SELECT c.name, c.email, o.total
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id;

Column Aliases

-- Rename columns in result
SELECT 
    c.name AS customer_name,
    c.email AS contact_email,
    o.total AS order_total,
    o.total * 0.1 AS tax_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- Aliases for aggregate columns
SELECT 
    c.name,
    COUNT(o.order_id) AS order_count,
    SUM(o.total) AS total_spent,
    AVG(o.total) AS avg_order
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

Aliases in ORDER BY and GROUP BY

-- Use aliases in ORDER BY
SELECT 
    c.name,
    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
ORDER BY total_spent DESC;  -- Using alias

-- Using column position
SELECT c.name, SUM(o.total)
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY 1  -- Using position
ORDER BY 2 DESC;

Aliases in Subqueries

-- Subquery aliases are required
SELECT * FROM (
    SELECT c.name, 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
) AS customer_totals
WHERE total_spent > 500;

Alias Best Practices

1. Use meaningful aliases - c for customers, o for orders
2. Be consistent throughout the query
3. Avoid reserved words as aliases
4. Use AS for clarity (optional but readable)

-- Good: Consistent and meaningful aliases
SELECT 
    c.name AS customer_name,
    c.email,
    o.order_id,
    o.total AS order_total,
    p.name AS product_name
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
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.total > 100
ORDER BY o.order_date DESC;

-- Bad: Inconsistent aliases
SELECT 
    a.name,  -- Why 'a'?
    b.total  -- Why 'b'?
FROM customers a
INNER JOIN orders b ON a.id = b.cid;

Aliases make complex queries with multiple tables much more manageable.

Practical INNER JOIN Examples

Here are real-world examples of INNER JOIN queries.

E-Commerce Example

-- Schema
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Get order details with product information
SELECT 
    o.order_id,
    o.order_date,
    p.name AS product_name,
    p.category,
    oi.quantity,
    p.price,
    oi.quantity * p.price AS line_total
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.customer_id = 1;

HR Example

-- Schema
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    dept_id INT,
    manager_id INT
);

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100),
    location VARCHAR(50)
);

-- Employee with department info
SELECT 
    e.name AS employee,
    d.dept_name AS department,
    d.location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- Employee with manager name
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.emp_id;

Reporting Example

-- Monthly sales report
SELECT 
    DATE_FORMAT(o.order_date, '%Y-%m') AS month,
    p.category,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.quantity) AS items_sold,
    SUM(oi.quantity * p.price) AS revenue
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m'), p.category
ORDER BY month, revenue DESC;

Multiple Table Join

-- Complete order information
SELECT 
    c.name AS customer,
    o.order_id,
    o.order_date,
    p.name AS product,
    oi.quantity,
    p.price,
    oi.quantity * p.price AS total,
    e.name AS processed_by
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
INNER JOIN products p ON oi.product_id = p.product_id
LEFT JOIN employees e ON o.processed_by = e.emp_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY o.order_date DESC;

Performance Tips

-- Add indexes on join columns
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

-- Use EXPLAIN to check query plan
EXPLAIN SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- Filter early for better performance
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01';  -- Filter before join

INNER JOIN is the foundation for combining data from multiple tables in relational databases.