RIGHT / RIGHT OUTER JOIN

• A RIGHT JOIN (also called RIGHT OUTER JOIN) returns:

• All rows from the right table
• Matching rows from the left table
• If there is no match, the result contains NULL for columns from the left table

SELECT A.column1, B.column2 FROM TableA A

RIGHT JOIN TableB B

ON A.common_column = B.common_column;

========================================================================

Table: Employee

EmployeeID	FirstName	LastName	Title	HireDate	ManagerID
1	George	Cruz	CEO	12/8/2008	NULL
2	John	Mathew	Vice President	4/1/2009	1
3	Ethan	William	Director I	8/8/2009	2
4	Jacob	Logan	Director II	12/8/2011	3
5	Nathan	Mateo	Senior Manager	9/3/2009	4
6	Alice	Charles	Manager I	12/8/2012	5
7	John	Robert	Manager II	10/8/2011	6
8	Sophia	James	Manager III	5/8/2011	7
9	Kevin	Chan	Product Manager	7/8/2011	5
10	Susan	Mortiz	Solutions Arthitect	1/3/2012	6
11	Martha	Miller	Systems Arthitect	12/15/2012	10

 

Table: Location

 

LocationID	LocationCode	LocationName
1	L001	San Francisco
2	L002	New York
3	L003	Chicago
4	L004	Los Angeles

Query :

SELECT E.EmployeeID, E.LastName, E.FirstName, L.*

FROM Employee AS E RIGHT JOIN Location AS L ON E.EmployeeLocationID = L.LocationID

Output :

EmployeeID	FirstName	LastName	LocationID	LocationCode	LocationName
1	George	Cruz	1	L001	San Francisco
2	John	Mathew	1	L001	San Francisco
6	Alice	Charles	1	L001	San Francisco
9	Kevin	Chan	1	L001	San Francisco
10	Susan	Mortiz	1	L001	San Francisco
11	Martha	Miller	1	L001	San Francisco
3	Ethan	William	2	L002	New York
5	Nathan	Mateo	2	L002	New York
7	John	Robert	2	L002	New York
NULL	NULL	NULL	3	L003	Chicago
NULL	NULL	NULL	4	L004	Los Angeles

Description :

• The above query with RIGHT JOIN has Location table as the dominant table. The query returns all records from the Location table and only matching records from the Employee table. If a row in Location table does not have matching record in Employee table, then the row output will contain NULL value for each column from Employee table.

• As you can see, since there are no matching row in Employee table for LocationID 3 and 4, NULL value is returned for each column from Employee table. Also notice that the output does not contain all the employees from the Employee table. EmployeeID 4 and 8 are not part of the output because it does not have a matching LocationID in Location table.

========================================================================

Example

Now let's look at an example that shows how to use the RIGHT OUTER JOIN in a SELECT statement.

 

Using the same customers table as the previous example:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	techonthenet.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	techonthenet.com

 

And the orders table with the following data:

order_id	customer_id	order_date
1	7000	2016/04/18
2	5000	2016/04/18
3	8000	2016/04/19
4	4000	2016/04/20
5	NULL	2016/05/01

 

SELECT customers.customer_id, orders.order_id, orders.order_date
FROM customers
RIGHT OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

 

There will be 5 records selected. These are the results that you should see:

customer_id	order_id	order_date
NULL	5	2016/05/01
4000	4	2016/04/20
5000	2	2016/04/18
7000	1	2016/04/18
8000	3	2016/04/19

This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the customers table where the joined fields are equal.

If a customer_id value in the orders table does not exist in the customers table, all fields in the customers table will display as NULL in the result set. As you can see, the row where order_id is 5 would be included with a RIGHT OUTER JOIN but the customer_id field displays NULL.