How to Use RIGHT JOIN in SQL
The RIGHT JOIN clause, also known as RIGHT OUTER JOIN, in SQL is used to combine rows from two or more tables based on a related column between them. It returns all the rows from the right table (the second table in the query) and the matching rows from the left table (the first table). If no match is found,NULL values are returned for columns from the left table.
Syntax of RIGHT JOIN
SELECT column1, column2, ... FROM table1 RIGHT JOIN table2 ON table1.common_column = table2.common_column;
- table1: The first table in the join operation (the left table).
- table2: The second table in the join operation (the right table).
- common_column: The column that exists in both tables and is used to match the rows.
Example of RIGHT JOIN
Consider two tables: employees and departments.
Table 1: employees
| employee_id | first_name | last_name | department_id |
|---|---|---|---|
| 1 | John | Doe | 101 |
| 2 | Jane | Smith | 102 |
| 3 | Jim | Brown | NULL |
| 4 | Jill | White | 103 |
Table 2: departments
| department_id | department_name |
|---|---|
| 101 | HR |
| 102 | IT |
| 104 | Marketing |
SQL Query
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;
Result:
| first_name | last_name | department_name |
|---|---|---|
| John | Doe | HR |
| Jane | Smith | IT |
| NULL | NULL | Marketing |
Explanation of the Query
The query selects the first_name, last_name, and department_name columns.
It performs a RIGHT JOIN between the employees table and the departments table.
The ON keyword specifies the condition for the join, which is that the department_id column in employees must match the department_id in departments.
Since employees does not have any matching employees, it is included with NULLvalues for first_name and last_name.
Key Points:
- A RIGHT JOIN includes all rows from the right table and matching rows from the left table.
- If no match is found in the left table,NULL values are returned for the columns from the left table.
- The RIGHT JOIN is useful when you want to include all records from the right table and retrieve matching records from the left table, even when no matches exist.
Use Cases for RIGHT JOIN:
- Incorporating Missing Data: Use RIGHT JOIN when you want to ensure that all records from the right table are included, even if there are no matching records in the left table.
- When You Need Data from Right Table with Optional Left Table Data: Use RIGHT JOIN when the right table’s data is essential for the query result, and you want to retrieve matching left table data if it exists.
Conclusion
The RIGHT JOIN operation is useful for including all rows from the right table, even if matching rows do not exist in the left table. It ensures that no rows from the right table are excluded from the result set, with NULL values used for missing data from the left table.
