Microsoft® SQL Server® 2012 Bible

224

Part II: Building Databases and Working with Data

Full Outer Joins

A full outer join returns all the data from both data sets regardless of the intersection, as

shown in Figure 9-5. It is functionally the same as taking the results from a left outer join

and the results from a right outer join, and unioning them together. (Unions are explained

later in this chapter.)

FIGURE 9-5

The full outer join returns all the data from both data sets, matching the rows where it can

and presenting NULL when it cannot.

Data Set A Data Set B

Common

Intersection

Full Outer Join

In real life, referential integrity reduces the need for a full outer join because every row

from the secondary table should have a match in the primary table (depending on the

optionality of the foreign key), so left outer joins are typically suffi cient. Full outer joins

are most useful for cleaning up data that has not had the benefi t of clean constraints to

fi lter out bad data.

The following example is a mock-up of such a situation and compares the full outer

join with an inner and a left outer join. Table Customer2 is the primary table. Table

SalesOrder2 is a secondary table with a foreign key that refers to Customer2.

No foreign key constraint exists, so there may be some nonmatches for the outer join

to fi nd:

USE tempdb

GO

CREATE TABLE dbo.Customer2 (

CustomerID INT ,

LastName VARCHAR(50)

);

CREATE TABLE dbo.SalesOrder2 (

OrderNumber VARCHAR(15),

CustomerID INT

);

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

http://www.it-ebooks.info