Web Database Applications with PHP and MySQL, 2nd Edition

Join Queries

You'll often want to output data that's based on relationships between two or more tables. For example, in the winestore database, you might want to know which customers have placed orders, which customers live in Australia, or how many bottles of wine Lucy Williams has bought. These are examples of join queries, queries that match rows between tables based (usually) on primary key values. In SQL, a join query matches rows from two or more tables based on a 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've 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's helpful to have a copy of the ER model at hand as you work your way through this section.

Beware of the Cartesian Product

Suppose you want to find out the names of the wineries in the winestore database and, for each winery, the name of the region that it's located in. To do this, you examine the ER model and discover that the region and winery tables are related, and that they both contain attributes that you need in the answer to your query. Specifically, you need to retrieve the winery_name attribute from the winery table and the region_name attribute from the region table, and you need to join the two tables together to find the result.

Consider this query, which ...