PHP —

Elementary Natural Joins

A cartesian product isn't the join we want. Instead, we want to limit the results to only the sensible rows, where the winery is actually located in the region. From a database perspective, we want only rows in which the region_id in the winery table matches the corresponding region_id in the region table. This is a natural join.[1]

[1] It isn't quite true to say that the joins described here are natural joins. A true natural join doesn't require you to specify the join condition, because "natural" implies that the system figures this out itself. So, a real natural join doesn't need the WHERE clause; one is automatically included "behind the scenes." The joins described throughout this chapter are actually called inner joins, but the results are identical to a those of a natural join.

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.

Examples

The natural join can be used in many other examples in the winestore. Consider another example that finds all the wines made by all the wineries:

SELECT winery_name, wine_name, type
  FROM winery, wine WHERE
    wine.winery_id = winery.winery_id;

This query finds all wines made by wineries through a natural join of the winery and wine tables using the winery_id attribute. The result is a large table of the 1,028 wines stocked at the winestore, their types, and the relevant wineries.

You can extend this query to produce a list of wines made by a specific winery or group of wineries. To find all wines made by wineries with a name beginning with Borg, use:

SELECT winery_name, wine_name, type
  FROM winery, wine WHERE
    wine.winery_id = winery.winery_id AND
    winery.winery_name LIKE 'Borg%';

This example extends the previous example by producing not all natural join pairs of wines and wineries, but only those for the winery or wineries beginning with Borg. The LIKE clause is covered later, in Section 3.9.

Here are two more example join queries:

  • To find the name of the region Ryan Ridge Winery is situated in:

    SELECT region.region_name FROM region,winery
  WHERE winery.region_id=region.region_id AND
  winery.winery_name='Ryan Ridge Winery';

  • To find which winery makes Curry Hill Red:

    SELECT winery.winery_name FROM winery, wine
  WHERE wine.winery_id=winery.winery_id AND
  wine.wine_name='Curry Hill Red';