Performing Right Joins in SQL

A RIGHT JOIN in SQL is used to retrieve records from two tables, ensuring that all rows from the right table are included, even if there are no matching records in the left table. In other words, it returns all rows from the right table, and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table.

Right joins are the mirror image of left joins. While a left join returns all records from the left table, a right join returns all records from the right table and the matching rows from the left.

Syntax of RIGHT JOIN

The basic syntax of the RIGHT JOIN is as follows:

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

table1: The left table.
table2: The right table.
column_list: The columns you wish to select.
The join condition is specified using the ON clause, which matches rows based on a common field.

How RIGHT JOIN Works

Consider two tables: employees and departments.

employees Table

employee_id	name	department_id
1	Alice	10
2	Bob	20
3	Charlie	30
4	David	NULL

departments Table

department_id	department_name
10	Sales
20	HR
30	Engineering
40	Marketing

To perform a Right Join to see all departments, even those that have no employees, we write:

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

Explanation

We are joining the employees table on the departments table based on the department_id.
The query returns all rows from the departments table, regardless of whether there is a matching employee in the employees table.

Output

name	department_name
Alice	Sales
Bob	HR
Charlie	Engineering
David	Marketing

In this case, all the employees have a department assigned, so no NULL values are returned. Now let’s modify the employees table and see what happens when some employees are not assigned to a department.

Modified employees Table

employee_id	name	department_id
1	Alice	10
2	Bob	20
3	Charlie	NULL
4	David	NULL

Now, we run the same RIGHT JOIN query.

Output with NULL values

name	department_name
Alice	Sales
Bob	HR
NULL	Engineering
NULL	Marketing

The RIGHT JOIN ensures that all departments are returned.
Since Charlie and David are not assigned to a department (NULL values in department_id), no employee names are returned for Engineering and Marketing, and instead, NULL values are shown.

RIGHT JOIN vs LEFT JOIN

A common question is how RIGHT JOIN differs from LEFT JOIN. Let’s compare the two.

LEFT JOIN: Returns all rows from the left table and matched rows from the right table. Unmatched rows from the right table will return NULL values.
RIGHT JOIN: Returns all rows from the right table and matched rows from the left table. Unmatched rows from the left table will return NULL values.

The difference lies in which table’s rows are prioritized for inclusion in the result set. If you switch the tables in a LEFT JOIN, you get the effect of a RIGHT JOIN, and vice versa.

Example with LEFT JOIN (for comparison)

				
					SELECT employees.name, departments.department_name
FROM departments
LEFT JOIN employees
ON departments.department_id = employees.department_id;

This will give the same result as the RIGHT JOIN example but the tables have switched places.

Using WHERE Clause with RIGHT JOIN

You can add filtering conditions using a WHERE clause when performing a right join. For example, to retrieve only those departments where there are no employees, you can do this:

				
					SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id
WHERE employees.name IS NULL;

Output

name	department_name
NULL	Engineering
NULL	Marketing

This query retrieves all departments without any employees by filtering out NULL values for the employee names.

RIGHT JOIN with Multiple Tables

You can use a RIGHT JOIN with multiple tables to query more complex data structures. Suppose we have another table, projects, with details of employees working on different projects:

employee_id	name	department_id
1	Alice	10
2	Bob	20
3	Charlie	30
4	David	NULL

department_id	department_name
10	Sales
20	HR
30	Engineering
40	Marketing

projects Table

project_id	project_name	employee_id
101	Project Alpha	1
102	Project Beta	2
103	Project Gamma	3

In this case:

The projects table includes a project_id (unique identifier for each project), project_name (name of the project), and employee_id (which links the project to the employee working on it).

				
					SELECT employees.name, departments.department_name, projects.project_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id
RIGHT JOIN projects ON employees.employee_id = projects.employee_id;

Output

name	department_name	project_name
Alice	Sales	Project Alpha
Bob	HR	Project Beta
Charlie	Engineering	Project Gamma
NULL	Marketing	NULL

Explanation of the output:

Alice, Bob, and Charlie have matching department_id and employee_id with their respective departments and projects, so their names, departments, and project names are shown.
Since the RIGHT JOIN ensures that all departments (and projects) will be shown even if there is no employee associated, we see a NULL for the employee in the Marketing department because no employee is assigned to it. Similarly, there’s no project for this department, so NULL appears for the project_name.

If RIGHT JOIN is used with more tables or if the tables have more or fewer rows, the output may vary accordingly.

RIGHT JOIN is a valuable SQL tool when you need to retrieve all records from the right table and their matching records from the left table. It’s the opposite of the left join, providing flexibility in how you manage and query data across related tables. Happy coding !❤️

Performing Right Joins in SQL

Syntax of RIGHT JOIN

How RIGHT JOIN Works

employees Table

departments Table

Explanation

Output

Modified employees Table

Output with NULL values

RIGHT JOIN vs LEFT JOIN

Example with LEFT JOIN (for comparison)

Using WHERE Clause with RIGHT JOIN

Output

RIGHT JOIN with Multiple Tables

projects Table

Output

Explanation of the output:

Table of Contents

Explore

Popular Tutorials

Contact here