SQL RIGHT JOIN Examples

By: Jim Evans   |   Updated: 2021-06-24   |   Comments (1)   |   Related: > JOIN Tables

Problem

There are several options for Joining tables when write T-SQL code depending on the results you want to return. When writing T-SQL code, how do you use a RIGHT OUTER Join to join tables such that you return all the rows from one table regardless of the match to the other table?

Solution

In contrast to the SQL INNER join type that returns rows between tables with matching rows based on joined columns in both tables, SQL provides the OUTER join types that including SQL RIGHT JOIN, SQL LEFT JOIN and FULL OUTER JOIN. Outer join clauses include rows that do not match between the joined tables (or are only in one of the tables). In this article I will focus on the RIGHT OUTER JOIN. For the examples in this tutorial, I will use Microsoft’s sample database AdventureWorks.

What Is RIGHT JOIN

In T-SQL a Join is the term used for combining records from 2 or more tables. The RIGHT JOIN is one of the 3 forms of OUTER joins. Joining tables is done in the "FROM" clause of a T-SQL statement using the keyword RIGHT OUTER or RIGHT JOIN. In this tip I will use the fully spelled RIGHT OUTER JOIN keywords to clearly differentiate from other Join types.

RIGHT JOIN VS. LEFT JOIN

The OUTER JOIN types, RIGHT and LEFT virtually do the same thing, but depends on the order of the tables listed in the FROM clause. For example, to get all rows from TableA and show matches to TableB the following snippets would give the same results:

• From TableB (left table) RIGHT OUTER JOIN TableA (right table)
• From TableA (left table) LEFT OUTER JOIN TableB (right table)

The RIGHT and LEFT keywords define the location in the query to the table for which all rows will be returned. In both these cases all rows will be returned for TableA.

When to Use RIGHT JOIN

The RIGHT OUTER JOIN is used when you want to join records from tables, and you want to return all the rows from one table and show the other tables columns if there is a match else return NULL values.

In our first example we will show the use of RIGHT OUTER JOIN to return records for all Vendors showing Purchase Orders or NULLs if a Vendor has no Purchase Orders.

RIGHT OUTER JOIN Example 1 – Vendor to PurchaseOrderHeader

In this example we will return all PurchaseOrderHeader records with rows for each Vendor. The Primary Key of the Vendor table is BusinessEntityId and it references to the VendorID field in the PurchaseOrderheader table as a foreign key. We will use the RIGHT OUTER JOIN condition, specifying the columns from each table we are joining on. Note that I use table aliases as a short cut to denote which table each column in the select is coming from (v for Vendor and po for PurchaseOrderHeader). Here is the syntax:

--Example 1: PurchaseOrders per Vendor
SELECT 
   v.BusinessEntityID,
   v.AccountNumber,
   v.Name,
   po.PurchaseOrderID, 
   po.VendorID, 
   po.OrderDate, 
   po.SubTotal, 
   po.TaxAmt, 
   po.Freight, 
   po.TotalDue
FROM [Purchasing].[PurchaseOrderHeader] as po
   RIGHT OUTER JOIN [Purchasing].[Vendor] as v  ON po.VendorID = v.BusinessEntityID
ORDER BY po.OrderDate, v.BusinessEntityID;
GO

Example 1 Results

A partial result set is shown below. We see in the results that there are Vendors who do not have Purchase Orders thus their PurchaseOrderHearder row values are NULL. The Vendors who do have Purchase Order show values!

RIGHT JOIN Example 2 – Vendor to Purchase Order with Order Details

In the next example I will build off the first query and use INNER JOIN to join the Product Header, Details and Products, and then use RIGHT OUTER JOIN to include Vendors that do not have Purchased Products. The diagram below shows the 4 tables that I will be joining with INNER JOIN and RIGHT OUTER JOIN. Please take note that the Right OUTER JOIN to Vendor must be the last join in the query! If it is not the Query results will be as if we used INNER JOINS throughout! Please experiment with the RIGHT JOIN location in the join list to see what I mean!

In this example I show four tables joined together using the RIGHT OUTER JOIN.

--Example 2: PurchaseOrders with Details per Vendor
SELECT 
   po.VendorID, 
   v.AccountNumber,
   v.Name,
   po.PurchaseOrderID, 
   pod.ProductID, 
   p.ProductNumber,
   p.Name, 
   pod.OrderQty, 
   pod.UnitPrice, 
   pod.LineTotal
FROM ([Purchasing].[PurchaseOrderHeader] as po 
   INNER JOIN [Purchasing].[PurchaseOrderDetail] as pod ON pod.PurchaseOrderID = po.PurchaseOrderID
   INNER JOIN [Production].[Product] as p ON p.ProductID = pod.ProductID)
   RIGHT OUTER JOIN [Purchasing].[Vendor] as v ON po.VendorID = v.BusinessEntityID
Order by 1 
GO

Example 2 Results

The results show that we have Vendors who do not have Purchase Orders with Product Details. A partial result set is shown.

Next Steps

This article gives you explanations and example of RIGHT OUTER JOIN. Hopefully, this SQL tutorial will help your understanding of RIGHT OUTER JOINS and when to use them. Below are references to other articles that show more about other types of T-SQL JOIN syntax:

• Getting Started with SQL INNER JOIN
• Read more about JOINS: SQL LEFT JOIN Examples
• Read about other SQL Server Join Example
• CROSS JOIN - Calculate Running Totals Using SQL Server CROSS JOIN and Using CROSS JOIN queries to find records out of sequence
• Visit Microsoft to find out how to download sample databases

About the author

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips