Microsoft® SQL Server® 2012 Bible

227

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

9

to those rows where LastName is equal to Adams. The net effect is the same as when an

inner join was used (but it might take more execution time):

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

FROM dbo.Customer2 c

LEFT OUTER JOIN dbo.SalesOrder2 so

ON c.CustomerID = so.CustomerID

WHERE c.LastName = 'Adams';

Result:

CustomerID LastName OrderNumber

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

102 Adams 1

Multiple Outer Joins

Coding a query with multiple outer joins can be tricky. Typically, the order of data sources

in the FROM clause doesn’t matter, but here it does. The key is to code them in a sequential

chain. If you are tasked with providing a result that contains all customers, any orders

they may have placed, and any corresponding shipping details for those orders, think

through it this way:

1. Grab all the customers regardless of whether they’ve placed any orders.


2. Then grab all the orders regardless of whether they’ve shipped.


3. Then grab all the ship details.

When chaining multiple outer joins, stick to left outer joins because mixing left and right

outer joins quickly becomes confusing. Be sure to unit test the query with a small sample

set of data to ensure that the outer join chain is correct.

Self-Joins

A self-join is a join that refers back to the same table. This type of unary relationship is

often used to extract data from a reflexive (also called a recursive) relationship, such as

organizational charts (employee to boss). Think of a self-join as a table joined with a tem-

porary copy of itself.

To set up some sample data, use the following code:

USE tempdb;

CREATE TABLE dbo.Employee (

EmployeeID int PRIMARY KEY,

EmployeeName varchar(30),

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

http://www.it-ebooks.info