MySQL INNER JOIN

Introduction

In this chapter, we will learn about the INNER JOIN in MySQL. An INNER JOIN returns only the rows that have matching values in both tables. It is the most common type of join used to retrieve data that is related between two tables. We will cover the syntax, examples, filtering with INNER JOIN, and joining multiple tables using INNER JOIN.

Syntax

The basic syntax for the INNER JOIN is:

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

• columns: The columns you want to retrieve.
• table1 and table2: The tables to join.
• common_column: The column that the tables share, used to match rows.

Example with INNER JOIN

Let’s go through an example where we use INNER JOIN to retrieve data from the students and departments tables.

1. Create a Database:

CREATE DATABASE school;

2. Select the Database:

USE school;

3. Create Tables:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE,
    department_id INT
);

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(100) NOT NULL
);

4. Insert Data into the Tables:

INSERT INTO students (first_name, last_name, email, enrollment_date, department_id) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01', 1),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02', 2),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03', 1),
('Neha', 'Verma', 'neha.verma@example.com', '2023-07-04', 3),
('Sahil', 'Mehta', 'sahil.mehta@example.com', '2023-07-05', 2);

INSERT INTO departments (department_name) VALUES
('Mathematics'),
('Science'),
('Literature');

5. Use the INNER JOIN:

SELECT students.first_name, students.last_name, departments.department_name
FROM students
INNER JOIN departments ON students.department_id = departments.id;

Output

Filtering with INNER JOIN

You can also filter the results of an INNER JOIN by adding a WHERE clause to specify additional conditions.

Example with Filtering

SELECT students.first_name, students.last_name, departments.department_name
FROM students
INNER JOIN departments ON students.department_id = departments.id
WHERE departments.department_name = 'Science';

Output

In this example, the query retrieves only those students who belong to the ‘Science’ department.

Multiple Table INNER JOIN

You can join more than two tables using multiple INNER JOIN statements.

Example with Multiple Tables

Let’s create an additional courses table and join it with the students and departments tables.

1. Create the Courses Table:

CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

2. Insert Data into the Courses Table:

INSERT INTO courses (course_name, department_id) VALUES
('Algebra', 1),
('Biology', 2),
('English Literature', 3);

3. Join Multiple Tables:

SELECT students.first_name, students.last_name, departments.department_name, courses.course_name
FROM students
INNER JOIN departments ON students.department_id = departments.id
INNER JOIN courses ON departments.id = courses.department_id;

Output

In this example, the query retrieves data from the students, departments, and courses tables, combining them based on the relationships between their columns.

Important Considerations

• Matching Values: The INNER JOIN returns rows only when there is a match in both tables. If there are rows in either table that do not have matching rows in the other table, those rows will not be included in the result.
• Performance: Joins can impact performance, especially on large datasets. Ensure appropriate indexing on the columns used in the join condition to optimize query performance.
• Column Naming: If the joined tables have columns with the same name, use table aliases or fully qualify the column names to avoid ambiguity.

Example with Table Aliases

SELECT s.first_name, s.last_name, d.department_name, c.course_name
FROM students s
INNER JOIN departments d ON s.department_id = d.id
INNER JOIN courses c ON d.id = c.department_id;

Conclusion

The INNER JOIN is used for retrieving related data from multiple tables in MySQL. This chapter covered the syntax and usage of the INNER JOIN, provided examples with the students, departments, and courses tables, and discussed filtering and joining multiple tables using INNER JOIN.