MySQL Cookbook

Finding Rows in One Table That Match Rows in Another

Problem

You want to use rows in one table to locate rows in another table.

Solution

Use a join with an appropriate WHERE clause to match up records from different tables.

Discussion

The records in the shirt, tie, and 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 so.

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