Datasheet

Adding a where clause identical to the one used earlier for the cross-join query would result in a single
row being returned — row 2 in Figure 1-8.
Inner joins are powerful — even more so when you consider that they can link any number of tables
together. For example:
SELECT [Order].OrderId, [Order].CustomerName, [Order].CustomerAddress,
Product.ProductId, Product.ProductName, Product.ProductCost,
ProductCategory.ProductCategoryId, ProductCategory.CategoryName,
OrderProduct.Quantity
FROM [Order] INNER JOIN OrderProduct
ON [Order].OrderId = OrderProduct.OrderId INNER JOIN Product
ON OrderProduct.ProductId = Product.ProductId INNER JOIN ProductCategory
ON Product.ProductCategoryId = ProductCategory.ProductCategoryId
This may look complicated, but it really isn’t. It uses lots of simple steps that look quite complex when
combined. Look at the steps individually and you’ll see that it’s quite straightforward. Figure 1-9 illus-
trates the query graphically (the diamonds on the relationship lines signify inner joins — each type of
join uses a different symbol).
Figure 1-9: A query involving multiple inner joins
The first part of the query shows the columns to return — which are indicated by ticked boxes in
Figure 1-9.
Order is a SQL keyword, which is why it is enclosed in square brackets in the query.
The query contains three consecutive inner joins, each of which joins a pair of tables:
1. Order is joined to OrderProduct on the value of the OrderId columns. This results in as many
rows in the result set as there are rows in the
OrderProduct table, with row data from each row
in the
Order table being duplicated in multiple rows of the result set.
2. ProductId columns are used to add data from the Product table to the result set, and an inner
join is made between
OrderProduct and Product.
3. Product is joined to ProductCategory as in the previous example.
This results in a useful combination of data. The outcome gives you full order information — including
what products make up an order, how many of each product are in the order, and what categories those
products belong to.
You come across inner joins frequently in this book, so there are plenty more examples to get your teeth into.
23
Database Fundamentals
44063c01.qxd:WroxBeg 9/12/06 10:31 PM Page 23