231
Chapter 9: Merging Data with Joins, Subqueries, and CTEs
9
is not a requirement. The condition between the two columns is not necessarily equal, nor
is the join limited to one condition.The ON condition of the JOIN behaves much like a WHERE condition, restricting the prod-
uct of the two joined data sets. WHERE-clause conditions may be fl exible and powerful, and
the same is true of join conditions. This understanding of the ON condition enables the use
of two powerful techniques: Q (theta) joins and multiple-condition joins.Multiple-Condition Joins
If a join is nothing more than a condition between two data sets, it makes sense that mul-
tiple conditions are possible at the join. Multiple-condition joins and Q joins go hand-in-
hand. Without the ability to use multiple-condition joins, Q joins would be of little value.If the database schema uses natural primary keys, there are probably tables with composite
primary keys, which means queries must use multiple-condition joins.A composite key is a primary key made up of more than one column. A natural key is a primary key made up of mean-
ingful business data rather than an arbitrary sequence number (IDENTITY). Because it can be diffi cult to fi nd a
consistently unique value in business data, composite keys are common when natural keys are used.Join conditions can refer to any table in the FROM clause, enabling interesting three-way
joins:FROM A
INNER JOIN B
ON A.col = B.col
INNER JOIN C
ON B.col = C.col
AND A.col = C.col;The fi rst query in the previous section, “Placing the Conditions Within Outer Joins,” was a
multiple-condition join.Q (theta) Joins
A theta join (depicted throughout as Q) is a join based on a nonequal on condition. In rela-
tional theory, conditional operators (=, >, <, >=, <=, <>) are called Q operators. Although the
equals condition is technically a Q operator, it is commonly used, so only joins with condi-
tions other than equal are referred to as Q joins.The Q condition may be set within Management Studio’s Query Designer using the join
Properties dialog (refer to Figure 9-7).Now that this chapter has covered the basics of how to join tables in SQL Server and how
the syntax of your join criteria can affect how many results you receive, it’s time to cover
combining data from multiple queries and subquerying.c09.indd 231c09.indd 231 7/30/2012 4:25:08 PM7/30/2012 4:25:08 PM
http://www.it-ebooks.info