Microsoft® SQL Server® 2012 Bible

223

Chapter 9: Merging Data with Joins, Subqueries, and CTEs

9

There’s no trick to telling the difference between left and right outer joins. In code, left or

right refers to the table that is included regardless of the match. The outer-join table (some-

times called the driving table) is typically listed fi rst, so left outer joins are more common

than right outer joins. Any confusion between left and right outer joins is probably caused

by the use of graphical-query tools to build joins because left and right refers to the table’s

listing in the SQL text, and the tables’ positions in the graphical-query tool are moot.

Best Practice

When coding outer joins, always order your data sources so that you can write left outer joins. Don’t

use right outer joins, and never mix left outer joins and right outer joins.

To modify the previous customer-sales order query so that it returns all contacts regard-

less of any orders, changing the join type from inner to left outer is all that’s required, as

follows:

USE AdventureWorksLT2012;

SELECT CST.CompanyName, SOH.TotalDue

FROM SalesLT.Customer CST

LEFT OUTER JOIN SalesLT.SalesOrderHeader SOH

ON CST.CustomerID = SOH.CustomerID

ORDER BY CST.CompanyName;

The left outer join includes all rows from the Customer table and matching rows from the

SalesOrderHeader table. The abbreviated result of the query is as follows:

CompanyName TotalDue

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

A Bike Store NULL

A Bike Store NULL

A Cycle Shop NULL

A Great Bicycle Company NULL

A Great Bicycle Company NULL

A Typical Bike Shop NULL

A Typical Bike Shop NULL

Acceptable Sales & Service NULL

Acceptable Sales & Service NULL

Action Bicycle Specialists 119960.824

Because all the companies except for Action Bicycle Specialists in this sample set do

not have corresponding rows in the SalesOrderHeader table, the columns from the

SalesOrderHeader table return a null for those rows.

Having said that, SQL Server supports backward compatibility, so if the database compat-

ibility level is set to 80 (SQL Server 2000), the ANSI 82 style outer joins still work.

c09.indd 223c09.indd 223 7/30/2012 4:25:07 PM7/30/2012 4:25:07 PM

http://www.it-ebooks.info