229
Chapter 9: Merging Data with Joins, Subqueries, and CTEs
9
The key to constructing a self-join is to include a second reference to the table using a
table alias. When the table is available twice to the SELECT statement, the self-join func-
tions much like any other join. In the following example, the dbo.Employee table is refer-
enced using the table alias Mgr.Switching to the sample data, the following query reveals all employees and their corre-
sponding managers:SELECT Mgr.EmployeeName MgrName, Mgr.EmployeeID EmpIDMgr,
Emp.EmployeeName , Emp.EmployeeID
FROM dbo.Employee Emp JOIN dbo.Employee Mgr
ON Emp.MgrID = Mgr.EmployeeID;The results shown here illustrate the relationship:MgrName EmpIDMgr EmployeeName EmployeeID
------------------- --------- ----------------- ----------
Janet Jones 1 Tom Smith 2
Tom Smith 2 Ted Adams 3
Tom Smith 2 Mary Thomas 4
Tom Smith 2 Jack Jones 5
Ted Adams 3 Anita Kidder 6
Ted Adams 3 William Owens 7
Mary Thomas 4 Sean Watson 8
Jack Jones 5 Brenda Jackson 9
Jack Jones 5 Frank Johnson 10Cross Joins
The cross join, which is an unrestricted join, is a pure relational algebra multiplication of
the two source tables. Without a join condition restricting the result set, the result set
includes every possible combination of rows from the data sources. Each row in data set one
is matched with every row in data set two — for example, if the fi rst data source has 5 rows
and the second data source has 4 rows, a cross join between them would result in 20 rows.
This type of result set is referred to as a Cartesian Product.Using the Customer2/SalesOrder2 sample tables, you can construct a cross join in
Management Studio by omitting the join condition between the two tables, as shown in
Figure 9-7.In code, this type of join is specifi ed by the keywords CROSS JOIN and the lack of an
ON condition:SELECT Customer2.CustomerID,
Customer2.LastName,
SalesOrder2.OrderNumber
FROM Customer2 CROSS JOIN
SalesOrder2;c09.indd 229c09.indd 229 7/30/2012 4:25:08 PM7/30/2012 4:25:08 PM
http://www.it-ebooks.info