Microsoft® SQL Server® 2012 Bible

222

Part II: Building Databases and Working with Data

to build a query that lists all customers plus their recent orders, only an outer join can retrieve

every customer whether or not the customer has placed an order. An inner join between cus-

tomers and orders would miss every customer who did not place a recent order.

Depending on the nullability of the keys and the presence of rows on both sides of the join, it’s easy to write a query

that misses rows from one side or the other of the join. This error might occur in third-party ISV application code. To

avoid this data integrity error, know your schema well and always unit test your queries against a small data set with

known answers.

FIGURE 9-4

An outer join includes not only rows from the two data sources with a match, but also

unmatched rows from outside the intersection.

Data Set A Data Set B

Common

Intersection

Right Outer Join

Left Outer Join

Some of the data in the result set produced by an outer join looks just like the data from an

inner join. Data is in columns that come from each of the data sources, but any rows from

the outer-join table that do not have a match in the other side of the join return data only

from the outer-join table. In this case, columns from the other data source have null values.

T-SQL Code and Outer Joins

In SQL code, an outer join is declared by the keywords LEFT OUTER or RIGHT OUTER

before the JOIN (technically, the keyword OUTER is optional):

SELECT *

FROM Table1

LEFT|RIGHT [OUTER] JOIN Table2

ON Table1.column = Table2.column;

Several keywords (such as INNER, OUTER, or AS) in SQL are optional or may be abbreviated (such as PROC for

PROCEDURE). Although most developers omit the optional syntax, explicitly stating the intent by spelling out the full

syntax improves the readability of the code.

c09.indd 222c09.indd 222 7/30/2012 4:25:06 PM7/30/2012 4:25:06 PM

http://www.it-ebooks.info