Rails における内部結合、外部結合まとめ

## 0. はじめに
Qiitawはじめ、さまざまなところでRailsのActiveRecordの内部結合や外部結合に関する記事がありますが、それらがまとまって存在していると良いリファレンスとなるのではないかと思い本記事を作成しました。  
また、Rails5で動作確認しておきながら、Rails5から追加された`left_outer_joins`などは載せてません。今後、載せていきたいと思います。
`group by`やサブクエリ（副問い合わせ）に関しては下記もご参照ください。

 - [ActiveRecordにおけるGROUP BYの使い方](https://qiita.com/yuyasat/items/e26bcf0eb2c89c63db9d)
 - [ActiveRecordでサブクエリ（副問い合わせ）と内部結合](https://qiita.com/yuyasat/items/d7f86e1f9bdd5ed3a876)

### 0-1. RubyとRailsとPostgreSQLのバージョン
```
$ ruby -v
ruby 2.2.4p230 (2015-12-16 revision 53155) [x86_64-darwin15]
$ rails -v
Rails 5.0.0

=> SELECT version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.3 on x86_64-apple-darwin15.4.0, compiled by Apple LLVM version 7.3.0 (clang-703.0.31), 64-bit
```
### 0-2. 今回扱うERD
|ERD|説明|
|-----------|----|
|<img src="https://qiita-image-store.s3.amazonaws.com/0/121304/9ec02211-1e4e-f25e-ec8a-cdf6f9099e15.png" width="150px">|女優テーブルがある。<br>女優テーブルは映画テーブルを持つ。<br>映画テーブルは、映画に関連するキーワードを入れたタグテーブルを持つ。|
### 0-3. モデル
```ruby:Actress
class Actress < ApplicationRecord
  has_many :movies
end
```
```ruby:Movie
class Movie < ApplicationRecord
  has_many :tags
  belongs_to :actress
end
```
```ruby:Tag
class Tag < ApplicationRecord
  belongs_to :movie
end
```
### 0-4. 今回扱うデータ
```text:actress
SELECT id, name FROM actresses;
 id |    name
----+------------
  1 | 多部未華子
  2 | 佐津川愛美
  3 | 新垣結衣
  4 | 堀北真希
  5 | 吉高由里子
  6 | 悠城早矢
```
```text:movies
SELECT id, actress_id, title, year FROM movies;
 id | actress_id |        title        | year
----+------------+---------------------+------
  1 |          2 | 蝉しぐれ            | 2005
  2 |          1 | 夜のピクニック      | 2006
  3 |          4 | ALWAYS 三丁目の夕日 | 2005
  4 |          2 | 忍道-SHINOBIDO-     | 2012
  5 |          2 | 貞子vs伽椰子        | 2016
  6 |          4 | 県庁おもてなし課    | 2013
  7 |          5 | 真夏の方程式        | 2013
```
```text:tags
SELECT id, movie_id, key FROM tags;
 id | movie_id |    key
----+----------+------------
  1 |        1 | 時代劇
  2 |        1 | 子役
  3 |        3 | 昭和
  4 |        5 | ホラー
  5 |        7 | ミステリー
  6 |        7 | 夏
  7 |        6 | 公務員
  8 |        6 | 地方活性
  9 |        1 | 夏
```

### 0-5. 補足
- arel_tableを使う方法もありますが、今回は省略しました。
- 今回、左部分除外結合という聞きなれない言葉を使わせていただきました。
    - これは、[Visual Representation of SQL Joins](http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins)にある「LEFT JOIN EXCLUDING INNER JOIN」の日本語として当てはめたものです。
        - 適切な日本語をご存知の方は教えて下さい。
    - 3と6における左部分除外結合のベン図を載せます。

|actressesとmoviesの左部分除外結合<br>（moviesのないactressesを抽出する）|actressesとmoviesとtagsの左部分除外結合<br>（tagsのないactressesを抽出する）|
|:-----------:|:----:|
|<img src="https://qiita-image-store.s3.amazonaws.com/0/121304/a49d4ead-52a3-0a75-849b-1424e2545594.png" height="120">|<img src="https://qiita-image-store.s3.amazonaws.com/0/121304/28ad63e0-3353-cb6d-a7d5-91a53f25505d.png" height="120">|
※今回のデータの場合、moviesは必ずactress_idをもつし、tagsは必ずmovie_idを持つので、上記のベン図は厳密には違いますが、より一般性を持たせた図にしました。

## 1. actressesとmoviesの内部結合  
### 1-1. joins（selectなし）を使う
- actressesのカラムしか持ってきてくれない。
- 結合先の情報が不要な場合はこれで良い。

```ruby:Rails
Actress.joins(:movies)
```

```sql:SQL
SELECT
  "actresses".*
FROM
  "actresses"
  INNER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"
```
### 1-2. joins（selectあり）を使う
- 結合先（この場合はmovies）のカラムも取得できる。

```ruby:Rails
Actress.joins(:movies).select("actresses.*, movies.*").first.title
=> "夜のピクニック"
```

```sql:SQL
SELECT
  actresses.*,
  movies.*
FROM
  "actresses"
  INNER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"
ORDER BY "actresses"."id" ASC
LIMIT 1
```
- 実行してみます。

```ruby:execute
Actress.joins(:movies).select("actresses.*, movies.*").each do |actress_movie|
  puts "#{actress_movie.name} : #{actress_movie.title}"
end
# =>
  Actress Load (1.8ms)  SELECT actresses.*, movies.* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
```
```text:result
佐津川愛美 : 蝉しぐれ
多部未華子 : 夜のピクニック
堀北真希 : ALWAYS 三丁目の夕日
佐津川愛美 : 忍道-SHINOBIDO-
佐津川愛美 : 貞子vs伽椰子
堀北真希 : 県庁おもてなし課
吉高由里子 : 真夏の方程式
```
- カラム名がかぶる場合は、select内で名前付けをする。

```ruby:Rails
Actress.joins(:movies).select("actresses.*, movies.id AS movie_id").last.id
# =>
  Actress Load (0.9ms)  SELECT  actresses.*, movies.id AS movie_id FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"  ORDER BY "actresses"."id" DESC LIMIT 1
=> 5 # actressのid

Actress.joins(:movies).select("actresses.*, movies.id AS movie_id").last.movie_id
# =>
  Actress Load (0.6ms)  SELECT  actresses.*, movies.id AS movie_id FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"  ORDER BY "actresses"."id" DESC LIMIT 1
=> 7 # movieのid
```
### 1-3. joinsに関する補足
- mergeを使うと結合先のモデルのscopeを使うことができる。

```ruby:execute
# 実用的には普通にモデル内に書けばいい。
Movie.class_eval { scope :year_2013, -> { where(year: 2013) } }
=> :year_2013
# ベタに書くと、Actress.joins(:movies).where(movies: { year: 2013 })
Actress.joins(:movies).merge(Movie.year_2013)
# =>
  Actress Load (0.5ms)  SELECT "actresses".* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" WHERE "movies"."year" = $1  [["year", 2013]]
=> [#<Actress:0x007fed0bc19eb8
  id: 4,
  name: "堀北真希",
  created_at: Sat, 03 Sep 2016 06:51:29 UTC +00:00,
  updated_at: Sat, 03 Sep 2016 06:51:29 UTC +00:00>,
 #<Actress:0x007fed0bba9820
  id: 5,
  name: "吉高由里子",
  created_at: Sat, 03 Sep 2016 06:51:29 UTC +00:00,
  updated_at: Sat, 03 Sep 2016 06:51:29 UTC +00:00>]
```
- 複数個`select`して`count`をするとSQLが壊れる・・・。注意！
    - selectしない場合はOK。

```ruby:select使わないときやselect内が１個の時はOK
Actress.joins(:movies).count
# =>
   (2.5ms)  SELECT COUNT(*) FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
=> 7
Actress.joins(:movies).select("actresses.*").count
# =>
   (0.5ms)  SELECT COUNT(actresses.*) FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
=> 7
Actress.joins(:movies).select("movies.*").count
# =>
   (0.5ms)  SELECT COUNT(movies.*) FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
=> 7
```
```ruby:selectしてcountするとNG
Actress.joins(:movies).select("actresses.*, movies.*").count
# =>
SELECT COUNT(actresses.*, movies.*) FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  function count(actresses, movies) does not exist
LINE 1: SELECT COUNT(actresses.*, movies.*) FROM "actresses" INNER J...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
: SELECT COUNT(actresses.*, movies.*) FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
from /Users/user_name/.rbenv/versions/2.2.4/lib/ruby/gems/2.2.0/gems/activerecord-5.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:598:in `async_exec'
```
- `size`や`length`を使うと良い。

```ruby:selectしてsizeするとacctressの数が取れ、SQLもCOUNT(*)となっている
Actress.joins(:movies).select("actresses.*, movies.*").size
# =>
   (0.5ms)  SELECT COUNT(*) FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
=> 7
```
```ruby:selectしてlengthするとacctressの数が取るが、SQLはactresses.*となっている
Actress.joins(:movies).select("actresses.*, movies.*").length
# =>
  Actress Load (0.5ms)  SELECT actresses.*, movies.* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
=> 7
```
- 普段あまり意識しない`count`、`size`、`length`だが、SQLが絡むと挙動が違うので注意が必要。

### 1-4. joins + preloadを使う
- 内部結合した結果で絞り込んで結合先を取得してくれる。
- クエリは各テーブルごとに走る。
- やや扱いにくい印象。
    - 内部結合の際は、[1-5. joins + eager_loadを使う](#1-5-joins--eager_loadを使う)か[1-6. joins + includesを使う](#1-6-joins--includesを使う)を使うのが無難そう。
    - preloadに関しては、`GROUP BY`したときの挙動もeager_loadと異なるので、[ActiveRecordにおけるGROUP BYの使い方](https://qiita.com/yuyasat/items/e26bcf0eb2c89c63db9d#2-2-%E5%90%84%E5%A5%B3%E5%84%AA%E3%81%AE%E6%98%A0%E7%94%BB%E6%95%B0%E3%82%92%E7%9F%A5%E3%82%8A%E3%81%9F%E3%81%84%E5%90%8D%E5%89%8D%E3%81%A7%E8%A1%A8%E7%A4%BAjoin%E3%81%97%E3%81%AA%E3%81%84%E6%96%B9%E6%B3%95) もご覧ください。

```ruby:Rails
Actress.joins(:movies).preload(:movies)
```
- [2-3.　preloadを使う](#2-3-preloadを使う)と違い、IN句で絞り込まれているのがわかる。

```sql:SQL
  Actress Load (0.4ms)  SELECT "actresses".* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
  Movie Load (0.4ms)  SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (2, 1, 4, 5)
```
- 実行する際にはいささか注意が必要。
    - 以下のような場合、distinctをつける。（uniqでもいいが、Rails5では、DEPRECATION WARNINGが出る。5.1で削除予定。）

```ruby:execute
Actress.joins(:movies).preload(:movies).distinct.each do |actress|
  puts "#{actress.name}:"
  actress.movies.each { |movie| puts "  #{movie.title}" }
end
# =>
  Actress Load (0.5ms)  SELECT DISTINCT "actresses".* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
  Movie Load (0.4ms)  SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (5, 2, 4, 1)
```
```text:result
吉高由里子:
  真夏の方程式
佐津川愛美:
  蝉しぐれ
  忍道-SHINOBIDO-
  貞子vs伽椰子
堀北真希:
  ALWAYS 三丁目の夕日
  県庁おもてなし課
多部未華子:
  夜のピクニック
```
- distinctをつけないと・・・。

```ruby:execute
Actress.joins(:movies).preload(:movies).each do |actress|
  puts "#{actress.name}:"
  actress.movies.each { |movie| puts "  #{movie.title}" }
end
# =>
  Actress Load (0.5ms)  SELECT "actresses".* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
  Movie Load (0.4ms)  SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (2, 1, 4, 5)
```
```text:result
佐津川愛美:
  蝉しぐれ
  忍道-SHINOBIDO-
  貞子vs伽椰子
多部未華子:
  夜のピクニック
堀北真希:
  ALWAYS 三丁目の夕日
  県庁おもてなし課
佐津川愛美:
  Movie Load (0.4ms)  SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" = $1  [["actress_id", 2]]
  蝉しぐれ
  忍道-SHINOBIDO-
  貞子vs伽椰子
佐津川愛美:
  Movie Load (0.3ms)  SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" = $1  [["actress_id", 2]]
  蝉しぐれ
  忍道-SHINOBIDO-
  貞子vs伽椰子
堀北真希:
  Movie Load (0.3ms)  SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" = $1  [["actress_id", 4]]
  ALWAYS 三丁目の夕日
  県庁おもてなし課
吉高由里子:
  真夏の方程式
```
- joinsを使っているからか、where句やmergeによる結合先のscopeが使える。

```ruby:Rails
Actress.joins(:movies).preload(:movies).where(movies: { year: 2013 })
Actress.joins(:movies).preload(:movies).merge(Movie.year_2013)
Actress.joins(:movies).merge(Movie.year_2013).preload(:movies)
```
- ただし、結合しているわけではないので使い方に注意が必要。
    - 例えばこんな感じに使うと・・・。

```ruby:execute
Actress.joins(:movies).merge(Movie.year_2013).preload(:movies).each do |actress|
  puts "#{actress.name}"
  actress.movies.each { |movie| puts "  #{movie.title}" }
end
# =>
  Actress Load (0.3ms)  SELECT "actresses".* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" WHERE "movies"."year" = $1  [["year", 2013]]
  Movie Load (0.4ms)  SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (4, 5)
```
 - せっかくmoviesの条件を絞ったのに出てきてしまう。（当たり前だけど）

```text:result
堀北真希
  ALWAYS 三丁目の夕日
  県庁おもてなし課
吉高由里子
  真夏の方程式
```
### 1-5. joins + eager_loadを使う
- [2-2. eager_loadを使う](#2-2-eager_loadを使う)で後述するように単にeager_loadを使うと外部結合になるが、joinsと組み合わせると内部結合になる。
- 結合先も取得してくれ、actressごとにmoviesを取得できる。
- [1-6. joins + includesを使う](#1-6-joins--includesを使う)と同じ挙動

```ruby:Rails
Actress.joins(:movies).eager_load(:movies)
```
```sql:SQL
SELECT
  "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
  "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5
FROM
  "actresses"
  INNER JOIN
    "movies" ON "movies"."actress_id" = "actresses"."id"
```
- 実行してみます。

```ruby:execute
Actress.joins(:movies).eager_load(:movies).each do |actress|
  puts "#{actress.name} "
  actress.movies.each { |movie| puts "  #{movie.title}" }
end
# =>
  SQL (0.5ms)  SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
```
```text:result
佐津川愛美
  蝉しぐれ
  忍道-SHINOBIDO-
  貞子vs伽椰子
多部未華子
  夜のピクニック
堀北真希
  ALWAYS 三丁目の夕日
  県庁おもてなし課
吉高由里子
  真夏の方程式
```
- ただし、`each`ブロック内で`count`などの集合関数を使うと、旨味が減る。

```ruby:execute
Actress.joins(:movies).eager_load(:movies).each do |actress|
  puts "#{actress.name}: #{actress.movies.count}"
end
# =>
  SQL (0.6ms)  SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
   (2.0ms)  SELECT COUNT(*) FROM "movies" WHERE "movies"."actress_id" = $1  [["actress_id", 2]]
佐津川愛美: 3
   (0.4ms)  SELECT COUNT(*) FROM "movies" WHERE "movies"."actress_id" = $1  [["actress_id", 1]]
多部未華子: 1
   (0.3ms)  SELECT COUNT(*) FROM "movies" WHERE "movies"."actress_id" = $1  [["actress_id", 4]]
堀北真希: 2
   (0.3ms)  SELECT COUNT(*) FROM "movies" WHERE "movies"."actress_id" = $1  [["actress_id", 5]]
吉高由里子: 1
```
- `count`を使うのではなく、`size`や`length`を使うと良い。

```ruby:execute
Actress.joins(:movies).eager_load(:movies).each do |actress|
  puts "#{actress.name}: #{actress.movies.size}"
end
# =>
  SQL (0.6ms)  SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
佐津川愛美: 3
多部未華子: 1
堀北真希: 2
吉高由里子: 1
```

### 1-6. joins + includesを使う
- 結合先であるmoviesも一気に取得してくれる。
- クエリは内部結合で１回のみ。
- [1-4. joins + preloadを使う](#1-4-joins--preloadを使う)と違いdistinctしなくて良い。
- [1-5. joins + eager_loadを使う](#1-5-joins--eager_loadを使う)と同じ挙動。

```ruby:Rails
Actress.joins(:movies).includes(:movies)
```
```sql:SQL
SELECT
  "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
  "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5
FROM
  "actresses"
  INNER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"
```
- 実行してみます。

```ruby:execute
Actress.joins(:movies).includes(:movies).each do |actress|
  puts "#{actress.name}"
  actress.movies.each { |movie| puts "  #{movie.title}" }
end
# =>
  SQL (0.6ms)  SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
```
```text:result
佐津川愛美
  蝉しぐれ
  忍道-SHINOBIDO-
  貞子vs伽椰子
多部未華子
  夜のピクニック
堀北真希
  ALWAYS 三丁目の夕日
  県庁おもてなし課
吉高由里子
  真夏の方程式
```
- where句やmergeによる結合先のscopeが使えます。
    - 以下の３つは同じ挙動。

```ruby:Rails
Actress.joins(:movies).includes(:movies).where(movies: { year: 2013 })
Actress.joins(:movies).includes(:movies).merge(Movie.year_2013)
Actress.joins(:movies).merge(Movie.year_2013).includes(:movies)
```

## 2. actressesとmoviesの左外部結合
### 2-1. joinsを使う
- joinsの中にSQLを書く必要がある。
- actressesのカラムだけ必要な場合は、selectは不要。

```ruby:execute
Actress.joins("LEFT OUTER JOIN movies ON actresses.id = movies.actress_id")
# =>
  Actress Load (0.4ms)  SELECT "actresses".* FROM "actresses" LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
```
- moviesのカラムが必要な場合は、selectが必要。

```ruby:execute
Actress.joins(
  "LEFT OUTER JOIN movies ON actresses.id = movies.actress_id"
).select("movies.*")
# =>
  Actress Load (0.4ms)  SELECT movies.* FROM "actresses" LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
```
- actressesとmoviesの両方のカラムが必要な場合は、ふたつ書く。
- カラム名がかぶる場合は、1-2に同じ。
- 順番も変わらないので、左外部結合として一般に想像できる結果が返ってくる。

```ruby:execute
Actress.joins(
  "LEFT OUTER JOIN movies ON actresses.id = movies.actress_id"
).select("actresses.*, movies.*").each do |actress_movie|
  puts "#{actress_movie.name}: #{actress_movie.title}"
end
# =>
  Actress Load (0.5ms)  SELECT actresses.*, movies.* FROM "actresses" LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
``` 

```text:result
佐津川愛美: 蝉しぐれ
多部未華子: 夜のピクニック
堀北真希: ALWAYS 三丁目の夕日
佐津川愛美: 忍道-SHINOBIDO-
佐津川愛美: 貞子vs伽椰子
堀北真希: 県庁おもてなし課
吉高由里子: 真夏の方程式
悠城早矢:
新垣結衣:
```

### 2-2. eager_loadを使う
- 結合先も取得してくれる。
- あくまでactressesがベースとなっているので、発行するSQLとしては純粋な左外部結合だが、[2-1. joinsを使う](#2-1-joinsを使う)のように外部結合先の値を`.first.title`のように取り出すことができない。

```ruby:Rails
Actress.eager_load(:movies)
```
```sql:SQL
SELECT
  "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
  "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5
FROM
  "actresses"
  LEFT OUTER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"
```
- eager_loadで取得した後にeachブロック内で`actress.movies`を使うときにSQLクエリを発行せずにmoviesを取得できる。

```ruby:execute
Actress.eager_load(:movies).each do |actress|
  puts "#{actress.name}:"
  actress.movies.each { |movie| puts "  #{movie.title}" }
end
# =>
  SQL (1.3ms)  SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
```
```text:result
佐津川愛美:
  蝉しぐれ
  忍道-SHINOBIDO-
  貞子vs伽椰子
多部未華子:
  夜のピクニック
堀北真希:
  ALWAYS 三丁目の夕日
  県庁おもてなし課
吉高由里子:
  真夏の方程式
悠城早矢:
新垣結衣:
```
- firstとかを使うと旨味が出ない（こういう使い方を想定していないのか）
    - ２回クエリが発行されてしまう。

```ruby:execute
Actress.eager_load(:movies).first.movies
# =>
  SQL (0.5ms)  SELECT  DISTINCT "actresses"."id", "actresses"."id" AS alias_0 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" ORDER BY "actresses"."id" ASC LIMIT $1  [["LIMIT", 1]]
  SQL (0.5ms)  SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" WHERE "actresses"."id" = 1 ORDER BY "actresses"."id" ASC
```

### 2-3. preloadを使う
- actressesとmoviesに対して２回クエリが発行される。
- SQL的には左外部結合ではないが、情報としては同等のものが得られる。
- [2-4. includes（referencesなし）](#2-4-includesreferencesなしを使う)と同じ挙動。

```ruby:Rails
Actress.preload(:movies)
```
```sql:SQL
  Actress Load (0.3ms)  SELECT "actresses".* FROM "actresses"
  Movie Load (0.4ms)  SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (1, 2, 3, 4, 5, 6)
```
```ruby:execute
Actress.preload(:movies).each do |actress|
  puts "#{actress.name} "
  actress.movies.each { |movie| puts "  #{movie.title}" }
end
# =>
  Actress Load (0.4ms)  SELECT "actresses".* FROM "actresses"
  Movie Load (0.4ms)  SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (1, 2, 3, 4, 5, 6)
```
```text:result
多部未華子
  夜のピクニック
佐津川愛美
  蝉しぐれ
  忍道-SHINOBIDO-
  貞子vs伽椰子
新垣結衣
堀北真希
  ALWAYS 三丁目の夕日
  県庁おもてなし課
吉高由里子
  真夏の方程式
悠城早矢
```
- preloadした先でwhere句は使えないので、[2-5. includes（referencesあり）](#2-5-includesreferencesありを使う)に記述したincludes + where句とは少し違う。

```ruby.execute
Actress.preload(:movies).where(movies: { year: 2013 }).each do |actress|
  puts "#{actress.name} "
  actress.movies.each { |movie| puts "  #{movie.title}" }
end
# =>
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "movies"
LINE 1: SELECT "actresses".* FROM "actresses" WHERE "movies"."year" ...
                                                    ^
: SELECT "actresses".* FROM "actresses" WHERE "movies"."year" = $1
from /Users/user_name/.rbenv/versions/2.2.4/lib/ruby/gems/2.2.0/gems/activerecord-5.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:657:in `prepare'
```

### 2-4. includes（referencesなし）を使う
- SQLクエリが２回走る。
- SQL的には外部結合ではないが、情報としては同等のものが得られる。
- [2-3. preloadを使う](#2-3-preloadを使う)と同じ挙動

```ruby:Rails
Actress.includes(:movies)
```

```ruby:SQL
  Actress Load (0.3ms)  SELECT "actresses".* FROM "actresses"
  Movie Load (0.4ms)  SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (1, 2, 3, 4, 5, 6)
```
- 実行してみます。

```ruby:execute
Actress.includes(:movies).each do |actress|
  puts "#{actress.name}:"
  actress.movies.each { |movie| puts "  #{movie.title}" }
end
# =>
  Actress Load (0.5ms)  SELECT "actresses".* FROM "actresses"
  Movie Load (0.6ms)  SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (1, 2, 3, 4, 5, 6)
```
```text:result
２−３と同じ。
```

### 2-5. includes（referencesあり）を使う
- includesした先も持ってきてくれる。
- 結果、SQLクエリは１回で済む。
- [2-2. eager_load](#2-2-eager_loadを使う)と同じ挙動。

```ruby:Rails
Actress.includes(:movies).references(:movies)
```

```sql:SQL
SELECT
  "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2,  "actresses"."updated_at" AS t0_r3,
  "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5
FROM
  "actresses"
  LEFT OUTER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"
```
- 実行してみる

```ruby:result
Actress.includes(:movies).references(:movies).each do |actress|
  puts "#{actress.name}:"
  actress.movies.each { |movie| puts "  #{movie.title}" }
end
# =>
  SQL (0.6ms)  SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
```
```text:result
佐津川愛美:
  蝉しぐれ
  忍道-SHINOBIDO-
  貞子vs伽椰子
多部未華子:
  夜のピクニック
堀北真希:
  ALWAYS 三丁目の夕日
  県庁おもてなし課
吉高由里子:
  真夏の方程式
悠城早矢:
新垣結衣:
```

- includes先にwhere句をつけるとreferencesが自動的に付与され、クエリは１回で済む。

```ruby:execute
Actress.includes(:movies).where(movies: { year: 2013 }).each do |actress|
  puts "#{actress.name}:"
  actress.movies.each { |movie| puts "  #{movie.title}" }
end
# =>
  SQL (1.1ms)  SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" WHERE "movies"."year" = $1  [["year", 2013]]
```

```text:result
堀北真希:
  県庁おもてなし課
吉高由里子:
  真夏の方程式
```
## 1, 2のまとめ

|処理|joins(:movies)|joins(:movies)<br>.preload(:movies)|joins(:movies)<br>.eager_load(:movies)|joins(:movies)<br>.includes(:movies)|preload(:movies)|eager_load(:movies)|includes(:movies) |includes(:movies)<br>.references(:movies)|
|:---:|:---:|:---:|:---:|:---:|:---:|:---:|:---:|:---:|
|結合|内部結合|SELECT２回|内部結合|内部結合|SELECT２回|左外部結合|SELECT2回|左外部結合|
|情報|内部結合|内部結合|内部結合|内部結合|左外部結合|左外部結合|左外部結合|左外部結合|
|moviesの取得|×<br>selectを使う必要あり。|○|○|○|○|○|○|○|
|その他| |where句は使える。<br>扱いにやや注意。 |joins(:movies).<br>includes(:movies)と同じ。|joins(:movies).<br>eager_load(:movies)と同じ。|includesと同じ。<br>但し、where句は使えない。| |preloadと同じ。<br>where句が使える。|eager_loadと同じ。|
※情報のところは少し語弊があると思います。SELECT２回のものは厳密には左外部結合ではないですが、ActiveRecordで得られる情報としては同じという意味で記載しました。

## 3. actressesとmoviesの左除外結合（moviesのないactressesを抽出）
<img src="https://qiita-image-store.s3.amazonaws.com/0/121304/a49d4ead-52a3-0a75-849b-1424e2545594.png" height="120">
（再掲）

### 3-1. joinsを使う
- 左外部結合したあと`where(movies: { actress_id: nil })`で絞る。

```ruby:Rails
Actress.joins(
  "LEFT OUTER JOIN movies ON actresses.id = movies.actress_id"
).where(movies: { actress_id: nil })
```
```sql:SQL
SELECT
  actresses.*,
  movies.*
FROM
  "actresses"
  LEFT OUTER JOIN
    movies
  ON
    actresses.id = movies.actress_id
WHERE "movies"."actress_id" IS NULL
```

```ruby:execute
Actress.joins(
  "LEFT OUTER JOIN movies ON actresses.id = movies.actress_id"
).where(movies: { actress_id: nil }).select("actresses.*, movies.*").each do |actress_movie|
  puts "#{actress_movie.name}: #{actress_movie.title}"
end
# =>
  Actress Load (0.5ms)  SELECT actresses.*, movies.* FROM "actresses" LEFT OUTER JOIN movies ON actresses.id = movies.actress_id WHERE "movies"."actress_id" IS NULL
```
```text:result
新垣結衣:
悠城早矢:
```
### 3-2. eager_loadを使う
```ruby:Rails
 Actress.eager_load(:movies).where(movies: { actress_id: nil })
```
```sql:SQL
SELECT
  "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
  "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3,  "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5
FROM
  "actresses"
  LEFT OUTER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"
WHERE
  "movies"."actress_id" IS NULL
```
- 実行してみます。

```ruby:execute
Actress.eager_load(:movies).where(movies: { actress_id: nil }).each do |actress|
  puts "#{actress.name}:"
  actress.movies.each { |movie| puts "  #{movie.title}" }
end
# =>
  SQL (0.6ms)  SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" WHERE "movies"."actress_id" IS NULL
```
```text:result
新垣結衣:
悠城早矢:
```
### 3-3. includesを使う
- where句を使うことになるので、結果的にeager_loadと同じになる。
- 実行結果のみ記す。

```ruby:execute
Actress.includes(:movies).where(movies: { actress_id: nil }).each do |actress|
  puts "#{actress.name}:"
  actress.movies.each { |movie| puts "  #{movie.title}" }
end
# =>
  SQL (0.5ms)  SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" WHERE "movies"."actress_id" IS NULL
```
```text:result
新垣結衣:
悠城早矢:
```
### 3-4. missingを使う（Rails6.1より）[^1]

[^1]: https://blog.saeloun.com/2020/01/21/rails-6-1-adds-query-method-missing-to-find-orphan-records.html


## 4. ActressとMovieとTagの内部結合  
### 4-1. joinsを使う
- 内部結合は難しくない。
- selectの有無などは、[1. actressesとmoviesの内部結合](#1-actressesとmoviesの内部結合)を参照。

```ruby:Rails
Actress.joins(movies: :tags)
```

```sql:SQL
SELECT
  "actresses".*
FROM
  "actresses"
  INNER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"
  INNER JOIN
    "tags"
  ON
    "tags"."movie_id" = "movies"."id"
```
- selectをつけて実行してみます。

```ruby:execute
Actress.joins(movies: :tags).select("actresses.*, movies.*, tags.*").each do |actress_movie_tag|
  puts "#{actress_movie_tag.name} : #{actress_movie_tag.title} : #{actress_movie_tag.key}"
end
# =>
  Actress Load (0.6ms)  SELECT actresses.*, movies.*, tags.* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" INNER JOIN "tags" ON "tags"."movie_id" = "movies"."id"
```
```text:result
佐津川愛美 : 蝉しぐれ : 時代劇
佐津川愛美 : 蝉しぐれ : 子役
堀北真希 : ALWAYS 三丁目の夕日 : 昭和
佐津川愛美 : 貞子vs伽椰子 : ホラー
吉高由里子 : 真夏の方程式 : ミステリー
吉高由里子 : 真夏の方程式 : 夏
堀北真希 : 県庁おもてなし課 : 公務員
堀北真希 : 県庁おもてなし課 : 地方活性
佐津川愛美 : 蝉しぐれ : 夏
```

## 5. ActressとMovieとTagの左外部結合
### 5-1. joinsを使う
```ruby:Rails
Actress.joins(
  "LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
   LEFT OUTER JOIN tags ON movies.id = tags.movie_id"
)
```
```sql:SQL
SELECT
  "actresses".*
FROM
  "actresses"
  LEFT OUTER JOIN
    movies
  ON
    actresses.id = movies.actress_id
  LEFT OUTER JOIN
    tags
  ON
    movies.id = tags.movie_id
```
- selectをつけて実行してみます。

```ruby:execute
Actress.joins(
  "LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
   LEFT OUTER JOIN tags ON movies.id = tags.movie_id"
).select("actresses.*, movies.*, tags.key AS key").each do |act_mov_tag|
  puts "#{act_mov_tag.name} : #{act_mov_tag.title} : #{act_mov_tag.key}"
end
# =>
  Actress Load (0.7ms)  SELECT actresses.*, movies.*, tags.key AS key FROM "actresses" LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
   LEFT OUTER JOIN tags ON movies.id = tags.movie_id
```
```text:result
佐津川愛美 : 蝉しぐれ : 時代劇
佐津川愛美 : 蝉しぐれ : 子役
堀北真希 : ALWAYS 三丁目の夕日 : 昭和
佐津川愛美 : 貞子vs伽椰子 : ホラー
吉高由里子 : 真夏の方程式 : ミステリー
吉高由里子 : 真夏の方程式 : 夏
堀北真希 : 県庁おもてなし課 : 公務員
堀北真希 : 県庁おもてなし課 : 地方活性
佐津川愛美 : 蝉しぐれ : 夏
多部未華子 : 夜のピクニック :
佐津川愛美 : 忍道-SHINOBIDO- :
悠城早矢 :  :
新垣結衣 :  :
```
### 5-2. eager_loadを使う
```ruby:Rails
Actress.eager_load(movies: :tags)
```
```sql:SQL
SELECT
  "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1,  "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
  "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5,
  "tags"."id" AS t2_r0, "tags"."movie_id" AS t2_r1, "tags"."key" AS t2_r2, "tags"."created_at" AS t2_r3, "tags"."updated_at" AS t2_r4
FROM
  "actresses"
  LEFT OUTER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"
  LEFT OUTER JOIN
    "tags"
  ON
    "tags"."movie_id" = "movies"."id"
```
- 多少見やすいようにprintして、実行してみます。

```ruby:execute
Actress.eager_load(movies: :tags).each do |actress|
  puts "#{actress.name}:"
  actress.movies.each do |movie|
    print "  #{movie.title}: "
    movie.tags.each { |tag| print "#{tag.key} " }
    puts
  end
end
# =>
  SQL (1.0ms)  SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5, "tags"."id" AS t2_r0, "tags"."movie_id" AS t2_r1, "tags"."key" AS t2_r2, "tags"."created_at" AS t2_r3, "tags"."updated_at" AS t2_r4 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" LEFT OUTER JOIN "tags" ON "tags"."movie_id" = "movies"."id"
```
```text:result
佐津川愛美:
  蝉しぐれ: 時代劇 子役 夏
  貞子vs伽椰子: ホラー
  忍道-SHINOBIDO-:
堀北真希:
  ALWAYS 三丁目の夕日: 昭和
  県庁おもてなし課: 公務員 地方活性
吉高由里子:
  真夏の方程式: ミステリー 夏
多部未華子:
  夜のピクニック:
悠城早矢:
新垣結衣:
```
### 5-3. includes（referencesなし）を使う
- 3回クエリが走る。
- 実行結果のみ記す。

```ruby:execute
Actress.includes(movies: :tags).each do |actress|
  puts "#{actress.name}:"
  actress.movies.each do |movie|
    print "  #{movie.title}: "
    movie.tags.each { |tag| print "#{tag.key} " }
    puts
  end
end
# =>
  Actress Load (0.4ms)  SELECT "actresses".* FROM "actresses"
  Movie Load (0.7ms)  SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (1, 2, 3, 4, 5, 6)
  Tag Load (0.4ms)  SELECT "tags".* FROM "tags" WHERE "tags"."movie_id" IN (1, 2, 3, 4, 5, 6, 7)
```
```text:result
5-2に同じ。
```

### 5-4. includes（referencesあり）を使う
- 結合先がmoviesとtagsの２種類あるのでreferencesの引数は３パターン考えられるがどれもSQLクエリは同じだった。

```ruby:moviesとtags
Actress.includes(movies: :tags).references(:movies, :tags)
```
```ruby:moviesのみ
Actress.includes(movies: :tags).references(:movies)
```
```ruby:tagsのみ
Actress.includes(movies: :tags).references(:tags)
```

```sql:SQL
SELECT
  "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
  "movies"."id" AS t1_r0,  "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5, 
  "tags"."id" AS t2_r0, "tags"."movie_id" AS t2_r1, "tags"."key" AS t2_r2, "tags"."created_at" AS t2_r3, "tags"."updated_at" AS t2_r4 
FROM
  "actresses"
  LEFT OUTER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"
  LEFT OUTER JOIN
    "tags"
  ON
    "tags"."movie_id" = "movies"."id"
```
```text:result
5-2、5-3に同じ。
```

## 6. actressesとmoviesとtagsの左部分除外結合（tagsのないactressesを抽出する）
<img src="https://qiita-image-store.s3.amazonaws.com/0/121304/28ad63e0-3353-cb6d-a7d5-91a53f25505d.png" height="120">
（再掲）
### 6-1. joinsを使う
- [3-1. joinsを使う](#3-1-joinsを使う)の応用。

```ruby:Rails
Actress.joins(
  "LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
   LEFT OUTER JOIN tags ON movies.id = tags.movie_id"
).where(movies: { tags: { movie_id: nil } })
```
```sql:SQL
SELECT
  "actresses".*
FROM
  "actresses"
  LEFT OUTER JOIN
    movies
  ON
    actresses.id = movies.actress_id
  LEFT OUTER JOIN
    tags
  ON
    movies.id = tags.movie_id
WHERE "tags"."movie_id" IS NULL
```
- selectをつけて実行してみます。

```ruby:execute
Actress.joins(
  "LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
   LEFT OUTER JOIN tags ON movies.id = tags.movie_id"
).where(movies: { tags: { movie_id: nil } }).select("actresses.*, movies.*, tags.*").each do |amt|
  puts "#{amt.name} : #{amt.title} : #{amt.key}"
end
# =>
  Actress Load (0.6ms)  SELECT actresses.*, movies.*, tags.* FROM "actresses" LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
   LEFT OUTER JOIN tags ON movies.id = tags.movie_id WHERE "tags"."movie_id" IS NULL
```
```text:result
多部未華子 : 夜のピクニック :
佐津川愛美 : 忍道-SHINOBIDO- :
悠城早矢 :  :
新垣結衣 :  :
```

### 6-2. eager_loadを使う 
- [3-2. eager_loadを使う](#3-2-eager_loadを使う)の応用。

```ruby:Rails
Actress.eager_load(movies: :tags).where(tags: { movie_id: nil })
```
```sql:SQL
SELECT
  "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
  "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5, 
  "tags"."id" AS t2_r0, "tags"."movie_id" AS t2_r1, "tags"."key" AS t2_r2, "tags"."created_at" AS t2_r3, "tags"."updated_at" AS t2_r4 
FROM
  "actresses"
  LEFT OUTER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"
  LEFT OUTER JOIN
    "tags"
  ON
    "tags"."movie_id" = "movies"."id"
WHERE "tags"."movie_id" IS NULL
```
- 実行してみます。

```ruby:execute
Actress.eager_load(movies: :tags).where(tags: { movie_id: nil }).each do |actress|
  puts "#{actress.name}:"
  actress.movies.each do |movie|
    print "  #{movie.title}:"
    movie.tags.each { |tag| print "#{tag.key}" }
    puts
  end
end
# =>
  SQL (0.6ms)  SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5, "tags"."id" AS t2_r0, "tags"."movie_id" AS t2_r1, "tags"."key" AS t2_r2, "tags"."created_at" AS t2_r3, "tags"."updated_at" AS t2_r4 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" LEFT OUTER JOIN "tags" ON "tags"."movie_id" = "movies"."id" WHERE "tags"."movie_id" IS NULL
```
```text:result
多部未華子:
  夜のピクニック:
佐津川愛美:
  忍道-SHINOBIDO-:
悠城早矢:
新垣結衣:
```
### 6-3. includesを使う 
- [3-3. includesを使う](#3-3-includesを使う)の応用。
- 実行結果のみ記す。

```ruby:execute
Actress.includes(movies: :tags).where(tags: { movie_id: nil }).each do |actress|
  puts "#{actress.name}:"
  actress.movies.each do |movie|
    print "  #{movie.title}:"
    movie.tags.each { |tag| print "#{tag.key}" }
    puts
  end
end
# =>
  SQL (0.9ms)  SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5, "tags"."id" AS t2_r0, "tags"."movie_id" AS t2_r1, "tags"."key" AS t2_r2, "tags"."created_at" AS t2_r3, "tags"."updated_at" AS t2_r4 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" LEFT OUTER JOIN "tags" ON "tags"."movie_id" = "movies"."id" WHERE "tags"."movie_id" IS NULL
```
```text:result
6-2と同じ。
```
## 最後に
- Railsの経験の浅い若輩者ですので、間違い等ございましたら、優しく教えて下さい。
- 本記事を書くにあたり、幾つかの過去の記事を参考にさせていただきました。[参考](#参考)をご覧ください。とても勉強になりました。ありがとうございました。
- 今回は、1988年生まれの女優さんを題材にさせていただきました。ここに感謝の意を表します。ありがとうございました。

## 参考
1. [Visual Representation of SQL Joins](http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins)
2. [ActiveRecordのjoinsとpreloadとincludesとeager_loadの違い](http://qiita.com/k0kubun/items/80c5a5494f53bb88dc58)
3. [has_manyをeager_load/includesするときの注意点](http://qiita.com/metheglin/items/c6d3b04e1305f7ec5d96)
4. [preloadとeager_loadで1000000億倍早くなったはなし](http://texta.pixta.jp/entry/2016/02/10/180244)
5. [Rails と テーブル結合](http://qiita.com/merrill/items/de6eb7e899753ad3361a)
6. [INNER JOIN で eager loading](http://qiita.com/akishin/items/36dba4b2ccdaefb53dc6)
7. [佐津川愛美 | オフィシャルサイト](http://www.horipro.co.jp/satsukawaaimi/)
8. [堀北真希｜Sweet Power](http://sweetpower.jp/sweetpower/maki/)
9. [Mikako Tabe official web site](http://www.tabemikako.com/)
10. [吉高 由里子 - オフィシャル ウェブサイト](http://artist.amuse.co.jp/artist/yoshitaka_yuriko/)