More than 3 years have passed since last update.

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

Last updated at 2020-10-27Posted at 2016-09-03

0. はじめに

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

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	説明
	女優テーブルがある。女優テーブルは映画テーブルを持つ。映画テーブルは、映画に関連するキーワードを入れたタグテーブルを持つ。

0-3. モデル

Actress

class Actress < ApplicationRecord
  has_many :movies
end

Movie

class Movie < ApplicationRecord
  has_many :tags
  belongs_to :actress
end

Tag

class Tag < ApplicationRecord
  belongs_to :movie
end

0-4. 今回扱うデータ

actress

SELECT id, name FROM actresses;
 id |    name
----+------------
  1 | 多部未華子
  2 | 佐津川愛美
  3 | 新垣結衣
  4 | 堀北真希
  5 | 吉高由里子
  6 | 悠城早矢

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

0-5. 補足

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

actressesとmoviesの左部分除外結合（moviesのないactressesを抽出する）	actressesとmoviesとtagsの左部分除外結合（tagsのないactressesを抽出する）

※今回のデータの場合、moviesは必ずactress_idをもつし、tagsは必ずmovie_idを持つので、上記のベン図は厳密には違いますが、より一般性を持たせた図にしました。

1. actressesとmoviesの内部結合

1-1. joins（selectなし）を使う

actressesのカラムしか持ってきてくれない。
結合先の情報が不要な場合はこれで良い。

Rails

Actress.joins(:movies)

SQL

SELECT
  "actresses".*
FROM
  "actresses"
  INNER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"

1-2. joins（selectあり）を使う

結合先（この場合はmovies）のカラムも取得できる。

Rails

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

SQL

SELECT
  actresses.*,
  movies.*
FROM
  "actresses"
  INNER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"
ORDER BY "actresses"."id" ASC
LIMIT 1

実行してみます。

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"

result

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

カラム名がかぶる場合は、select内で名前付けをする。

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を使うことができる。

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。

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

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を使うと良い。

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

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-6. joins + includesを使うを使うのが無難そう。
- preloadに関しては、GROUP BYしたときの挙動もeager_loadと異なるので、ActiveRecordにおけるGROUP BYの使い方もご覧ください。

Rails

Actress.joins(:movies).preload(:movies)

2-3.　preloadを使うと違い、IN句で絞り込まれているのがわかる。

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で削除予定。）

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)

result

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

distinctをつけないと・・・。

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)

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が使える。

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)

ただし、結合しているわけではないので使い方に注意が必要。
- 例えばこんな感じに使うと・・・。

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の条件を絞ったのに出てきてしまう。（当たり前だけど）

result

堀北真希
  ALWAYS 三丁目の夕日
  県庁おもてなし課
吉高由里子
  真夏の方程式

1-5. joins + eager_loadを使う

2-2. eager_loadを使うで後述するように単にeager_loadを使うと外部結合になるが、joinsと組み合わせると内部結合になる。
結合先も取得してくれ、actressごとにmoviesを取得できる。
1-6. joins + includesを使うと同じ挙動

Rails

Actress.joins(:movies).eager_load(:movies)

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"

実行してみます。

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"

result

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

ただし、eachブロック内でcountなどの集合関数を使うと、旨味が減る。

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を使うと良い。

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を使うと違いdistinctしなくて良い。
1-5. joins + eager_loadを使うと同じ挙動。

Rails

Actress.joins(:movies).includes(:movies)

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"

実行してみます。

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"

result

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

where句やmergeによる結合先のscopeが使えます。
- 以下の３つは同じ挙動。

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は不要。

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が必要。

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に同じ。
順番も変わらないので、左外部結合として一般に想像できる結果が返ってくる。

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

result

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

2-2. eager_loadを使う

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

Rails

Actress.eager_load(:movies)

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を取得できる。

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"

result

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

firstとかを使うと旨味が出ない（こういう使い方を想定していないのか）
- ２回クエリが発行されてしまう。

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なし）と同じ挙動。

Rails

Actress.preload(:movies)

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)

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)

result

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

preloadした先でwhere句は使えないので、2-5. includes（referencesあり）に記述した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を使うと同じ挙動

Rails

Actress.includes(:movies)

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)

実行してみます。

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)

result

２−３と同じ。

2-5. includes（referencesあり）を使う

includesした先も持ってきてくれる。
結果、SQLクエリは１回で済む。
2-2. eager_loadと同じ挙動。

Rails

Actress.includes(:movies).references(:movies)

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"

実行してみる

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"

result

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

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

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

result

堀北真希:
  県庁おもてなし課
吉高由里子:
  真夏の方程式

1, 2のまとめ

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

3. actressesとmoviesの左除外結合（moviesのないactressesを抽出）

（再掲）

3-1. joinsを使う

左外部結合したあとwhere(movies: { actress_id: nil })で絞る。

Rails

Actress.joins(
  "LEFT OUTER JOIN movies ON actresses.id = movies.actress_id"
).where(movies: { actress_id: nil })

SQL

SELECT
  actresses.*,
  movies.*
FROM
  "actresses"
  LEFT OUTER JOIN
    movies
  ON
    actresses.id = movies.actress_id
WHERE "movies"."actress_id" IS NULL

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

result

新垣結衣:
悠城早矢:

3-2. eager_loadを使う

Rails

 Actress.eager_load(:movies).where(movies: { actress_id: nil })

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

実行してみます。

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

result

新垣結衣:
悠城早矢:

3-3. includesを使う

where句を使うことになるので、結果的にeager_loadと同じになる。
実行結果のみ記す。

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

result

新垣結衣:
悠城早矢:

3-4. missingを使う（Rails6.1より）¹

4. ActressとMovieとTagの内部結合

4-1. joinsを使う

