The SQL RIGHT JOIN keyword returns all rows from the RIGHT table, even if there are no matches in the LEFT table.
Departments Table
deptName | employeeID |
Sales | 1000 |
HR | 1002 |
Accounting | 1003 |
Operations | 1004 |
Employees Table
employeeID | employeeName |
1000 | John Smith |
1001 | Fred White |
1002 | Jane Scott |
1003 | Samuel Williams |
In this example, employeeID is a primary key in the table called "Employees". You'll notice that employeeID also exists in the "Departments" table, but as a foreign key.
Using an SQL Join, we can combine these two tables into one when presenting the results of a query.
Design Example
In this example, using a RIGHT JOIN, we can build a query to display the departments and employees even though not every employee is assigned to a department, such as in the case of 'Fred White'.
The two tables are joined by the 'employeeID' field.
We can use the RIGHT JOIN to bind this data together.
Syntax
SELECT tableName#.columnName#, tableName#.columnName#, etc...
FROM tableName1
RIGHT JOIN tableName2
ON tableName1.columnName# = tableName2.columnName# |
Example
List the departments and their assigned employees.
SELECT Departments.deptName, Employees.employeeName
FROM Departments
RIGHT JOIN Employees
ON Departments.employeeID = Employees.employeeID
|
Results
deptName | employeeName |
Sales | John Smith |
| Fred White |
HR | Jane Scott |
Accounting | Samuel Williams |
You should immediately notice that 'Fred White' from the Employees table is
listed.
This is because there is there is no department assigned to 'Fred White' and we had
specific a RIGHT JOIN in the query.
If there are rows in "Departments" that do not have matches in "Employees", the
records from Employees will be returned in the result set.
Did you find the page informational and useful? Share it using one of your favorite social sites.
Recommended Books & Training Resources