Railsで使われるテーブル結合方法について

はじめに

joinsやincludesメソッドを実行した時に生成される、SQLのクエリやテーブル結合の復習も兼ねて投稿しました。

対象者

• なんとなくでActiveRecordのメソッド（joins,includesなど）を使っていた方
• SQLの基本を一通り学んだ方
• RailsでWebアプリ開発をこれから始める方

今回使用するテーブル

usersテーブル

id	name	email
1	taro	[e-mail]
2	hanako	[e-mail]
3	jiro	[e-mail]

---

postsテーブル

id	title	body	user_id
1	post1	this is first post by taro.	1
2	post2	this is second post by taro .	1
3	post1	this is first post by hanako.	2
4	post1	this is first post by jiro.	3

---

commentsテーブル

id	body	post_id	user_id
1	good!	1	1
2	great!	2	2
3	like!	3	1
4	bad...	4	3

---

アソシエーション

class User < ApplicationRecord
  has_many :posts
  has_many :comments
end

class Post < ApplicationRecord
  belongs_to :user
  has_many :comments
end

class Comment < ApplicationRecord
  belongs_to :user
  belongs_to :post
end

• User-Post: 1人のユーザーが複数の投稿を持つ、1対多の関係です
• Post-Comment: 1つの投稿に複数のコメントが付く、1対多の関係です
• Comment-User, Comment-Post: コメントは、特定のユーザーと投稿に紐づくため、それぞれ1対1の関係です

Railsでよく使われるテーブル結合

内部結合(INNER JOIN)

両方のテーブルで共通のデータを持つレコードのみを結合します

（例）usersテーブルとpostsテーブルを結合する

SELECT *
  FROM users -- 結合元の左テーブル
  INNER JOIN posts --　結合先の右テーブル
    ON users.id = posts.user_id -- 左テーブルのidと右テーブルのuser_idが一致するもの

結果セット

id	title	body	user_id	id	body	post_id	user_id
1	taro post1	this is first post by taro.	1	1	good!	1	1
2	taro post2	this is second post by taro .	1	2	great!	2	2
3	hanako post	this is first post by hanako.	2	2	like!	3	2
4	jiro post1	this is first post by jiro .	3	4	bad...	4	3

joinメソッド

内部結合する時に使われるメソッドです。

# 投稿とその投稿に対するコメントを取得する
Post.joins(:comments)

このコードを実行すると、上記と同等のSQL文が生成されます。

SELECT posts.*, comments.* 
FROM posts 
INNER JOIN comments ON posts.id = comments.post_id;

結果セット

id	title	body	user_id	id	body	post_id	user_id
1	taro post1	this is first post by taro.	1	1	good!	1	1
2	taro post2	this is second post by taro .	1	2	great!	2	2
3	hanako post	this is first post by hanako.	2	2	like!	3	2
4	jiro post1	this is first post by jiro .	3	4	bad...	4	3

もし右テーブルに共通のデータが含まれていなかったら

例えば、commentsテーブルに外部キーuser_idがNULLのレコードが存在する場合
（匿名のユーザーによってコメントが作成された場合と仮定します）

    # -- commentsテーブルにデータ挿入
    INSERT INTO comments (body, post_id, user_id) VALUES
      ('good!', 1, 1),
      ('great!', 2, 2),
      ('like!', 3, 2),
      ('bad...', 4, null); -- user_idがnullの場合

（例）usersテーブルとcommentsテーブルを結合する

SELECT *
  FROM users -- 結合元の左テーブル
  INNER JOIN comments --　結合先の右テーブル
    ON users.id = comments.user_id -- 左テーブルのidと右テーブルのuser_idが一致するもの

結果セット

id	name	email	id	body	post_id	user_id
1	taro	[e-mail]	1	good!	1	1
2	hanako	[e-mail]	2	great!	2	2
2	hanako	[e-mail]	3	like!	3	2

bad...というコメントのレコードが結合されてないのがわかります。
このように、共通のデータがいずれかのテーブルに存在しない場合、そのレコードは結合が行われないのが、内部結合（INNER JOIN）です。

内部結合のメリット

• 必要なデータだけを抽出: 不要なデータは含まれないため、処理が高速化されます
• 関連するデータを一括で取得: 複数のテーブルに散らばったデータを一つにまとめることができます
• データの整合性を保つ: 両方のテーブルに存在するデータのみを扱うため、データの整合性を保ちやすくなります

内部結合のデメリット

• 共通するデータがない場合はレコードが出力されない: 両方のテーブルに共通するデータがない場合は、何も出力されません
• 結合条件の設定が重要: 結合条件を間違えると、意図しない結果が得られる可能性があります

---

左外部結合(LEFT OUTER JOIN)

左側のテーブルの全てのレコードを返し、右側のテーブルに該当するレコードがあれば結合。該当するレコードがなければNULL値で埋められます。

（例）usersテーブルとpostsテーブルを結合する

先ほどのINNER JOINの例と同じように、右テーブルにNULL値が含まれるという場合

includesメソッド

