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.