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

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

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

## 今回使用するテーブル
### usersテーブル
| id  | name   | email              |
| --- | ------ | ------------------ |
| 1   | taro   | taro@example.com   |
| 2   | hanako | hanako@example.com |
| 3   | jiro   | jiro@example.com   |

---

### 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       |
---

### アソシエーション
```ruby
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)
両方のテーブルで共通のデータを持つレコードのみを結合します

![スクリーンショット 2024-10-06 8.38.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677000/88b321d9-964b-eaf6-9154-3f89962c2d55.png)

#### （例）usersテーブルとpostsテーブルを結合する
```sql
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メソッド
内部結合する時に使われるメソッドです。
```ruby
# 投稿とその投稿に対するコメントを取得する
Post.joins(:comments)
```

このコードを実行すると、[上記と同等のSQL文](#usersテーブルとpostsテーブルを結合する)が生成されます。
```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のレコードが存在する場合
（匿名のユーザーによってコメントが作成された場合と仮定します）

```sql
    # -- 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テーブルを結合する
```sql
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   | taro@example.com   | 1   | good!  | 1       | 1       |
| 2   | hanako | hanako@example.com | 2   | great! | 2       | 2       |
| 2   | hanako | hanako@example.com | 3   | like!  | 3       | 2       |

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

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

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

---

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

![スクリーンショット 2024-10-06 9.47.17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677000/2dac89be-fd22-46e0-bb2f-004f82de3c46.png)


#### （例）usersテーブルとpostsテーブルを結合する
先ほどの[INNER JOINの例](#もし右テーブルに共通のデータが含まれていなかったら)と同じように、右テーブルにNULL値が含まれるという場合

##### includesメソッド
```ruby
User.includes(:comments)
```

```sql
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   | taro@example.com   | 1   | good!  | 1       | 1       |
| 2   | hanako | hanako@example.com | 2   | great! | 2       | 2       |
| 2   | hanako | hanako@example.com | 3   | like!  | 3       | 2       |
| 3   | jiro   | jiro@example.com   |     |        |         |         |

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

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

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

---

## その他のテーブル結合
Railsではあまり使われない結合方法についても挙げます。

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

##### （例）commentsテーブルとusersテーブルを結合する
まず前提として、**Railsでは右外部結合が直接サポートされていません。**

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

```ruby
Comment.joins("RIGHT OUTER JOIN comments ON comments.user_id = users.id")
```
もしくは
```ruby
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
```


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

**結果セット**
![スクリーンショット 2024-10-06 11.08.15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677000/ee836e44-c3a4-733f-01ec-69cff570fa75.png)

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

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

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

---

### 完全外部結合(FULL OUTER JOIN)
**左側のテーブルと右側のテーブルの、全てのレコード**が結果に含まれます。一致するレコードがない場合は、NULL値で表示されます。

#### （例）全てのユーザーとその投稿数を表示（投稿のないユーザーも、投稿のない記事も表示）
```sql
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つのテーブルの**共通のカラム名を結合条件として自動的に結合する**方法です。共通のカラムの値が一致するレコードのみが結合されます。

#### （例）ユーザー名と投稿タイトルを結合
```sql
-- usersとpostsテーブルに共通のカラム名があると仮定
SELECT u.name, p.title
FROM users u
NATURAL JOIN posts;
```

### 統合結合(UNION/UNION ALL)
2つ以上のSELECT文の結果を1つの結果セットに結合します。
- UNION: 重複する行を削除して結合します
- UNION ALL: 重複する行も含めて結合します

#### （例1）ユーザー名と投稿タイトルを一つの結果セットに結合（重複行を削除）
```sql
SELECT name, title
FROM users
UNION
SELECT title, NULL
FROM posts;
```

#### （例2）ユーザー名と投稿タイトルを一つの結果セットに結合（重複行も含む）
```sql
SELECT name, title
FROM users
UNION ALL
SELECT title, NULL
FROM posts;
```

## 余談
本記事を作成するにあたって、以下のツールが役に立ちました。
### DB Fiddle
Web上で手軽にSQLを作成・実行できるツールです。
[DB Fiddle](https://www.db-fiddle.com/)
![スクリーンショット 2024-10-06 11.32.39.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677000/7b336880-29f6-d666-40c3-11956c85091f.png)

### Canva（キャンバ）
オンラインで使える無料のグラフィックデザインツールです。
画像を用意するのに困ってましたが、[ベン図](https://www.canva.com/ja_jp/graphs/venn-diagrams/)も手軽に作れました。
![スクリーンショット 2024-10-06 11.39.08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/677000/3b28e3ed-44f8-01eb-98f5-a591eab0f1c9.png)

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

## 参考記事
- [理解するポイントはここ！ 外部結合と内部結合の違いを図で説明してみた](https://amg-solution.jp/blog/26287)
- [簡単にSQLを試せるツール「DB Fiddle」がスゴイ](https://zenn.dev/sql_geinin/articles/7454c2350259b9)
- [Canva](https://www.canva.com/)