How to Use Right Outer Joins in SQL

A RIGHT JOIN (also referred to as a RIGHT OUTER JOIN) returns all rows from the right table with corresponding rows from the left table. If there's no matching row, NULLs are returned as values from the left table.

The syntax for a RIGHT JOIN is as follows:
SELECT table1.column, table2.column
FROM table1
RIGHT [OUTER] JOIN table2 ON (table1.column=table2.column)
WHERE conditions

Consider a join of the SalesOrderHeader table and the Customer table on their CustomerID columns. The results show only the Companies who have orders pending, their Freight, CustomerID and TotalDue.

SELECT oh.CustomerID, c.CompanyName, oh.Freight, oh.TotalDue
FROM SalesLT.SalesOrderHeader AS oh
JOIN SalesLT.Customer AS c
ON c.CustomerID = oh.CustomerID;

To return all records from the SalesOrderHeader table and only matching data from the Customer table (in other words, all orders including those for which there was no matching customer record), we will use a RIGHT [OUTER] join. Here is the modified query of the Transact-SQL right outer join:

SELECT oh.CustomerID, c.CompanyName, oh.Freight, oh.TotalDue
FROM SalesLT.SalesOrderHeader AS oh
RIGHT OUTER JOIN SalesLT.Customer AS c
ON c.CustomerID = oh.CustomerID;

Below is another right outer join example: This query retrieves a list of all customers and any orders they have placed, including customers who have registered but never placed an order.

///////////////////New Copy Code ////////////////////////// /////////////////////////////////////// ///////////////////// PUB Database Examples //////////////////////////// ////Using Right Outer Joins Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities where a publisher is located (in this case, Abraham Bennet and Cheryl Carson). The SQL-92 right outer join operator, RIGHT OUTER JOIN, indicates all rows in the second table are to be included in the results, regardless of whether there is matching data in the first table. To include all publishers in the results, regardless of whether a city has a publisher located in the same city, use an SQL-92 right outer join. Here is the Transact-SQL query and results of the right outer join: USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors AS a RIGHT OUTER JOIN publishers AS p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC ////////screenshot emeka An outer join can be further restricted by using a predicate (such as comparing the join to a constant). This example contains the same right outer join, but eliminates all titles that have sold less than 50 copies: USE pubs SELECT s.stor_id, s.qty, t.title FROM sales s RIGHT OUTER JOIN titles t ON s.title_id = t.title_id AND s.qty > 50 ORDER BY s.stor_id ASC /////////////// end of page ///////////

How to Use Outer Joins in SQL

.

////////////////

Related Content