Microsoft® SQL Server® 2012 Bible

225

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

9

The sample data includes rows that would normally break referential integrity. The follow-

ing batch inserts the six sample data rows:

INSERT Customer2(CustomerID,LastName)

VALUES(101, 'Smith'),

(102, 'Adams'),

(103, 'Reagan');

INSERT SalesOrder2 (OrderNumber ,CustomerID )

VALUES( '1',102),

( '2',104),

( '3',105);

An inner join between table Customer2 and table SalesOrder2 returns only one match-

ing row:

SELECT c.CustomerID, c.LastName , so.OrderNumber

FROM dbo.Customer2 c

INNER JOIN dbo.SalesOrder2 so

ON c.CustomerID = so.CustomerID;

Result:

CustomerID LastName OrderNumber

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

102 Adams 1

A left outer join extends the inner join and includes the rows from table Customer2 with-

out a match:

SELECT c.CustomerID, c.LastName , so.OrderNumber

FROM dbo.Customer2 c

LEFT OUTER JOIN dbo.SalesOrder2 so

ON c.CustomerID = so.CustomerID;

All the rows are now returned from table Customer2, but two rows are still missing from

table SalesOrder2:

CustomerID LastName OrderNumber

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

101 Smith NULL

102 Adams 1

103 Reagan NULL

A full outer join retrieves every row from both tables, regardless of a match between the

tables:

SELECT c.CustomerID, c.LastName , so.OrderNumber

FROM dbo.Customer2 c

FULL OUTER JOIN dbo.SalesOrder2 so

ON c.CustomerID = so.CustomerID;

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

http://www.it-ebooks.info