主婦がアプリをリリースするまでの軌跡

自宅でお小遣い稼ぎをしたい主婦がアプリリリースを目指すブログです。

【SQL】テーブル結合 ー内部結合ー

f:id:song-of-life1352607:20170220105406j:plain

こんにちは、マリンです。

今回はSQLの内部結合と外部結合についてです。

まさかこの私がSQLについての記事を書く日が来ようとは…

〇〇結合とは?

まずはそもそものお話しです。

〇〇結合とはなんぞ?

は?そこから?と思った方は飛ばして下さい。


データベースからデータを取り出す際、

基本は「一つのテーブルから条件に合致するデータを取り出す」

だと思います。


ではもし、「欲しいのはこのテーブルのデータだけど、

あっちのテーブルと一致するやつで検索したいんだよなぁ〜」

と言うときは?

SELECT * FROM sample_tables

だけでは探せないですよね。

この場合検索条件に出せるのはここで出たsample_tablesのカラムだけです。


こんな時に使えるのが〇〇結合です。

予め関連づけられたテーブルを一時的に結合させ、

それぞれのカラムを使った検索条件で絞り込んだデータを、

結合させて取得する事が可能になります。


では次からはその結合方法について解説しますので、

その前に例として使うテーブル構造をば。

[cars]

belongs_to :owner

id name owners_id
1 crown 1
2 vellfire 1
3 stepwagon 3
4 legacy 2
5 elgrand

[owners]

has_many :cars

id name
1 ichiro
2 jiro
3 saburo
4 shiro




内部結合

内部結合は、それぞれのテーブルから

指定したカラムの値が一致するレコードだけを結合します。

ポイントは、「一致するレコードだけ」という点です。

一致しないものや、そのカラムがnilのものなどは除外されます。


基本構文

SELECT カラム1, カラム2,... FROM テーブル1
INNER JOIN テーブル2 ON 条件

テーブル1からカラム1、カラム2...を撮ってきた後、

結合の条件に従ってテーブル2からも値を取得し、

一致したものを結合します。

SELECTを指定しなければテーブル1の全カラムが対象になります。


詳細

では例を使って詳細を解説します。

ここからはRailsでの書き方も追加します。

[SQL]

SELECT * FROM cars
INNER JOIN owners
ON cars.owner_id = owners.id

[Rails]

Car.joins(:owner)

carsテーブルとownersテーブルを結合し、

cars.owner_idowners.idの値が一致するレコードをくっつけます。


これを実行すると

irb():001:0> Car.joins(:owner)
  Car Load (6.5ms)  SELECT "cars".* FROM "cars" INNER JOIN "owners" ON "owners"."id" = "cars"."owner_id"
=> #<ActiveRecord::Relation [#<Car id: 1, owner_id: 1, name: "crown", created_at: "2018-04-26 03:41:31", updated_at: "2018-04-30 16:06:58">,
 #<Car id: 2, owner_id: 1, name: "vellfir", created_at: "2018-04-28 16:14:28", updated_at: "2018-04-30 16:07:50">,
 #<Car id: 3, owner_id: 3, name:"Stepwagon", created_at: "2018-04-29 06:33:21", updated_at: "2018-04-30 16:07:58",
 #<Car id: 4, owner_id: 2, name: "legacy", created_at: "2018-04-29 06:33:38", updated_at: "2018-04-30 16:08:08">]>

こんな感じで条件に一致するレコードが取得できます。

一致していない(owner_idがない)carsの

id: 5, name: "elgrand"は除外されています。


基準となるのがFROMの方のテーブルなのでcarsのレコードが表示されていますが、

結合されているのでINNER JOINownersの値も取得できます。

そちらも検証してみました。

INNER JOINの方のカラムを取得する場合にはSELECTでそのカラムを指定する必要があります。

 詳しくは補足をご覧ください。


【番外】結合した方のカラムの値を取得してみる

結合後のレコードからownersnameを取得してみます。

  • nameというカラム名が両テーブルでかぶっているので、結合時に別名をつける。

[SQL]

SELECT cars.*, owners.name AS owner_name
FROM "cars"
INNER JOIN "owners"
ON cars.owner_id = owners.id

[Rails]

def self.list_by_ownerid
    @cars = Car.joins(:owner).select("cars.*, owners.name as owner_name")
end

SELECTの中でowner.nameowner_nameに変更して値を取得します。




次に、上で取得・連結したレコードの中からrailsowner_nameを取り出します。

Car.list_by_ownerid.map {|car| car.owner_name}

上記を実行すると

SELECT cars.*, owners.name as owner_name FROM "cars"
 INNER JOIN "owners" 
ON "owners"."id" = "cars"."owner_id"
=> ["ichiro", "ichiro", "saburo", "jiro"]

と結果が出るはずです。


並び順などに注目していただくとわかりますが、

carsテーブルが基準となっているので、

車が2台登録されている"ichiro"は2回出て、

並び順はcars.idの順番に並んでいます。


まとめ

最後に内部結合のポイントをまとめておきます。

  • 値が一致したレコードのみを取得、結合する

  • 基準となるテーブルは右側(FROM)のテーブル

  • 基準テーブルは並び順などに関係してくる

基準テーブルについては結合全体で関係してくることです。

次は外部結合について書きたいと思います。


補足

「内部結合/詳細」の中で

基準となるのがFROMの方のテーブルなのでcarsのレコードが表示されていますが、

結合されているのでINNER JOINownersの値も取得できます。

と書きましたが、

これはその後の番外で解説したSELECT句に結合する方のカラムを指定した時のみ、

そのカラムの値を使用できるようです。


試しにOwnerモデルの方でCarsをjoinして以下のようにやってみましたが、

NoMethodErrorが出ました。

# Model
def self.owner_list
    @owner = Owner.joins(:cars)
end

# View
# dealership: ディーラー名
<% Owner.owner_list.each do |owner| %>
    <%= owner.dealership %>
<% end %>

モデルを以下のように書き換えるときちんと表示できました。

def self.owner_list
    @owner = Owner.joins(:cars).select("owners.*, cars.dealership")
end