Web Database Applications with PHP, and MySQL

Join Queries

A join query is a querying technique that matches rows from two or more tables based on a join condition in a WHERE clause and outputs only those rows that meet the condition. As part of the process of converting the winestore entity-relationship model to SQL statements, we have included the attributes required in any practical join condition.

To understand which tables can be joined in the winestore database, and how the joins are processed, it is helpful to have a copy of the ER model at hand.

Beware of the Cartesian Product

Oddly, the easiest way to introduce join queries is to discuss what not to do. Consider this query, which we might intuitively, but wrongly, use to find all the wineries in a region:

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 ...