Export (0) Print
Expand All

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.

Microsoft SQL Server 2005 uses these SQL-92 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

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 SQL-92 left outer join. The following is the query:

USE AdventureWorks;
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.

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 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 sales persons in the results, regardless of whether they are assigned a territory, use an SQL-92 right outer join. Here is the Transact-SQL query and results of the right outer join:

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

Here is the result set.

Territory                                          SalesPersonID
-------------------------------------------------- -------------
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 AdventureWorks;
GO
SELECT st.Name AS Territory, sp.SalesPersonID
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).

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 SQL-92 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 AdventureWorks;
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 ;

Community Additions

ADD
Show:
© 2014 Microsoft