SQL RIGHT JOIN

Introduction

In this chapter, we will focus on the RIGHT JOIN operation in SQL. The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side if there is no match. This chapter will cover the definition, syntax, and provide examples to help you understand how to use the RIGHT JOIN effectively.

What is a RIGHT JOIN?

The RIGHT JOIN keyword returns all records from the right table, and the matched records from the left table. If there is no match, the result is NULL on the side of the left table.

Syntax for RIGHT JOIN

Basic Syntax

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

• columns: The columns you want to retrieve.
• table1: The left table you want to join.
• table2: The right table you want to join.
• table1.column = table2.column: The condition that specifies how the tables should be joined.

Step-by-Step Example

Sample Tables

First, let's create two sample tables named employees and departments.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Insert Sample Data

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ravi', 'Verma', 'ravi.verma@example.com', NULL, 50000);

Using RIGHT JOIN

To retrieve all departments and their employees (including departments with no employees):

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

Output

first_name	last_name	department_name
Ramesh	Kumar	HR
Sita	Patel	Finance
Arjun	Singh	HR
Priya	Sharma	Finance
NULL	NULL	IT

In this example, all departments are included in the result set. Departments without a matching department_id in the employees table have NULL in the first_name and last_name columns.

Filtering with RIGHT JOIN

You can also use additional filtering conditions with RIGHT JOIN.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id
WHERE employees.salary > 55000 OR employees.salary IS NULL;

Output

first_name	last_name	department_name
Sita	Patel	Finance
Priya	Sharma	Finance
NULL	NULL	IT

In this example, only the employees with a salary greater than 55000 and departments without employees are included in the result set.

Using RIGHT JOIN with Aggregate Functions

You can also use aggregate functions with RIGHT JOIN to perform calculations on the joined tables.

SELECT departments.department_name, COUNT(employees.id) AS employee_count
FROM departments
RIGHT JOIN employees ON departments.department_id = employees.department_id
GROUP BY departments.department_name;

Output

department_name	employee_count
HR	2
Finance	2
IT	0

In this example, the query returns the number of employees in each department, including departments with no employees.

Multiple Table RIGHT JOIN

You can join more than two tables using RIGHT JOIN.

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO projects (project_id, project_name, department_id)
VALUES
(1, 'Project A', 1),
(2, 'Project B', 2),
(3, 'Project C', 3);

SELECT employees.first_name, employees.last_name, departments.department_name, projects.project_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id
RIGHT JOIN projects ON departments.department_id = projects.department_id;

Output

first_name	last_name	department_name	project_name
Ramesh	Kumar	HR	Project A
Arjun	Singh	HR	Project A
Sita	Patel	Finance	Project B
Priya	Sharma	Finance	Project B
NULL	NULL	IT	Project C

In this example, we join the employees, departments, and projects tables to retrieve employees along with their department names and project names.

Conclusion

The RIGHT JOIN is used for combining rows from two or more tables based on a related column and including all rows from the right table. This chapter covered the basic syntax for RIGHT JOIN, provided examples to illustrate its use, and demonstrated how to filter and join multiple tables. Understanding how to use RIGHT JOIN effectively will greatly enhance your ability to query and analyze data across multiple tables in a relational database. In the next chapter, we will explore FULL JOIN with examples.