Microsoft® SQL Server® 2012 Bible

215

9

Using Joins........................................................................................................

In relational algebra, a join is the multiplication of two data sets followed by a restriction

of the result so that only the intersection of the two data sets is returned. The whole pur-

pose of the join is to horizontally merge two data sets and produce a new result set from

the combination by matching rows in one data source to rows in the other data source, as

shown in Figure 9-1. This section explains the various types of joins and how to use them

to select data.

FIGURE 9-1

A join merges rows from one data set with rows from another data set, creating a new set of

rows that includes columns from both. The code, 101, is common to Smith and order number

1 and merges the two original rows into a single result row.

Name

Smith

Code

101

Name

Smith

Code

101

Code

101

Order

1

Order

1

By merging the data using the join, the rest of the SQL SELECT statement, including the

column expressions, aggregate groupings, and WHERE clause conditions, can access any of

the columns or rows from the joined tables. These capabilities are the core and power of SQL.

Joins are based on the idea of intersecting data sets. As shown in Figure 9-2, a relational

join deals with two sets of data that have common values, and it’s these common values

that defi ne how the tables intersect.

FIGURE 9-2

Relational joins are based on the overlap, or common intersection, of two data sets.

Data Set A Data Set B

Common

Intersection

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

c09.indd 215c09.indd 215 7/30/2012 4:25:04 PM7/30/2012 4:25:04 PM

http://www.it-ebooks.info