SQL RIGHT JOIN

The RIGHT JOIN (or RIGHT OUTER JOIN) in SQL is used to combine rows from two or more tables. It returns all the rows from the right table (the second table) and the matching rows from the left table (the first table). If there is no match, the result will contain NULL values for columns from the left table.

 

Syntax of RIGHT JOIN

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

• table1: Left table in the join.
• table2: Right table in the join.
• column: The column that links the two tables.

 

Example

Consider the following two tables:

Employees Table

Projects Table

In this case, the project with project_id = 103 does not have an assigned employee.

To get a list of projects and the employees assigned to them, using RIGHT JOIN:

SELECT projects.project_name, employees.first_name, employees.last_name
FROM employees
RIGHT JOIN projects
ON employees.employee_id = projects.employee_id;

Output:

In this result:

• The RIGHT JOIN includes all projects from the projects table.
• It returns matching employee names where the employee_id matches in the employees table.
• Since Project C does not have an employee assigned, the first_name and last_name are NULL.

 

RIGHT JOIN with WHERE Clause

You can filter the results using a WHERE clause after performing a RIGHT JOIN. For example:

SELECT projects.project_name, employees.first_name, employees.last_name
FROM employees
RIGHT JOIN projects ON employees.employee_id = projects.employee_id
WHERE employees.first_name IS NULL;

This query will return all projects that do not have any employees assigned:

Output:

 

RIGHT JOIN with Multiple Tables

You can join more than two tables using multiple RIGHT JOINs. For example, adding a departments table:

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

This query would return a list of employees with their departments and projects (if any).

 

Key Points

• RIGHT JOIN returns all rows from the right table and the matching rows from the left table.
• If no match is found, the left table's columns will contain NULL.
• It is useful when you want to include all records from the right table, even when there is no match in the left table.
• You can combine RIGHT JOIN with WHERE or AND clauses to filter the result.

 

Summary

The RIGHT JOIN ensures that all rows from the right table are returned, even if there is no match in the left table. It is helpful when you want to retrieve data from one table while including rows from another table, even when no corresponding data exists in the first table.