Datasheet

You’ll return to inner joins in subsequent chapters. One last note for now: It is possible to use operators
other than
= to join tables. For numeric fields you can, for example, use >. The results of this are more
difficult to illustrate (and to explain the usefulness of), but it’s something to bear in mind for later.
Understanding Outer Joins
The other types of join that you can perform are outer joins. As mentioned earlier, these are like inner
joins, but are where rows that aren’t joined to other rows may be included in the result set. You can see
this in action if, for example, the
ProductCategory table contains a third row, as shown in Figure 1-10.
Figure 1-10: ProductCategory table contents
Executing the inner join query shown earlier:
SELECT * FROM Product INNER JOIN ProductCategory
ON Product.ProductCategoryId = ProductCategory.ProductCategoryId
gives the same results shown in Figure 1-8. The additional row in ProductCategory has no effect,
because there is no row in
Product with a matching ProductCategoryId column. This is where outer
joins can come in handy. They enable you to include extra rows such as the one added to
ProductCategoryId.
There are three types of outer join that enable you to specify which table in the join should have all of its
rows included in the result set, regardless of the join specification. These are:
❑ Left outer join: Includes all rows in the first table specified in the result set.
❑ Right outer join: Includes all rows in the second table specified in the result set.
❑ Full outer join: Includes all rows in the both tables specified in the result set.
A left outer join is specified using the keywords
LEFT OUTER JOIN:
SELECT * FROM Product LEFT OUTER JOIN ProductCategory
ON Product.ProductCategoryId = ProductCategory.ProductCategoryId
This specifies that all rows in Product should be included. However, looking at the result set in
Figure 1-8, you can see that all the
Product rows are already in the result. This query gives the same
result as an inner join in this instance. This is, in fact, enforced by the foreign key relationship between
the
Product and ProductCategory tables. The Product.ProductCategoryId column cannot be null
and cannot refer to a nonexistent row in
ProductCategory, so all the rows in Product are, by implica-
tion, included in the result set.
A right outer join uses the keywords
RIGHT OUTER JOIN:
SELECT * FROM Product LEFT OUTER JOIN ProductCategory
ON Product.ProductCategoryId = ProductCategory.ProductCategoryId
24
Chapter 1
44063c01.qxd:WroxBeg 9/12/06 10:31 PM Page 24