MySQL LEFT JOIN

Introduction

In this chapter, we will learn about the LEFT JOIN in MySQL.

A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table. This is useful when you want to include all records from one table, regardless of whether there is a corresponding record in the other table.

We will cover the syntax, examples, and important considerations for using the LEFT JOIN.

Syntax

The basic syntax for the LEFT JOIN is:

SELECT columns
FROM table1
LEFT 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 LEFT JOIN

Let’s go through an example where we use LEFT 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', NULL);

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

5. Use the LEFT JOIN:

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

Output

Explanation

In the above example:

• The LEFT JOIN is used to combine rows from the students and departments tables.
• The ON clause specifies the condition for the join, which is that the department_id in the students table matches the id in the departments table.
• The result set includes all rows from the students table. For rows in the students table without a matching row in the departments table, the result is NULL for the columns from the departments table.

Filtering with LEFT JOIN

You can also filter the results of a LEFT 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
LEFT JOIN departments ON students.department_id = departments.id
WHERE departments.department_name IS NULL;

Output

In this example, the query retrieves only those students who do not belong to any department.

Multiple Table LEFT JOIN

You can join more than two tables using multiple LEFT 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),
('Physics', NULL);

3. Join Multiple Tables:

SELECT students.first_name, students.last_name, departments.department_name, courses.course_name
FROM students
LEFT JOIN departments ON students.department_id = departments.id
LEFT 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. The LEFT JOIN ensures that all students are included, even if they do not have matching departments or courses.

Important Considerations

• All Rows from Left Table: The LEFT JOIN returns all rows from the left table, regardless of whether there is a match in the right table.
• NULL Values: When there is no match, the result set will contain NULL for columns from the right table.
• 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
LEFT JOIN departments d ON s.department_id = d.id
LEFT JOIN courses c ON d.id = c.department_id;

Conclusion

The LEFT JOIN is used for retrieving related data from multiple tables in MySQL, ensuring that all rows from the left table are included in the result set. This chapter covered the syntax and usage of the LEFT JOIN, provided examples with the students, departments, and courses tables, and discussed filtering and joining multiple tables using LEFT JOIN.