User.includes(:comments)

SELECT *
  FROM users -- 結合元の左テーブル
  LEFT OUTER JOIN posts --　結合先の右テーブル
    ON users.id = posts.user_id -- 左テーブルのidと右テーブルのuser_idが一致するもの

結果セット

id	name	email	id	body	post_id	user_id
1	taro	[e-mail]	1	good!	1	1
2	hanako	[e-mail]	2	great!	2	2
2	hanako	[e-mail]	3	like!	3	2
3	jiro	[e-mail]

このようにNULL値のあるコメントも一緒に結合されます。

左外部結合のメリット

• 全てのレコードを網羅できる: 左側のテーブルの全てのレコードが必ず結果に含まれるため、全データの状況を把握できます
• 柔軟な分析: 全てのデータに対して、関連するデータが存在しない場合でも、分析を行うことができます。例えば上記で言うと、全ユーザーに対して投稿へのコメントがあるかどうかを調べたい場合などに有効です

左外部結合のデメリット

• 結果セットが大きくなる可能性: 左側のテーブルの全てのレコードが含まれるため、特にレコード数が多い場合、結果セットが大きくなり、処理時間が長くなる可能性があります
• NULL値の処理: NULL値を考慮した処理が必要になります。集計や比較を行う際に、NULL値をどのように扱うかについて注意が必要です

---

その他のテーブル結合

Railsではあまり使われない結合方法についても挙げます。

右外部結合(RIGHT OUTER JOIN)

右側のテーブルの全てのレコードを返し、左側のテーブルに該当するレコードがあれば結合します。該当するレコードがなければNULL値で埋められます。

（例）commentsテーブルとusersテーブルを結合する

まず前提として、Railsでは右外部結合が直接サポートされていません。

そのため全てのコメントに対して、投稿したユーザーの情報を取得します。ユーザーが存在しない場合は、ユーザーの情報はNULLになります。

Comment.joins("RIGHT OUTER JOIN comments ON comments.user_id = users.id")

もしくは

Comment.joins(:users)

    # -- commentsテーブルにデータ挿入
     INSERT INTO comments (body, post_id, user_id) VALUES
      ('good!', 1, 1),
      ('great!', 2, 2),
      ('like!', 3, 2),
     ('bad...', 4, null) -- user_idがnull

SELECT *
FROM comments
RIGHT OUTER JOIN users
ON comments.user_id = users.id;

結果セット

user_id: 3のjiroさんに紐づくコメントは作成されていないため、コメントのカラムがnullになっています

右外部結合があまり使われない理由

上記の結果セットからもわかるように、内部結合や左外部結合と比較して、直感的に理解しづらい、目的に合ったSQLを記述するのが難しいからだと思います。

右側（子）のテーブルを基準に、データの確認や集計をしたい場合には適しているかもしれませんが、基本的には左外部結合（LEFT OUTER JOIN）で左側のテーブルを基準にした方が関連性を確認しやすいです。

---

完全外部結合(FULL OUTER JOIN)

左側のテーブルと右側のテーブルの、全てのレコードが結果に含まれます。一致するレコードがない場合は、NULL値で表示されます。

（例）全てのユーザーとその投稿数を表示（投稿のないユーザーも、投稿のない記事も表示）

SELECT u.name, COUNT(p.id) AS post_count
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name;

自然結合(NATURAL)

2つのテーブルの共通のカラム名を結合条件として自動的に結合する方法です。共通のカラムの値が一致するレコードのみが結合されます。

（例）ユーザー名と投稿タイトルを結合

-- usersとpostsテーブルに共通のカラム名があると仮定
SELECT u.name, p.title
FROM users u
NATURAL JOIN posts;

統合結合(UNION/UNION ALL)

2つ以上のSELECT文の結果を1つの結果セットに結合します。

• UNION: 重複する行を削除して結合します
• UNION ALL: 重複する行も含めて結合します

（例1）ユーザー名と投稿タイトルを一つの結果セットに結合（重複行を削除）

SELECT name, title
FROM users
UNION
SELECT title, NULL
FROM posts;

（例2）ユーザー名と投稿タイトルを一つの結果セットに結合（重複行も含む）

SELECT name, title
FROM users
UNION ALL
SELECT title, NULL
FROM posts;

余談

本記事を作成するにあたって、以下のツールが役に立ちました。

DB Fiddle

Web上で手軽にSQLを作成・実行できるツールです。
DB Fiddle

Canva（キャンバ）

オンラインで使える無料のグラフィックデザインツールです。
画像を用意するのに困ってましたが、ベン図も手軽に作れました。

おわりに

SQLやテーブル結合のようなデータベースの基本を学ぶことは、バックエンド開発への興味関心を深めることに繋がると改めて思いました。
DB Fiddleのようなツールを使って、手軽にSQL文を書くことから始めてみると良いかもしれませんね。

参考記事

• 理解するポイントはここ！ 外部結合と内部結合の違いを図で説明してみた
• 簡単にSQLを試せるツール「DB Fiddle」がスゴイ
• Canva