• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL INNER JOIN

← Previous Next →

In relational databases, it’s common for related data to be stored across multiple tables. For example, one table might hold customer details, while another stores their orders. To get complete information—like which customer placed which order—you need to combine data from both tables.

The MySQL INNER JOIN is one of the most frequently used join types for this purpose. It returns only those records that have matching values in both tables. If there’s no match, the row is excluded from the result.

What Is an INNER JOIN in MySQL?

An INNER JOIN retrieves records that have matching values in both tables involved in the join. The result will contain only the rows where the condition in the ON clause is satisfied.

In simpler words:

  • If a customer has placed an order, their record appears in the result.

  • If a customer has no order, that customer is excluded.

  • If an order exists without a customer (which is rare), it’s also excluded.

INNER JOIN Syntax

The basic syntax of an INNER JOIN looks like this:

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

Here’s what each part means:

  • SELECT columns → The columns you want to retrieve.

  • FROM table1 → The first table in the join.

  • INNER JOIN table2 → The second table you are joining.

  • ON condition → Defines how both tables are related.

Example Tables

Let’s take two sample tables — customers and orders.

customers

customer_id customer_name city
1 Ananya Delhi
2 Priya Mumbai
3 Isha Pune
4 Riya Kolkata

orders

order_id customer_id order_date amount
101 1 2025-01-12 500
102 2 2025-02-10 700
103 1 2025-03-15 350
104 3 2025-03-20 800

INNER JOIN Example

Let’s use INNER JOIN to display all customers who have placed at least one order.

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

Result:

customer_name order_id amount
Ananya 101 500
Priya 102 700
Ananya 103 350
Isha 104 800

Explanation:

  • The query matches rows where customers.customer_id equals orders.customer_id.

  • Only those customers who have placed at least one order appear.

  • Riya is missing because she hasn’t placed any order.

Using Table Aliases with INNER JOIN

When tables have long names, you can use aliases to make the query cleaner and more readable.

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

Note: The keyword AS is optional. You can also write:

FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id;

Aliases are especially helpful when you’re joining more than two tables.

INNER JOIN with Multiple Tables

You can join more than two tables using multiple INNER JOINs. Let’s introduce a third table called products.

products

product_id product_name price
201 Laptop 45000
202 Headphones 3000
203 Keyboard 1500

And a new table order_items that connects orders with products.

order_items

order_id product_id quantity
101 201 1
102 202 2
103 203 1
104 201 1

Now, let’s find customer names, their orders, and the product names they purchased.

SELECT c.customer_name, o.order_id, p.product_name, oi.quantity
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;

Result:

customer_name order_id product_name quantity
Ananya 101 Laptop 1
Priya 102 Headphones 2
Ananya 103 Keyboard 1
Isha 104 Laptop 1

Explanation:
This query connects four tables:

  1. customers with orders

  2. orders with order_items

  3. order_items with products

All rows are shown only where matching values exist in all joins.

INNER JOIN with WHERE Clause

You can add a WHERE condition to filter results. For example, to display only customers from Delhi who placed an order:

SELECT c.customer_name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
WHERE c.city = 'Delhi';

Result:

customer_name order_id amount
Ananya 101 500
Ananya 103 350

INNER JOIN with Aggregate Functions

You can use aggregate functions like SUM, COUNT, or AVG with INNER JOIN.
Let’s find the total order amount for each customer:

SELECT c.customer_name, SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

Result:

customer_name total_spent
Ananya 850
Priya 700
Isha 800

INNER JOIN vs LEFT JOIN

Feature INNER JOIN LEFT JOIN
Matching records Returns only matching rows from both tables Returns all rows from the left table and matching rows from the right table
Unmatched rows Excluded from the result Appear with NULL values
Result size Usually smaller Usually larger
Use case When you only need records that exist in both tables When you want all records from one table regardless of matches

Performance Tips

  • Use proper indexing on the columns you use in the ON condition (like customer_id).

  • Avoid joining large tables unnecessarily.

  • Make sure join columns have the same data type.

  • Use EXPLAIN to analyze query performance before executing it on large datasets.

  • Avoid wildcard selection (SELECT *) when joining multiple tables — always specify the needed columns.

Common Mistakes to Avoid

  1. Missing the ON condition:
    Forgetting the ON clause causes a CROSS JOIN, leading to a huge result set.

  2. Ambiguous columns:
    If both tables have the same column name, always prefix with the table alias (like c.customer_id).

  3. Joining wrong columns:
    Make sure your join condition connects related columns, not unrelated ones.

When to Use INNER JOIN

Use an INNER JOIN when:

  • You only need records that have matches in both tables.

  • You’re analyzing related data, such as customer–order, employee–department, or student–course relationships.

  • You want clean, filtered results without NULL values.

Summary of the Tutorial

The MySQL INNER JOIN is one of the most powerful and frequently used SQL operations. It helps you connect tables based on common fields and extract only the records that have matching data on both sides.

Once you understand how INNER JOIN works, combining multiple tables to build reports, summaries, and analytics becomes easy. It’s a must-know concept for every SQL developer and is the foundation for mastering more advanced joins like LEFT JOIN and RIGHT JOIN.

Practice Questions

  1. Write an SQL query to display the customer_name, order_id, and amount for all customers who have placed at least one order using an INNER JOIN.

  2. Write a query to find the total amount spent by each customer using SUM() with an INNER JOIN between customers and orders.

  3. Write an SQL query to show customer names and order dates for customers who live in Delhi using an INNER JOIN and a WHERE clause.

  4. Using aliases, write a query to join the customers and orders tables and show the customer_name, city, order_id, and amount.

  5. Suppose you have three tables — customers, orders, and order_items. Write an INNER JOIN query that lists the customer_name, order_id, and product_id.

  6. Write a query that joins orders, order_items, and products tables to display order_id, product_name, and quantity for all orders.

  7. Write an SQL query to find customers who have made multiple orders (more than one) using COUNT() with an INNER JOIN.

  8. Create an INNER JOIN query to list the customer_name, order_id, and amount for orders greater than 500.

  9. Write an INNER JOIN query between employees and departments tables to show each employee’s name along with their department name.

  10. Write a query using INNER JOIN to display all customers who have placed orders in March 2025, showing their names, order dates, and amounts.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top