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?
• MySQL INNER JOIN with Multiple Tables
• Using WHERE Clause with MySQL INNER JOIN
• Sort INNER JOIN Results Using ORDER BY in MySQL
• INNER JOIN with WHERE vs ORDER BY: Key Differences
• INNER JOIN vs LEFT, RIGHT, FULL JOIN: Comparison Table
• Common MySQL INNER JOIN Mistakes and How to Fix Them?
• Real-World Examples of INNER JOIN in MySQL
• MySQL INNER JOIN Best Practices for Beginners
• Conclusion

Before getting started with the INNER JOIN, let us create a sample dataset 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. The INNER JOIN in SQL will help you to join two tables.

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

MySQL INNER JOIN 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 MySQL INNER JOIN example 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 MySQL INNER JOIN example joins the Course and Enrollment tables 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 in SQL 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 in SQL means that only records from both tables that match each other will be returned. 
• Unlike an INNER JOIN vs LEFT JOIN in MySQL or RIGHT JOIN, the MySQL 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

MySQL INNER JOIN with Multiple Tables

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. MySQL join with multiple tables can be used efficiently

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;

MySQL INNER JOIN Syntax:

-- 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.

Using the WHERE Clause with MySQL INNER JOIN

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

INNER JOIN 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.

Sort INNER JOIN Results Using ORDER BY in MySQL

Using the ORDER BY clause and an INNER JOIN in SQL, 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 in SQL retrieves matching records from all three tables.

INNER JOIN with WHERE vs ORDER BY: Key Differences

INNER JOIN vs LEFT, RIGHT, FULL JOIN: Comparison Table

Common MySQL INNER JOIN Mistakes and How to Fix Them?

There are some common errors in MySQL INNER JOIN while working on 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 of INNER JOIN in MySQL

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 in SQL fetches only employees with matching departments. The Finance department is not shown since it does not have any employees belonging to that department.

MySQL INNER JOIN Best Practices for Beginners

These are the MySQL INNER JOIN best practices:

1. 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.
2. Utilize Explicit INNER JOIN Syntax: It is advisable to use the INNER JOIN in SQL operators instead of the WHERE clause, both to increase readability and to improve maintainability. 
3. Ensure Proper Indexing Exists: For better query performance, index the columns in the ON clause (especially for foreign keys) for better execution speed. 
4. Use Table Aliases: Use short and appropriate aliases of tables to simplify the query and increase readability.
5. 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 in SQL 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;