You want to use rows in one table to locate rows in another table.
Use a join with an appropriate
WHERE clause to match up records from
The records in the
pants tables from Recipe 12.2
have no special relationship to each other, so no combination of rows
is more meaningful than any other. That’s okay,
because the purpose of the examples that use those tables is to
illustrate how to perform a join, not why you’d do
The “why” is that joins allow you to combine information from multiple tables when each table contains only part of the information in which you’re interested. Output rows from a join are more complete than rows from either table by itself. This kind of operation often is based on matching rows in one table to rows in another, which requires that each table have one or more columns of common information that can be used to link them together logically.
To illustrate, suppose you’re starting an art
collection, using the following two tables to record your
artist lists those painters whose
works you want to collect, and
painting lists each
painting that you’ve purchased:
CREATE TABLE artist ( a_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # artist ID name VARCHAR(30) NOT NULL, # artist name PRIMARY KEY (a_id), UNIQUE (name) ); CREATE TABLE painting ( a_id INT UNSIGNED NOT NULL, # artist ID p_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # painting ID title ...