MySQL INNER JOIN 

In MySQL, the INNER JOIN is one of the most frequently used joins to obtain data from multiple tables based upon a specified joining condition. This type of JOIN is important for relational databases, as it allows data to be retrieved efficiently when joining relevant information from different tables. It helps maintain data integrity by ensuring that only matching records from both tables are returned. Moreover, INNER JOINs are essential for optimizing query performance in normalized database designs, where data is split across multiple related tables. In this blog, let us explore the INNER JOIN in MySQL in detail with examples.

Table of Contents:

• What is an INNER JOIN in MySQL?
• Why Do We Need an INNER JOIN in MySQL?
• How to Perform an INNER JOIN with Multiple Tables in MySQL?
• How to Perform an INNER JOIN with a WHERE Clause in MySQL?
• How to Perform an INNER JOIN with an ORDER BY Clause in MySQL?
• Difference Between INNER JOIN with WHERE and INNER JOIN with ORDER BY
• Difference Between INNER JOIN and Other JOINS
• Common Mistakes in INNER JOIN and How to Avoid Them?
• Real-world Examples
• Best Practices
• Conclusion

Before getting started with the INNER JOIN, let us create some tables for better understanding.

Now, let’s create a Student table.

CREATE TABLE Students (

    student_id INT PRIMARY KEY,

    student_name VARCHAR(100),

    email VARCHAR(100)

);

INSERT INTO Students (student_id, student_name, email) VALUES

(1, 'John', '[email protected]'),

(2, 'Smith', '[email protected]'),

(3, 'Johnson', '[email protected]'),

(4, 'Wilson', '[email protected]');

SELECT * FROM Students;

This is how the Students table looks after inserting values.

Now, let’s create a Course table and insert some values into it

CREATE TABLE Courses (

    course_id INT PRIMARY KEY,

    course_name VARCHAR(100),

    instructor VARCHAR(100)

);

INSERT INTO Courses (course_id, course_name, instructor) VALUES

(101, 'SQL for Beginners', 'Michael Brown'),

(102, 'Advanced MySQL', 'Sarah Johnson'),

(103, 'Data Science with Python', 'Emily Davis'),

(104, 'Big Data Analytics', 'James Miller');

SELECT * from Courses;

This is how the Courses table looks once created and inserted with the values.

Finally, let’s create an enrollment table

CREATE TABLE Enrollments (

enrollment_id INT PRIMARY KEY,

    student_id INT,

    course_id INT,

    enrollment_date DATE,

    FOREIGN KEY (student_id) REFERENCES Students(student_id),

    FOREIGN KEY (course_id) REFERENCES Courses(course_id)

);

INSERT INTO Enrollments (enrollment_id, student_id, course_id, enrollment_date) VALUES

(1, 1, 101, '2024-01-15'),

(2, 1, 102, '2024-02-10'),

(3, 2, 103, '2024-02-20'),

(4, 3, 101, '2024-03-05'),

(5, 3, 104, '2024-03-10'),

(6, 4, 102, '2024-03-15');

SELECT * from Enrollments;

This is how the Enrollments table looks once created and inserted with the values.

What is an INNER JOIN in MySQL?

INNER JOIN is a type of SQL join that retrieves records from two or more tables that have a matching value in the specified columns. It returns only those records where the joined records meet the specified join condition.

Syntax:

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.common_column = table2.common_column;



Parameters:

• Table 1 and Table 2: Two tables that have to be joined.
• Common_column: Used to match the two tables

Example 1:

— Using an INNER JOIN between the Student and Enrollment Tables

SELECT s.student_id, s.student_name, e.course_id

FROM Students s

INNER JOIN Enrollments e ON s.student_id = e.student_id;

Output:

Explanation: Here, the INNER JOIN matches student_id from the student’s table with student_id from the enrollments table. Students not enrolled in any course will be excluded from the result set.

Example 2:

SELECT c.course_id, c.course_name, c.instructor, e.enrollment_id, e.student_id, e.enrollment_date

FROM Courses c

INNER JOIN Enrollments e ON c.course_id = e.course_id;

Output:

