select の from にテーブルを並べると、テーブル同士が総当たりで結合されます。さらにwhereで検索条件を指定して絞り込むと、内部結合した場合と同じような出力を得ることができます。
結合するテーブル
mysql> select * from kittens; +----+-------+---------+ | id | name | type_id | +----+-------+---------+ | 1 | mii | 1 | | 2 | tora | 2 | | 3 | shiro | 1 | | 4 | kuro | 4 | +----+-------+---------+ 4 rows in set (0.02 sec) mysql> select * from types; +----+---------------+ | id | name | +----+---------------+ | 1 | mike | | 2 | scottish-fold | | 3 | egyptian mau | +----+---------------+ 3 rows in set (0.01 sec)
from にテーブルを並べる
二つのテーブルを総当たりした結果が返されます。
mysql> select * from kittens, types; +----+-------+---------+----+---------------+ | id | name | type_id | id | name | +----+-------+---------+----+---------------+ | 1 | mii | 1 | 1 | mike | | 2 | tora | 2 | 1 | mike | | 3 | shiro | 1 | 1 | mike | | 4 | kuro | 4 | 1 | mike | | 1 | mii | 1 | 2 | scottish-fold | | 2 | tora | 2 | 2 | scottish-fold | | 3 | shiro | 1 | 2 | scottish-fold | | 4 | kuro | 4 | 2 | scottish-fold | | 1 | mii | 1 | 3 | egyptian mau | | 2 | tora | 2 | 3 | egyptian mau | | 3 | shiro | 1 | 3 | egyptian mau | | 4 | kuro | 4 | 3 | egyptian mau | +----+-------+---------+----+---------------+ 12 rows in set (0.03 sec)
whereを追加
総当たりした結果をwhereの条件で絞り込んだ結果が返されます。
mysql> select * from kittens, types where kittens.type_id = types.id; +----+-------+---------+----+---------------+ | id | name | type_id | id | name | +----+-------+---------+----+---------------+ | 1 | mii | 1 | 1 | mike | | 3 | shiro | 1 | 1 | mike | | 2 | tora | 2 | 2 | scottish-fold | +----+-------+---------+----+---------------+ 3 rows in set (0.00 sec)
これは内部結合を使った以下のSQL文と同じ結果になります。
mysql> select * from kittens join types on kittens.type_id = types.id; +----+-------+---------+----+---------------+ | id | name | type_id | id | name | +----+-------+---------+----+---------------+ | 1 | mii | 1 | 1 | mike | | 3 | shiro | 1 | 1 | mike | | 2 | tora | 2 | 2 | scottish-fold | +----+-------+---------+----+---------------+ 3 rows in set (0.00 sec)
