3.7.1. Beware of the Cartesian Product

SELECT winery_name,region_name FROM winery, region;

This query produces—in part—the following results:

+-------------------------------+-------------+
| winery_name                   | region_name |
+-------------------------------+-------------+
| Ryan Ridge Winery             | Victoria    |
| Macdonald Creek Premium Wines | Victoria    |
| Davie's                       | Victoria    |
| Porkenberger Brook Vineyard   | Victoria    |
| Rowley Hill Vineyard          | Victoria    |

The impression here is that, for example, Ryan Ridge Winery is located in the Victoria region. This might not be the case. Why? First, you can use the techniques covered so far in this chapter to check which region Ryan Ridge Winery is located in:

SELECT region_id FROM winery
  WHERE winery_name='Ryan Ridge Winery';

The result is region_id=2.

Now query the region table to find the name of region_id=2 using:

SELECT region_name FROM region 
  WHERE region_id=2;

The region_name is South Australia. So, Ryan Ridge Winery isn't in Victoria at all!

SELECT winery_name, region_name FROM winery, region
  ORDER BY winery_name, region_name;

Recall that the ORDER BY clause sorts the results after the query has been evaluated; it has no effect on which rows are returned from the query. Here is the first part of the result of the query with the ORDER BY clause:

+----------------------+-------------------+
| winery_name          | region_name       |
+----------------------+-------------------+
| Anderson Creek Wines | New South Wales   |
| Anderson Creek Wines | South Australia   |
| Anderson Creek Wines | Victoria          |
| Anderson Creek Wines | Western Australia |
| Anderson Group       | New South Wales   |
| Anderson Group       | South Australia   |
| Anderson Group       | Victoria          |
| Anderson Group       | Western Australia |

The query produces all possible combinations of the four region names and 300 wineries in the sample database! In fact, the size of the output can be accurately calculated as the total number of rows in the first table multiplied by the total rows in the second table. In this case, the output is 4 x 300 = 1,200 rows.

3.7.2. Elementary Natural Joins

Consider a revised example using a natural join:

SELECT winery_name, region_name 
  FROM winery, region 
  WHERE winery.region_id = region.region_id 
  ORDER BY winery_name;

An ORDER BY clause has been added to sort the results by winery_name but this doesn't affect the join. This query produces—in part—the following sensible results:

+----------------------+-------------------+
| winery_name          | region_name       |
+----------------------+-------------------+
| Anderson Creek Wines | Western Australia |
| Anderson Group       | New South Wales   |
| Beard                | South Australia   |
| Beard and Sons       | Western Australia |
| Beard Brook          | New South Wales   |

Several features are shown in this first successful natural join:

• The FROM clause contains more than one table name. In this example, SELECT retrieves rows from the tables winery and region.

• Attributes in the WHERE clause are specified using both the table name and attribute name, separated by a period. This usually disambiguates uses of the same attribute name in different tables.

  So, for example, region_id in the region table and region_id in the winery table are disambiguated as region.region_id and winery.region_id. This procedure can also be used for clarity in queries, even if it isn't required. It can be used in all parts of the query, not just the WHERE clause.

• The WHERE clause includes a join clause that matches rows between the multiple tables. In this example, the output is reduced to those rows where wineries and regions have matching region_id attributes, resulting in a list of all wineries and which region they are located in. This is the key to joining two or more tables to produce sensible results.

3.7.3. Joins with More than Two Tables

SELECT * FROM customer, orders, items WHERE
  customer.cust_id = orders.cust_id AND
  orders.order_id = items.order_id AND
  orders.cust_id = items.cust_id AND
  customer.cust_id = 2;

In this query, the natural join is between three tables, customer, orders, and items, and the rows selected are those in which the cust_id is the same for all three tables, the cust_id is 2, and the order_id is the same in the orders and items tables.

If you remove the cust_id=2 clause, the query outputs all items in all orders by all customers. This is a large result set, but still a sensible one that is much smaller than the cartesian product!

Here are two more examples that join three tables:

• To find which wines are made in the Margaret River region:

  SELECT wine_name FROM wine,winery,region
    WHERE wine.winery_id=winery.winery_id AND
    winery.region_id=region.region_id AND
    region.region_name='Margaret River';

• To find which region contains the winery that makes the Red River Red wine:

  SELECT region_name FROM wine,winery,region
    WHERE wine.winery_id=winery.winery_id AND
    winery.region_id=region.region_id AND
    wine.wine_name='Red River Red';

Extending to four or more tables generalizes the approach further. To find the details of customers who have purchased wines from Buonopane Wines, use:

SELECT DISTINCT customer.cust_id, customer.surname, customer.firstname 
  FROM customer, winery, wine, items
  WHERE customer.cust_id=items.cust_id AND
    items.wine_id=wine.wine_id AND
    wine.winery_id=winery.winery_id AND
    winery.winery_name='Buonopane Wines'
  ORDER BY customer.surname, customer.firstname;

This last query is the most complex so far and contains a four-step process. The easiest way to understand a query is usually to start with the WHERE clause and work toward the SELECT clause:

1. The WHERE clause restricts the winery rows to those that bear the name Buonopane Wines.

2. The resultant winery rows—there is probably only one winery called Buonopane Wines—are joined with wine to find all wines made by Buonopane Wines.

3. The wines made by Buonopane Wines are joined with the items that have been purchased.

4. The purchases of Buonopane Wines are joined with the customer rows of the customers who have purchased the wine. You can leave out the orders table, because the items table contains a cust_id for the join; if you need the order number, the discount applied, or another orders attribute, the orders table needs to be included in the query.

5. The result is the details of customers who have purchased Buonopane Wines. DISTINCT is used to show each customer only once. ORDER BY sorts the customer rows into telephone directory order.

Designing a query like this is a step-by-step process. We began by testing a query to find the winery_id of wineries with the name Buonopane Wines. Then, after testing the query and checking the result, we progressively added additional tables to the FROM clause and join conditions. Finally, we added the ORDER BY clause.