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

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

今回は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_idとowners.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 JOINのownersの値も取得できます。

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

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

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

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

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

• 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.nameをowner_nameに変更して値を取得します。

次に、上で取得・連結したレコードの中からrailsでowner_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 JOINのownersの値も取得できます。

と書きましたが、

これはその後の番外で解説した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