Explanation: Here, the INNER JOIN joins the Course and Enrollment table based on course_id which retrieves the details about courses that have enrollment.

Why Do We Need an INNER JOIN in MySQL?

Retrieve Related Data:

• Data is stored in separate tables in relational databases to make the storage efficient and organized. 
• Example: We can use INNER JOIN to merge related records – students and their enrollment records, and customers and their order records. 

Avoid Duplicate Data:

• We can normalize the data into many tables instead of keeping it in a single table
• INNER JOIN retrieves the data we need dynamically without wasting storage and will not duplicate any data.

Filtering Data:

• INNER JOIN means that only records from both tables that match each other will be returned. 
• Unlike a LEFT JOIN or RIGHT JOIN, an INNER JOIN will exclude any rows from both tables that do not match.

Increases Query Performance:

• This leads to reduced processing of the data, as the operations use less data because matching records were left out.
• INNER JOIN queries can be optimized using an index, which leads to an increase in performance

How to Perform an INNER JOIN with Multiple Tables in MySQL?

INNER JOIN is capable of combining data from multiple tables by joining on shared columns. Data from INNER JOIN includes only those rows with matching values in all the tables that have been joined.

Syntax:

SELECT column_name(s)

FROM table1

INNER JOIN table2 ON table1.common_column = table2.common_column

INNER JOIN table3 ON table2.common_column = table3.common_column;

Example:

-- Query to get the student name from the Students table, the course name from the Course table, and the date from the enrollment table



SELECT s.student_name, c.course_name, e.enrollment_date



FROM Students s



INNER JOIN Enrollments e ON s.student_id = e.student_id



INNER JOIN Courses c ON e.course_id = c.course_id;

Output:

Explanation: Here, the Students table joins the Enrollments table using student_id, and the Enrollments table joins the Courses table on course_id. Only matching records for student names, enrolled courses, and enrollment dates are returned using the join.

How to Perform an INNER JOIN with a WHERE Clause in MySQL?

Only relevant information is retrieved by using an INNER JOIN with the WHERE clause to filter the joined records according to specific conditions.

Syntax:

SELECT column_name(s)

FROM table1

INNER JOIN table2 ON table1.common_column = table2.common_column

WHERE condition;

Example:

-- To get the students who have enrolled after ‘2024-03-01’

SELECT s.student_name, c.course_name, e.enrollment_date

FROM Students s

INNER JOIN Enrollments e ON s.student_id = e.student_id

INNER JOIN Courses c ON e.course_id = c.course_id

WHERE e.enrollment_date > '2024-03-01';

Output:

Explanation: Here, the INNER JOIN retrieves all the matching records from three tables. The WHERE Clause filters the students enrolled after 2024-03-01.

How to Perform an INNER JOIN with an ORDER BY Clause in MySQL?

Using the ORDER BY clause and an INNER JOIN, the retrieved records are sorted according to the specified column either in ascending (ASC) or descending (DESC) order.

Syntax:

SELECT column_name(s)

FROM table1

INNER JOIN table2 ON table1.common_column = table2.common_column

ORDER BY column_name [ASC|DESC];

Example:

-- To retrieve course enrollment details sorted by enrollment date.

SELECT s.student_name, c.course_name, e.enrollment_date

FROM Students s

INNER JOIN Enrollments e ON s.student_id = e.student_id

INNER JOIN Courses c ON e.course_id = c.course_id

ORDER BY e.enrollment_date DESC;

Output:

Explanation: Here, the ORDER BY arranges the records by enrollment_date in descending (DESC) order to display the most recent enrollments first, INNER JOIN retrieves matching records from all three tables.

Difference Between INNER JOIN with WHERE and INNER JOIN with ORDER BY

Difference Between INNER JOIN and Other JOINS

Common Mistakes in INNER JOIN and How to Avoid Them?

1. Missing the ON Condition

Mistake: Missing the ON condition

SELECT s.student_name, c.course_name  

FROM Students s  

INNER JOIN Enrollments e  

INNER JOIN Courses c;

Solution: Always use the ON condition in JOINS to prevent the Cartesian product

SELECT s.student_name, c.course_name  

