Microsoft SQL Server 2012 Bible

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 purpose 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.

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 define how the tables intersect.

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

The intersection simply represents that some common column can connect a row ...