MySQL RIGHT JOIN

Introduction

In this chapter, we will learn about the RIGHT JOIN in MySQL. A RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the side of the left 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 RIGHT JOIN.

Syntax

The basic syntax for the RIGHT JOIN is:

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

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

5. Use the RIGHT JOIN:

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

Output

Explanation

In the above example:

• The RIGHT 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 departments table. For rows in the departments table without a matching row in the students table, the result is NULL for the columns from the students table.

Filtering with RIGHT JOIN

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

Output

In this example, the query retrieves only those departments that do not have any students enrolled.

Multiple Table RIGHT JOIN

You can join more than two tables using multiple RIGHT 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),
('History 101', 4);

3. Join Multiple Tables:

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

Important Considerations

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

Conclusion

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