pet テーブルでは飼っているペットのデータを管理しています。ペットに関するその他の情報、たとえば獣医に通った回数や出産した日など、その一生に発生するイベントなどを記録する場合は、別のテーブルを作成する必要があります。このテーブルはどのような構成でしょうか。このテーブルに必要なカラムを以下に示します。

これらの検討事項を踏まえ、event テーブルの CREATE TABLE ステートメントは以下のようになります。

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

pet テーブルの場合と同じように、情報を記述したタブ区切りのテキストファイルを作成して初期レコードをロードするのが最も簡単です。

これらのレコードをロードします。

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

pet テーブルに対して実行したクエリから学習した内容を参考にすれば、event テーブルからもさまざまなデータを取得できます。基本的な考え方は同じです。ただし、event テーブルだけでは質問に回答できない場合があります。それはどのような場合でしょうか。

それぞれのペットが出産したときの年齢を調べる場合を考えます。すでに 2 つの日付から年齢を計算する方法については説明しました。母親の出産日は event テーブルに格納されていますが、その日の母親の年齢を調べるには母親の誕生日が必要です。この誕生日は pet テーブルに格納されています。このことは、クエリで 2 つのテーブルを参照する必要があることを意味します。

mysql> SELECT pet.name,
    -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
    -> remark
    -> FROM pet, event
    -> WHERE pet.name = event.name AND 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         |
+--------+------+-----------------------------+

このクエリについては、注目すべき点がたくさんあります。

結合を実行する場合、必ずしも 2 つの異なるテーブルを使用する必要はありません。テーブルのレコードを同じテーブルのほかのレコードと比較する場合には、テーブルをそれ自体と結合させると便利です。たとえば、ペットの中で繁殖させる組み合わせを探す場合、pet テーブルをそれ自体と結合させて、同種のオスとメスの組み合わせの候補を取得することができます。

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
    -> FROM pet AS p1, pet AS p2
    -> WHERE 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     |
+--------+------+--------+------+---------+

このクエリでは、カラムを参照するために、テーブル名にエイリアスを指定して、それぞれのカラムの参照が、どちらのテーブルインスタンスと関連付けられているかをはっきりわかるようにしています。