内部結合は難しくない。
selectの有無などは、1. actressesとmoviesの内部結合を参照。

Rails

Actress.joins(movies: :tags)

SQL

SELECT
  "actresses".*
FROM
  "actresses"
  INNER JOIN
    "movies"
  ON
    "movies"."actress_id" = "actresses"."id"
  INNER JOIN
    "tags"
  ON
    "tags"."movie_id" = "movies"."id"

selectをつけて実行してみます。

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"

result

佐津川愛美 : 蝉しぐれ : 時代劇
佐津川愛美 : 蝉しぐれ : 子役
堀北真希 : ALWAYS 三丁目の夕日 : 昭和
佐津川愛美 : 貞子vs伽椰子 : ホラー
吉高由里子 : 真夏の方程式 : ミステリー
吉高由里子 : 真夏の方程式 : 夏
堀北真希 : 県庁おもてなし課 : 公務員
堀北真希 : 県庁おもてなし課 : 地方活性
佐津川愛美 : 蝉しぐれ : 夏

5. ActressとMovieとTagの左外部結合

5-1. joinsを使う

Rails

Actress.joins(
  "LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
   LEFT OUTER JOIN tags ON movies.id = tags.movie_id"
)

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をつけて実行してみます。

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

result

佐津川愛美 : 蝉しぐれ : 時代劇
佐津川愛美 : 蝉しぐれ : 子役
堀北真希 : ALWAYS 三丁目の夕日 : 昭和
佐津川愛美 : 貞子vs伽椰子 : ホラー
吉高由里子 : 真夏の方程式 : ミステリー
吉高由里子 : 真夏の方程式 : 夏
堀北真希 : 県庁おもてなし課 : 公務員
堀北真希 : 県庁おもてなし課 : 地方活性
佐津川愛美 : 蝉しぐれ : 夏
多部未華子 : 夜のピクニック :
佐津川愛美 : 忍道-SHINOBIDO- :
悠城早矢 :  :
新垣結衣 :  :

5-2. eager_loadを使う

Rails

Actress.eager_load(movies: :tags)

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して、実行してみます。

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"

result

佐津川愛美:
  蝉しぐれ: 時代劇 子役 夏
  貞子vs伽椰子: ホラー
  忍道-SHINOBIDO-:
堀北真希:
  ALWAYS 三丁目の夕日: 昭和
  県庁おもてなし課: 公務員 地方活性
吉高由里子:
  真夏の方程式: ミステリー 夏
多部未華子:
  夜のピクニック:
悠城早矢:
新垣結衣:

5-3. includes（referencesなし）を使う

3回クエリが走る。
実行結果のみ記す。

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)

result

5-2に同じ。

5-4. includes（referencesあり）を使う

結合先がmoviesとtagsの２種類あるのでreferencesの引数は３パターン考えられるがどれもSQLクエリは同じだった。

moviesとtags

Actress.includes(movies: :tags).references(:movies, :tags)

moviesのみ

Actress.includes(movies: :tags).references(:movies)

tagsのみ

Actress.includes(movies: :tags).references(:tags)

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"

result

5-2、5-3に同じ。

6. actressesとmoviesとtagsの左部分除外結合（tagsのないactressesを抽出する）

（再掲） ### 6-1. joinsを使う - [3-1. joinsを使う](#3-1-joinsを使う)の応用。

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

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をつけて実行してみます。

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

result

多部未華子 : 夜のピクニック :
佐津川愛美 : 忍道-SHINOBIDO- :
悠城早矢 :  :
新垣結衣 :  :

6-2. eager_loadを使う

3-2. eager_loadを使うの応用。

Rails

Actress.eager_load(movies: :tags).where(tags: { movie_id: nil })

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

実行してみます。

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

result

多部未華子:
  夜のピクニック:
佐津川愛美:
  忍道-SHINOBIDO-:
悠城早矢:
新垣結衣:

6-3. includesを使う

3-3. includesを使うの応用。
実行結果のみ記す。

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

result

6-2と同じ。

最後に

Railsの経験の浅い若輩者ですので、間違い等ございましたら、優しく教えて下さい。
本記事を書くにあたり、幾つかの過去の記事を参考にさせていただきました。参考をご覧ください。とても勉強になりました。ありがとうございました。
今回は、1988年生まれの女優さんを題材にさせていただきました。ここに感謝の意を表します。ありがとうございました。

参考

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

989

814

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

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

0. はじめに

0-1. RubyとRailsとPostgreSQLのバージョン

0-2. 今回扱うERD

0-3. モデル

0-4. 今回扱うデータ

0-5. 補足

1. actressesとmoviesの内部結合

1-1. joins（selectなし）を使う

1-2. joins（selectあり）を使う

1-3. joinsに関する補足

1-4. joins + preloadを使う

1-5. joins + eager_loadを使う

1-6. joins + includesを使う

2. actressesとmoviesの左外部結合

2-1. joinsを使う

2-2. eager_loadを使う

2-3. preloadを使う

2-4. includes（referencesなし）を使う

2-5. includes（referencesあり）を使う

1, 2のまとめ

3. actressesとmoviesの左除外結合（moviesのないactressesを抽出）

3-1. joinsを使う

3-2. eager_loadを使う

3-3. includesを使う

3-4. missingを使う（Rails6.1より）1

4. ActressとMovieとTagの内部結合

4-1. joinsを使う

5. ActressとMovieとTagの左外部結合

5-1. joinsを使う

5-2. eager_loadを使う

5-3. includes（referencesなし）を使う

5-4. includes（referencesあり）を使う

6. actressesとmoviesとtagsの左部分除外結合（tagsのないactressesを抽出する）

6-2. eager_loadを使う

6-3. includesを使う

最後に

参考

3-4. missingを使う（Rails6.1より）¹