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
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 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 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 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 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 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 ;