INNER JOIN in MySQL

Introduction

INNER JOIN helps you combine rows from two tables when they have matching values in a related column.

You want to see orders along with customer names from two tables.

You need to find employees and their departments where both exist.

You want to list products and their suppliers only if the supplier exists.

You want to match students with their enrolled courses.

You want to combine data from two tables based on a common ID.

Syntax

MySQL

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

INNER JOIN returns only rows where the join condition matches in both tables.

Use ON to specify the matching columns between the two tables.

Examples

Get customer names with their order IDs where customers have orders.

MySQL

SELECT customers.name, orders.order_id
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

List employees with their department names where the department exists.

MySQL

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

Show products and their suppliers only if the supplier is listed.

MySQL

SELECT products.product_name, suppliers.supplier_name
FROM products
INNER JOIN suppliers
ON products.supplier_id = suppliers.supplier_id;

Sample Program

This query joins customers with their orders and lists customer names with order IDs.

MySQL

CREATE TABLE customers (
  customer_id INT,
  name VARCHAR(50)
);

CREATE TABLE orders (
  order_id INT,
  customer_id INT
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO orders VALUES (101, 1), (102, 2), (103, 1);

SELECT customers.name, orders.order_id
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY orders.order_id;

Important Notes

If there is no match between tables, INNER JOIN excludes those rows.

You can join more than two tables by chaining INNER JOINs.

Always use meaningful aliases or full table names to avoid confusion.

Summary

INNER JOIN combines rows from two tables where the join condition matches.

It excludes rows without matching values in both tables.

Use INNER JOIN to find related data existing in both tables.