Using Outer Joins

Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join.

SQL Server uses the following ISO keywords for outer joins specified in a FROM clause:

  • LEFT OUTER JOIN or LEFT JOIN

  • RIGHT OUTER JOIN or RIGHT JOIN

  • FULL OUTER JOIN or FULL JOIN

Using Left Outer Joins

Consider a join of the Product table and the ProductReview table on their ProductID columns. The results show only the products for which reviews have been written.

To include all products, regardless of whether a review has been written for one, use an ISO left outer join. The following is the query:

USE AdventureWorks2008R2;
GO
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID

The LEFT OUTER JOIN includes all rows in the Product table in the results, whether or not there is a match on the ProductID column in the ProductReview table. Notice that in the results where there is no matching product review ID for a product, the row contains a null value in the ProductReviewID column.

Using Right Outer Joins

Consider a join SalesTerritory table and the SalesPerson table on their TerritoryID columns. The results show any territory that has been assigned to a sales person. The ISO 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 sales persons in the results, regardless of whether they are assigned a territory, use an ISO right outer join. Here is the Transact-SQL query and results of the right outer join:

USE AdventureWorks2008R2;
GO
SELECT st.Name AS Territory, sp.BusinessEntityID
FROM Sales.SalesTerritory st 
RIGHT OUTER JOIN Sales.SalesPerson sp
ON st.TerritoryID = sp.TerritoryID ;

Here is the result set.

Territory BusinessEntityID

-------------------------------------------------- -------------

NULL 268

Northeast 275

Southwest 276

Central 277

Canada 278

Southeast 279

Northwest 280

Southwest 281

Canada 282

Northwest 283

NULL 284

United Kingdom 285

France 286

Northwest 287

NULL 288

Germany 289

Australia 290

(17 row(s) affected)

An outer join can be further restricted by using a predicate. This example contains the same right outer join, but includes only sales territories with sales less than $2,000,000:

USE AdventureWorks2008R2;
GO
SELECT st.Name AS Territory, sp.BusinessEntityID
FROM Sales.SalesTerritory st 
RIGHT OUTER JOIN Sales.SalesPerson sp
ON st.TerritoryID = sp.TerritoryID 
WHERE st.SalesYTD < $2000000;

For more information about predicates, see WHERE (Transact-SQL).

Using Full Outer Joins

To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. SQL Server provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.

Consider a join of the Product table and the SalesOrderDetail table on their ProductID columns. The results show only the Products that have sales orders on them. The ISO FULL OUTER JOIN operator indicates that all rows from both tables are to be included in the results, regardless of whether there is matching data in the tables.

You can include a WHERE clause with a full outer join to return only the rows where there is no matching data between the tables. The following query returns only those products that have no matching sales orders, as well as those sales orders that are not matched to a product (although all sales orders, in this case, are matched to a product).

USE AdventureWorks2008R2;
GO
-- The OUTER keyword following the FULL keyword is optional.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product p
FULL OUTER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
WHERE p.ProductID IS NULL
OR sod.ProductID IS NULL
ORDER BY p.Name ;