Using Joins and Subqueries - MySQL - page 474

Producing master-detail and many-to-many relationships
Joins enable production of lists or summaries when each item in one table can match
many in the other, or when each item in either table can match many in the other.
Scripts that create tables used in this chapter are located in the tables directory of the
recipes distribution. For scripts that implement techniques discussed here, look in the
joins directory.
14.1. Finding Matches Between Tables
Problem
You need to perform a task that requires information from more than one table.
Solution
Use a join—that is, a query that lists multiple tables in its FROM clause and tells MySQL
how to match information from them.
Discussion
The essential idea behind a join is that it matches rows in one table with rows in one or
more other tables. Joins enable you to combine information from multiple tables when
each one answers only part of the question in which you're interested.
A complete join that produces all possible row combinations is called a Cartesian prod‐
uct. For example, joining each row in a 100-row table to each row in a 200-row table
produces a result containing 100 × 200 = 20,000 rows. With larger tables, or joins be‐
tween more than two tables, the result set for a Cartesian product easily becomes im‐
mense, so a join normally includes an ON or USING comparison clause to produce only
the desired matches between tables. (This requires that each table have one or more
columns of common information that link them together logically.) You can also include
a WHERE clause that restricts which of the joined rows to select. Each clause narrows the
focus of the query.
This section introduces join syntax and demonstrates how joins answer specific types
of questions when you are looking for matches between tables. Other sections show
how to identify mismatches between tables (see Recipe 14.2 ) and how to compare a table
to itself (see Recipe 14.4 ). The examples assume that you have an art collection and use
the following two tables to record your acquisitions. artist lists those painters whose
works you want to collect, and painting lists each painting you've actually purchased:
CREATE TABLE artist
(
a_id INT UNSIGNED NOT NULL AUTO_INCREMENT , # artist ID
name VARCHAR ( 30 ) NOT NULL , # artist name