Includes and joins in active record queries rails.

includes – Uses eager loading and optimally used when accessing each member attributes from the associated records.
joins – Uses lazy loading and optimally used when filtering results on condition with associated records.

includes – Performs LEFT OUTER JOIN
joins – Performs INNER JOIN

Explanation with Example:

Say, we have Job fairs happening next week which are having many registrants for their respective job fairs. Now, the Job fair admin needs to view all the registrants with associated Job Fair. Here’s where includes and joins comes to picture. Let’s see the difference.

Below is the association of Job fair and Registrant.

class JobFair < ActiveRecord::Base
  has_many :registrants
end

class Registrant < ActiveRecord::Base
  belongs_to :job_fair
end

Joins

The joins method lazy loads by only loading the Registrant table into memory as the associated job_fair table is not required. Therefore we are not loading redundant data into memory needlessly.

registrants_with_joins = Registrant.joins(:job_fair)

The above code results in below query:

SELECT "registrants".* FROM "registrants" INNER JOIN "job_fairs" ON "job_fairs"."id" = "registrants"."job_fair_id"

registrants_with_joins.each do |registrant|
  registrant.job_fair.name
end

Kills performance by n + 1 queries. The above code needs to fetch name of job fair which results in n + 1 queries, where n is length of registrants_with_joins.

JobFair Load (0.5ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 1]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 1]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 1]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 1]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 1]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 1]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 2]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 2]]
JobFair Load (0.5ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 2]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 2]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 2]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 3]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 3]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 3]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 3]]
JobFair Load (0.3ms)  SELECT  "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" = $1 LIMIT 1  [["id", 3]]

Includes

Avoid n + 1 queries

With includes, Active Record ensures that both tables are loaded into memory which in turn reduce the amount of database queries required to retrieve any associated data. When querying a table for data with an associated table, all of the specified associations are loaded using the minimum possible number of queries.

registrants_with_includes.each do |registrant|
  registrant.job_fair.name
end

It has already eagerly loaded the associated job_fair table, so this block only required a single query!. Awesome right..? Below query results from the above code.

SELECT "job_fairs".* FROM "job_fairs"  WHERE "job_fairs"."id" IN (1, 2, 3)

Conclusion:

Use Joins, when filtering results on some condition, don't use joins when we need to access each member attributes from the associated records which will result in n + 1 queries.

Use includes, when we need to access each member attributes from the associated records and don't use it when we need to filter results with condition which would result in high memory usage.