FROM Students s  

INNER JOIN Enrollments e ON s.student_id = e.student_id  

INNER JOIN Courses c ON e.course_id = c.course_id;



2. Using WHERE instead of ON Condition

Mistake: Using WHERE instead of ON to join tables

SELECT s.student_name, c.course_name  

FROM Students s, Enrollments e, Courses c  

WHERE s.student_id = e.student_id  

AND e.course_id = c.course_id;

SELECT s.student_name, c.course_name  

FROM Students s  

INNER JOIN Enrollments e ON s.student_id = e.student_id  

INNER JOIN Courses c ON e.course_id = c.course_id;

Solution: Always use the ON condition for joining tables and the WHERE Clause for filtering records

SELECT s.student_name, c.course_name  

FROM Students s  

INNER JOIN Enrollments e ON s.student_id = e.student_id  

INNER JOIN Courses c ON e.course_id = c.course_id;

3. Not using aliases when dealing with multiple tables

Mistake: Missing the alias when dealing with multiple tables leads to confusion 

SELECT student_name, course_name  

FROM Students  

INNER JOIN Enrollments ON Students.student_id = Enrollments.student_id  

INNER JOIN Courses ON Enrollments.course_id = Courses.course_id;

Solution: Use alias names 

SELECT s.student_name, c.course_name  

FROM Students s  

INNER JOIN Enrollments e ON s.student_id = e.student_id  

INNER JOIN Courses c ON e.course_id = c.course_id;

4. Not handling the duplicate records properly

Mistake: Selecting the student names may result in duplicates if a student is enrolled in more than one course.

SELECT s.student_name  

FROM Students s  

INNER JOIN Enrollments e ON s.student_id = e.student_id;

Solution: Use the DISTINCT keyword to remove duplicates

SELECT DISTINCT s.student_name  

FROM Students s  

INNER JOIN Enrollments e ON s.student_id = e.student_id;

Real-world Examples

1. HR System: Consider an HR wants to list all the employees with their department names.

Example:

CREATE TABLE Employees (

    employee_id INT PRIMARY KEY,

    employee_name VARCHAR(50),

    department_id INT

);

CREATE TABLE Departments (

    department_id INT PRIMARY KEY,

    department_name VARCHAR(50)

);

INSERT INTO Employees VALUES (1, 'Rohit', 101), (2, 'Vinoth', 102), (3, 'Babu', 101);

INSERT INTO Departments VALUES (101, 'HR'), (102, 'IT'), (103, 'Finance');

SELECT e.employee_name, d.department_name  

FROM Employees e  

INNER JOIN Departments d ON e.department_id = d.department_id;

Output:

Explanation: Here, the INNER JOIN fetches only employees with matching departments. The Finance department is not shown since it does not have any employees belonging to that department.

Best Practices

• Use WHERE to Filter, Not HAVING, Unless Working with Aggregates: Use the WHERE clause to filter data that has not been aggregated, and use HAVING to filter only after an aggregate.
• Utilize Explicit INNER JOIN Syntax: It is advisable to use the INNER JOIN operator instead of the WHERE clause, both to increase readability and to improve maintainability. 
• Ensure Proper Indexing Exists: For better query performance, index the columns in the ON clause (especially for foreign keys) for better execution speed. 
• Use Table Aliases: Use short and appropriate aliases of tables to simplify the query and increase readability.
• Ensure You Are Not Creating a Cartesian Product: Always ensure you have conditions included in the ON clause or WHERE clause so that an inadvertent Cartesian Product doesn’t multiply the number of rows returned in the result set.

Conclusion

The INNER JOIN in SQL is necessary to retrieve relevant data found across multiple tables. INNER JOINs are common in real-world applications such as managing employee records, maintaining customer orders, or managing student enrollment. Mastering INNER JOIN gives the foundation for understanding more advanced joins and relational database operations, making your SQL skills more efficient. In this blog, you have gained knowledge on performing an INNER JOIN in MySQL.

Take your skills to the next level by enrolling in our SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with our SQL interview questions, prepared by industry experts.

MySQL INNER JOIN – FAQs

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