3.3.4.9 複数のテーブルの使用

pet テーブルはペットの記録を保持します。獣医の診察や出産といったペットの生涯におけるイベントなど、ペットに関するほかの情報を記録するには、別のテーブルが必要です。このテーブルはどのようなものにしたらよいでしょうか。次の情報を含める必要があります。

• 各イベントがどのペットに関するものかを示すためのペット名。

• イベントがいつ発生したかを示す日付。

• イベントを説明するフィールド。

• イベントを分類できるようにする場合は、イベントタイプのフィールド。

これらを考慮すると、event テーブルの CREATE TABLE ステートメントは次のようになります。

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
    -> type VARCHAR(15), remark VARCHAR(255));

pet テーブルの場合と同様に、初期レコードをロードするもっとも簡単な方法として、次の情報を記述したタブ区切りのテキストファイルを作成します。

name	date	type	remark
Fluffy	1995-05-15	litter	4 kittens, 3 female, 1 male
Buffy	1993-06-23	litter	5 puppies, 2 female, 3 male
Buffy	1994-06-19	litter	3 puppies, 3 female
Chirpy	1999-03-21	vet	needed beak straightened
Slim	1997-08-03	vet	broken rib
Bowser	1991-10-12	kennel
Fang	1991-10-12	kennel
Fang	1998-08-28	birthday	Gave him a new chew toy
Claws	1998-03-17	birthday	Gave him a new flea collar
Whistler	1998-12-09	birthday	First birthday

次のようにレコードをロードします。

mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;

pet テーブルで実行したクエリーから学んだことを基にすれば、原則は同じであるため event テーブルのレコードも取得できるはずです。ただし、event テーブルだけでは質問に回答できない場合はどのようなときでしょうか。

各ペットの出産時の年齢を調べるとします。前に、2 つの日付から年齢を計算する方法を学びました。ペットの出産日は event テーブルにありますが、その日付での年齢を計算するには生年月日が必要で、それは pet テーブルにあります。したがって、このクエリーには両方のテーブルが必要です。

mysql> SELECT pet.name,
    -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
    -> remark
    -> FROM pet INNER JOIN event
    ->   ON pet.name = event.name
    -> WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

このクエリーには注目するべき点がいくつかあります。

• このクエリーは両方のテーブルから情報を取り出す必要があるため、FROM 句で 2 つのテーブルを結合しています。

• 複数のテーブルの情報を組み合わせる (結合する) 場合、1 つのテーブルのレコードとほかのテーブルのレコードがどのように対応するかを指定する必要があります。両方のテーブルに name カラムがあるため、これは簡単です。このクエリーは、ON 句を使用して、2 つのテーブルのレコードを name 値に基づいて対応させています。

  このクエリーは INNER JOIN を使用してテーブルを結合しています。INNER JOIN では、ON 句で指定された条件を両方のテーブルが満たす場合にかぎって、結果にテーブルの行が許可されます。この例では、pet テーブルの name カラムと event テーブルの name カラムが一致する必要があると ON 句で指定しています。名前が一方のテーブルにあって他方にはない場合、ON 句の条件が満たされないため、その行は結果に表示されません。

• name カラムは両方のテーブルにあるため、このカラムを参照するときはどちらのテーブルのものかを明確に示す必要があります。そのためには、カラム名の前にテーブル名を付加します。

2 つの異なるテーブルでなくても結合は実行できます。テーブル内のレコードをその同じテーブル内のほかのレコードと比較する場合に、テーブルをそれ自体に結合すると役立つことがあります。たとえば、繁殖のつがいにするペットを選ぶ場合、pet テーブルをそれ自体に結合して、同種の雄と雌のつがい候補を生成できます。

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
    -> FROM pet AS p1 INNER JOIN pet AS p2
    ->   ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
+--------+------+--------+------+---------+
| name   | sex  | name   | sex  | species |
+--------+------+--------+------+---------+
| Fluffy | f    | Claws  | m    | cat     |
| Buffy  | f    | Fang   | m    | dog     |
| Buffy  | f    | Bowser | m    | dog     |
+--------+------+--------+------+---------+

このクエリーでは、テーブル名のエイリアスを指定してカラムを参照し、各カラムがテーブルのどちらのインスタンスに関連するかを必ず明確にしています。