The SQL RIGHT JOIN Includes all rows from the Right table and those that match from the left. For that doesn’t match from the right, NULL is returned in the corresponding columns.
This is the key difference between a SQL RIGHT JOIN and INNER JOIN. Where an inner join only returns rows matching the join condition, in a Right outer join, it’s guaranteed all the Right tables rows are included in the result.
Checkout this illustration:
Check out the results for Fred and notice there is no matching Product, it is NULL. Also, the isn’t included in the result.
Another name for right join is Right outer join.
The RIGHT JOIN general form is:
SELECT table1.column1, table2.column2, … FROM table1 RIGHT JOIN table2 ON table1.commonColumn = table2.commonColumn
Notes:
- In this example table2 is the Right table and table1 the left.
- If there is no match between the commonColumn values, table1.column1 returns NULL
SQL RIGHT JOIN Example
Not every person in the AdventureWorks database is an employee. Let’s list every person, and some of their employment data, if it exists. We’ll use a RIGHT JOIN to do so.
Keep in mind it is a RIGHT JOIN, as the person table, which has every row we wish to return, is on the Right side of the JOIN operator.
Look at the results and notice there are NULL’s for some people. This is because there are no corresponding entries in the Employee table. The RIGHT JOIN is working as advertised, return all matches, and a NULL where none exist.
Important Points:
When working with RIGHT JOINS keep in mind your match from one table to another may match multiple rows. Meaning, your result may have more rows in the result that you have in either table.
When columns don’t match, the query the right row, and NULL for the left table columns..
Additional SQL Join Resources
To learn more about the joins, check out these useful resources:
