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
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 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.
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 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).
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 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 ;
See Also
Concepts
Using Operators in Expressions
Other Resources
Operators (Transact-SQL)
SELECT (Transact-SQL)
SELECT Examples (Transact-SQL)