Datasheet

Figure 1-7: Result of a cross join between Product and ProductCategory tables
There are six results because every row in
Product (3) is combined with every row in ProductCategory (2):
3×2=6. Note that there are two
ProductCategoryId columns — one from each table. This raises an important
point: How do you differentiate between columns in different tables that have the same name? You do so by
using an expanded reference to the columns; namely
[TableName].[ColumnName]. The two columns here
are therefore
Product.ProductCategoryId and ProductCategory.ProductCategoryId. This is the
method used by SQL Server and many other DBMSes to identify columns, and it’s the syntax you would use
if, say, you wanted to specify one of these columns in the column specification part of a select statement.
The addition of the where clause in the sample cross join query shown previously reduces the six-row
result set to three rows — those with a
CategoryName column containing the string Things (rows 1 to 3
in Figure 1-7).
At first glance this may seem useful, but the query hasn’t really achieved much. Crucially, it hasn’t taken
into account the actual relationship between the rows of data. Each
ProductCategory row is associated
with a
Product row in a one-to-many relationship, but that isn’t reflected in the result set you get with a
cross join. This relationship is achieved by the
Product.ProductCategoryId column being a foreign
key relating to the primary key
ProductCategory.ProductCategoryId.
To obtain a more meaningful set of results, you need to use one of the other types of join. The simplest of
them is the inner join.
Understanding Inner Joins
Inner joins require more information, namely a join specification, which takes a form similar to the where
clause filters you saw earlier. The syntax here (omitting the column specification for now) is as follows:
FROM TableA INNER JOIN TableB ON [Join Specification]
Handily, the keywords for performing an inner join are INNER JOIN. To get the behavior discussed ear-
lier, where the
Product and ProductCategory tables are joined based on their respective
ProductCategoryId columns, the query would need to be as follows:
SELECT * FROM Product INNER JOIN ProductCategory
ON Product.ProductCategoryId = ProductCategory.ProductCategoryId
The result of this query (again with truncated GUIDs) is shown in Figure 1-8.
Figure 1-8: Result of an inner join between Product and ProductCategory
based on ProductCategoryId columns
22
Chapter 1
44063c01.qxd:WroxBeg 9/12/06 10:31 PM Page 22