MySQL INNER JOIN

The INNER JOIN returns only the rows where there is a matching value in both tables. Rows that do not have a match in either table are excluded from the result. It is the most commonly used type of JOIN in MySQL.

How INNER JOIN Works

MySQL compares each row in the first table with every row in the second table. Only the rows where the join condition is true appear in the result. All non-matching rows are discarded.

Syntax

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

Sample Tables

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

INSERT INTO customers VALUES (1, 'Anita Roy');
INSERT INTO customers VALUES (2, 'Suresh Mehta');
INSERT INTO customers VALUES (3, 'Priya Nair');
INSERT INTO customers VALUES (4, 'Vikram Rao');

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_item VARCHAR(100),
    customer_id INT
);

INSERT INTO orders VALUES (101, 'Laptop', 1);
INSERT INTO orders VALUES (102, 'Phone', 2);
INSERT INTO orders VALUES (103, 'Tablet', 1);
INSERT INTO orders VALUES (104, 'Headphones', 5);

Note: Customer ID 5 in orders does not exist in customers. Customer ID 4 (Vikram Rao) has no orders.

Example: INNER JOIN

SELECT customers.customer_name, orders.order_item
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

Result:

customer_name | order_item
--------------+-----------
Anita Roy     | Laptop
Anita Roy     | Tablet
Suresh Mehta  | Phone

Vikram Rao (no orders) and order 104 (no matching customer) are both excluded.

INNER JOIN with Aliases

SELECT c.customer_name, o.order_id, o.order_item
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id;

INNER JOIN with WHERE

SELECT c.customer_name, o.order_item
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_item = 'Laptop';

Result:

customer_name | order_item
--------------+-----------
Anita Roy     | Laptop

INNER JOIN Across Three Tables

CREATE TABLE payments (
    payment_id INT PRIMARY KEY,
    order_id INT,
    amount DECIMAL(10,2)
);

INSERT INTO payments VALUES (1, 101, 55000.00);
INSERT INTO payments VALUES (2, 102, 18000.00);

SELECT c.customer_name, o.order_item, p.amount
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
INNER JOIN payments AS p ON o.order_id = p.order_id;

Result:

customer_name | order_item | amount
--------------+------------+---------
Anita Roy     | Laptop     | 55000.00
Suresh Mehta  | Phone      | 18000.00

Key Points

• INNER JOIN returns only rows with matching values in both tables.
• Rows without a match in either table are excluded from the result.
• The ON clause defines the matching condition between tables.
• Multiple tables can be joined in a single query using chained INNER JOIN clauses.
• Table aliases keep the query readable when multiple tables are involved.