MySQL テーブルの結合

この記事の目次

テーブルの結合とは

テーブル同士をある条件で結合することにより、正規化なしの状態を作り出すことです。

※基本的には、正規化ですが、パフォーマンスの問題が解消できないもしくは、できなくなりそうな時、あえて非正規化することもあります。

主キー(Primary Key) 一つの行を特定できる列のことです。

外部キー(Foreign Key) 他テーブルとの関連付けに使う列のこと。

※外部キーは関連付けられた先のテーブルでは主キーになります。

結合処理をする時の記述順序と実行順序

記述順序

select・・取得行(カラム)の指定
from・・対象テーブルの指定
結合処理
where・・・絞り込み条件の指定
group by ・・・グループ化の条件を指定
having・・・グループ化した後の絞り込み条件を指定
order by・・・並び替え条件を指定
limit・・・取得する行数の制限

実行順序

from・・・対象テーブルの指定
結合処理
where・・・絞り込み条件の指定
group by・・・グループ化の条件を指定
select・・・取得行(カラム)の指定
having・・・グループ化した後の絞り込み条件を指定
order by・・・並び替え条件を指定
limit・・・取得する行の制限

※selectの記述順序と実行順序が異なります。

内部結合でテーブルを結合する(inner join)

inner Joinの構文

select
  テーブル名1列名,
  テーブル名2列名...
from
  テーブル名1
inner join
  テーブル名2
on テーブル名1.列名　= テーブル名2.列名;

テーブル1 テーブル2

id	last_name	first_name	prefecture_id	id	name
1	xxxxx	xxxxx	1	1	北海道
2	xxxxx	xxxxx	4	4	宮城県
3	xxxxx	xxxxx	8	8	茨木県
4	xxxxx	xxxxx	27	27	大阪府
5	xxxxx	xxxxx	43	43	熊本県

テーブル1では都道府県がIDで格納されています。

テーブル2には都道府県IDと都道府県名が格納されています。

このテーブルを結合して

ユーザーID,苗字,名前,都道府県名で表示してみます。

select
    デーブル1.id,
    テーブル1.last_name,
    テーブル1.first_name,
    テーブル2.name
from
　　テーブル1
inner join
　　テーブル2
on テーブル1.prefecture_id = テーブル2.id;

すると以下のように結合されて出力されます

テーブル名が沢山あって見づらい時

以下のように記述を変更してSQLを見やすくしても出力結果は同じです。※as は省略可能です。

テーブル1→u

テーブル2→p

以下に表示している太字のテーブル名(asの前)は変更できません。

※inner join →joinもOKですが、外部結合の時は省略できません。

select
    u.id,
    u.last_name,
    u.first_name,
    p.name
from
    テーブル1 as u
inner join
    テーブル2 as p
on u.prefecture_id = p.id;

whrer句を使ってさらに絞り込んだ結合をします。

出力条件は

先程出力したユーザーID,苗字,名前,都道府県名で追加で男性だけのデータを出力してみます。

性別は、テーブル1の列gengerが　男性が1　女性が2　で持っていた場合。

構文

select
  テーブル名1列名,
  テーブル名2列名...
from
  テーブル名1
inner join
  テーブル名2
on テーブル名1.列名　= テーブル名2.列名
where
 絞り込み条件;

それではsqlに記述してみます

select
    u.id,
    u.last_name,
    u.first_name,
    p.name
from
    テーブル1 u
inner join
　　テーブル2 p
on u.prefecture_id = p.id
where u.gender = 1;

出力結果は以下のようにテーブルが結合され男性分のみのデータが出力されます。

外部結合(outer join)

片方のテーブルの情報が全て出力されるテーブルの結合で、もう1つのテーブルに欠落のあるデータを取り扱う結合です。

テーブル1 　テーブル2

id	last_name	id	user_id
1	田中	1	1
1	田中	2	1
2	太田
3	佐藤	3	3
3	佐藤	4	3

←一部のデータが欠けていても出力される

構文

slect
　テーブル名1.列名,
　テーブル名2.列名...
from
　テーブル名1
left outer join
　テーブル名2
on テーブル名1.列名 = テーブル名2.列名;

left outer join・・from句で最初に書いたテーブルをマスタとします。
※leftの場合テーブル名1がマスタとなります。right outer join・・from句で後に書いたテーブルをマスタとします。
※rightの場合はテーブル名2がマスタとなります。

上記の　テーブル1=users テーブル2=orders　とした場合　に結合するには

select
    u.last_name last_name,
    u.id user_id,
    o.user_id order_user_id,
    o.id orer_id    
from
    users u
left outer join
    orders o
on u.id = o.user_id
order by u.id;

となります。

元のデータが違うので表示が異なりますが、マスターのテーブルが全て出力され、もう一つのテーブルの値がないものはnullが表示されているのがわかると思います。

※left outer joinはleft join と略することができます。