Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


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     |
+--------+------+--------+------+---------+

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


User Comments
  Posted by Steve Seliquini on November 30, 2003
Depending on when this query was run, Bowser might not be available for mating. :) In this case we can add additional criteria to the where clause to ensure that the animal is actually alive to perform.

select p1.name, p1.gender, p2.name, p2.gender, p1.species
from pet as p1, pet as p2
where p1.species = p2.species and p1.gender = 'f' and p2.gender = 'm'
and p1.death is null and p2.death is null;
+--------+--------+-------+--------+---------+
| name | gender | name | gender | species |
+--------+--------+-------+--------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
+--------+--------+-------+--------+---------+
2 rows in set (0.00 sec)

mysql>

  Posted by on December 30, 2003
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"
-> AND p1.death IS NULL and p2.death IS NULL;
+--------+------+-------+------+---------+
| name | sex | name | sex | species |
+--------+------+-------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
+--------+------+-------+------+---------+
2 rows in set (0.00 sec)

mysql>

The above follows the naming convention and syntax of the tutorial.
  Posted by Allen Moore on December 2, 2004
Windows users, if your LOAD DATA INFILE... command mangles your input, remember to try adding ...LINES TERMINATED BY '\r\n'.
  Posted by Steve Lawson on January 22, 2005
Here's a query that produces a more succinct version of the "Breeding Pairs" table (that ignores the fact that poor Bowser is dead):

SELECT p1.species as Species, p1.name AS Female, p2.name AS Male
FROM pets AS p1, pets AS p2
WHERE p1.species = p2.species
AND p1.sex = 'f' AND p2.sex = 'm'

+---------+--------+--------+
| Species | Female | Male |
+---------+--------+--------+
| cat | Fluffy | Claws |
| dog | Buffy | Fang |
| dog | Buffy | Bowser |
+---------+--------+--------+

Here it is as a LEFT JOIN:

SELECT p1.species, p1.name AS Male, p2.name AS Female
FROM pets AS p1
LEFT JOIN pets AS p2 ON p1.species = p2.species
WHERE p1.sex = 'm' AND p2.sex = 'f'

and with the dead pet filter:

SELECT p1.species AS Species, p1.name AS Female, p2.name AS Male
FROM pets AS p1, pets AS p2
WHERE p1.species = p2.species
AND p1.sex = 'f' AND p2.sex = 'm'
AND p1.death IS NULL AND p2.death IS NULL

  Posted by Mario Vargas on September 25, 2006
The above queries by Steve, using the naming convention in the tutorial to avoid confusion (Steve's table is named "pets" and not "pet"):

Steve's concise example:

SELECT p1.species AS Species, p1.name AS Female, p2.name AS Male
FROM pet AS p1, pet AS p2
WHERE p1.species = p2.species
AND p1.sex = 'f' AND p2.sex = 'm';

+---------+--------+--------+
| Species | Female | Male |
+---------+--------+--------+
| cat | Fluffy | Claws |
| dog | Buffy | Fang |
| dog | Buffy | Bowser |
+---------+--------+--------+

Using an INNER JOIN (instead of a LEFT JOIN). Also defined the Female pets as alias "p1":

SELECT p1.species AS Species, p1.name AS Female, p2.name AS Male
FROM pet AS p1
INNER JOIN pet AS p2 ON p1.species = p2.species
WHERE p1.sex = 'f' AND p2.sex = 'm';

+---------+--------+--------+
| Species | Female | Male |
+---------+--------+--------+
| cat | Fluffy | Claws |
| dog | Buffy | Fang |
| dog | Buffy | Bowser |
+---------+--------+--------+

Using the dead pet filter:

SELECT p1.species AS Species, p1.name AS Female, p2.name AS Male
FROM pet AS p1, pet AS p2
WHERE p1.species = p2.species
AND p1.sex = 'f' AND p2.sex = 'm'
AND p1.death IS NULL AND p2.death IS NULL;

+---------+--------+-------+
| Species | Female | Male |
+---------+--------+-------+
| cat | Fluffy | Claws |
| dog | Buffy | Fang |
+---------+--------+-------+

The above query with the dead filter but using an INNER JOIN:

SELECT p1.species AS Species, p1.name AS Female, p2.name AS Male
FROM pet AS p1
INNER JOIN pet AS p2 ON p2.species = p1.species
WHERE p1.sex = 'f' AND p2.sex = 'm'
AND p1.death IS NULL AND p2.death IS NULL;

+---------+--------+-------+
| Species | Female | Male |
+---------+--------+-------+
| cat | Fluffy | Claws |
| dog | Buffy | Fang |
+---------+--------+-------+
  Posted by Keith Corlett on February 19, 2007
Bob makes an interesting point... and I think it's probably worth descending into the difference between the ON clause and the WHERE clause at this juncture... as it doth tend to cause newbies grief.

So the lonely pets club query...

select p.name
from pet p left join event e
on e.name = p.name
where e.name is null;
+----------+
| name |
+----------+
| Puffball |
+----------+
finds all the pets which do not have a correlated event.

This is a useful query "pattern" for locating non-nullable foreign key values which have broken the foreign key constraint... and it's fast.

But what about this one?

select p.name
from pet p left join event e
on e.name = p.name
and e.name is null;
+----------+
| name |
+----------+
| Fluffy |
| Claws |
| Buffy |
| Fang |
| Bowser |
| Chirpy |
| Whistler |
| Slim |
| Puffball |
+----------+
This query is a truism... it's just a complicated way of returning all pets names. It's a common mistake.

  Posted by Eladio Mora on March 14, 2014
Why not use timestampdiff function instead which is simpler and directly calculate year difference including month and day?

Ex:

SELECT pet.name,
timestampdiff(year,birth,date) as age,
remark
FROM pet INNER JOIN event
ON pet.name = event.name
WHERE event.type = 'litter';
Sign Up Login You must be logged in to post a comment.