SQLの勉強_テーブルの結合編

正規化

テーブルを分けて、情報の重複をなくす作業。

usersテーブルではprefecture_idを持っているが、ユーザーでもprefectureを持っているという場合、、重複を持っているといえる。

この場合、新たにprefectureテーブルを作り、usersテーブルにprefecture_idを参照するようにすれば、データの重複がなくなる。

正規化するメリット

• データ管理が楽になる。
  • 上記の例の場合、県名が変わる場合は、全部更新する必要がある。
  • しかし正規化をしていれば、prefecture_idの一箇所を編集するだけで対応が完了する。

テーブルの結合
テーブル同士をある条件で列を結合（合体）させ、正規化なしの状態にする。
基本は正規化するが、パフォーマンスの問題からあえて非正規化するパターンもある。

主キー・外部キー

主キー：Primaryキー。ひとつの行を特定できる列のこと。
usersの場合であれば、userごとに振られているidの列が該当する。

Ex:idが1であれば山田さんであるというように特定させられる・

外部キー：他のテーブルとの関連付けに使う列。
関連づけられたさきのテーブルでは、主キーになる。

上記のusersテーブルの場合、prefecture_id（県）を区別するidが、該当する。

リレーションシップの関係性

• 1対多

一人のユーザテーブルと、orders(注文)テーブルがあるとすると。
一人のユーザが、複数の注文を行う。
これは、1対多の関係といえる。

• 多対多

商品を管理するテーブルと、カテゴリテーブルが存在するとする。
ユーザーと、ユーザーの操作権限とか。
（人事部Aさんは、給与設定、明細確認、新規ユーザー追加など。）
中間のテーブル内に、カテゴリが複数用意されていると見る事ができる。

また、ユーザーと電話番号というような、
1対1というような関係も存在する。

テーブル内部結合_innor_join

顧客一覧と合わせて、都道府県名で出力したいとする。

まずは、結合なしで普通にデータを取得してみる。

select
  id,
  last_name,
  first_name,
  prefecture_id
from
    users;

取り出す事ができた。

次に、内部結合をして、prefecture_idを県名にする。県名は別のテーブルにあるため。

select
  users.id,
  users.last_name,
  users.first_name,
  prefectures.name
from
    users
inner join
  prefectures
on users.prefecture_id = prefectures.id;

以下の内容は、usersテーブルの外部キーprefecture_idと、
prefecturesテーブルの主キーid(prefectures.id)を結合する

inner join
  prefectures
on users.prefecture_id = prefectures.id;

また、列名の部分は、どちらのテーブルを参照しているかわからないため、列名の最初にテーブル名.をつけている。
users.idなら、usersテーブルのidである。

実行すれば、以下のように取得できる。

また、テーブル名はasを用いて、別名にする事ができる。
ちなみに、asは省略可能で、innner joinは、joinと省略可能。ただし、外部ジョインと区別するために明示することが多い。

select
  u.id,
  u.last_name,
  u.first_name,
  p.name
from
    users as u
inner join
  prefectures as p
on u.prefecture_id = p.id;

内部結合＆絞り込み

結合したテーブルを、where句と組み合わせて検索してみる。

innerjoinの後の末尾の部分に、where句をいれてやる事で実現できる。

select
  u.id,
  u.last_name,
  u.first_name,
  p.name
from
    users as u
inner join
  prefectures as p
on u.prefecture_id = p.id
where u.gender = 2;

内部結合には、inner joinを使用する。
以下のような記載だと、ordersテーブルと、ユーザーテーブルのuseridを結合することができる。

select * from orders o innner join users u on o.user_id = uid;

ここからwhere句で絞る事ができる。

select * from orders o innner join users u on o.user_id = uid
where u.prefecture_id = 13;

というような形。

外部結合 outer join

• 片方のテーブルの情報が全て出力されるテーブルを結合する。
• inner joinの場合は、両方に共通するものがある時に出力する。
• 外部結合すると、共通項となる値がない場合も、NULLで出力する。

left outer join : from句で最初に記載したテーブルをマスタ（全部出す側）にする。

right outer join : from句で最後に記載したテーブルをマスタ（全部出す側）にする。

userとorderを結合させる。(内部結合)

select
 u.last_name lastname,
 u.id user_id
 o.user_id order_user_id,
 o.id order_id
from
  users u
inner join
  orders o
on u.id = o.user_id
order by u.id;

userとorderを結合させる。(外部結合)

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

outer join(外部結合)の応用

外部結合,left outer joinを使用する。

商品一覧は、product tableを用いる。

select
  *
from
  products p
left outer join
  order_details od
on p.id = od.product_id
group by p.id;