SQL RIGHT Join is a type of join in SQL (Structured Query Language) that combines rows from two or more tables based on a related column between them. The RIGHT Join returns all the rows from the right table (the second table in the query), and matching rows from the left table (the first table in the query). If there is no match in the left table, the result will contain NULL values for the columns from the left table.
A RIGHT Join is useful when you want to return all the rows from one table, and the matching rows from another table. It is also used to return all the rows from one table, even if there is no matching record in the first table.
SQL RIGHT Join Syntax
The basic syntax of the SQL RIGHT Join is as follows:
SELECT column1, column2, ... FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
In this syntax:
- SELECT clause specifies the columns to be returned in the result set.
- FROM clause specifies the left table (table1) in the join.
- RIGHT JOIN clause specifies the right table (table2) in the join and the join condition using the ON clause.
- ON clause specifies the join condition that relates the columns from both tables.
SQL RIGHT Join Example
Consider two tables: “employees” and “departments”. The “employees” table has the following data:
| EmployeeID | EmployeeName | DepartmentID |
|---|---|---|
| E001 | John | D001 |
| E002 | Jane | D002 |
| E003 | Bob | D003 |
| E004 | Alice | D004 |
| E005 | Eve | D005 |
And the “departments” table has the following data:
| DepartmentID | DepartmentName |
|---|---|
| D001 | IT |
| D002 | Marketing |
| D003 | HR |
| D004 | Sales |
Now, if we want to return all the departments with their respective employees, we can use the following SQL RIGHT Join statement:
SELECT departments.DepartmentID, departments.DepartmentName, employees.EmployeeID, employees.EmployeeName FROM departments RIGHT JOIN employees ON departments.DepartmentID = employees.DepartmentID;
The output of this SQL RIGHT Join statement would be:
| DepartmentID | DepartmentName | EmployeeID | EmployeeName |
|---|---|---|---|
| D001 | IT | E001 | John |
| D002 | Marketing | E002 | Jane |
| D003 | HR | E003 | Bob |
| D004 | Sales | E004 | Alice |
| E005 | Eve |
In this example, we can see that the SQL RIGHT Join returns all the departments and the matching employees. And for the department which doesn’t have a matching employee, the result will have NULL values for the columns from the employees table.
SQL RIGHT Join with WHERE clause
We can also use the WHERE clause in a SQL RIGHT Join statement to filter the result set. Consider the following example:
SELECT departments.DepartmentID, departments.DepartmentName, employees.EmployeeID, employees.EmployeeName FROM departments RIGHT JOIN employees ON departments.DepartmentID = employees.DepartmentID WHERE employees.EmployeeName IS NOT NULL;
In this example, we are using the WHERE clause to filter the result set and only return the rows where the “EmployeeName” column is not NULL. The output of this statement would be:
| DepartmentID | DepartmentName | EmployeeID | EmployeeName |
|---|---|---|---|
| D001 | IT | E001 | John |
| D002 | Marketing | E002 | Jane |
| D003 | HR | E003 | Bob |
| D004 | Sales | E004 | Alice |
As we can see, the output only includes the rows where the “EmployeeName” column is not NULL, and the row with the “Eve” employee is excluded from the result set.
Summary
In conclusion, the SQL RIGHT Join is used to return all the records from the right table (employees) and matching records from the left table (departments). The RIGHT Join can be useful when we want to include all the records from the right table, even if there is no matching record in the left table. By using the ON and WHERE clauses, we can control the result set and return only the desired data. It’s important to understand the different types of joins in SQL, as they are essential for working with relational databases.
