Rails :include vs. :joins

It appears that the :include functionality was changed with Rails 2.1. Rails used to do the join in all cases, but for performance reasons it was changed to use multiple queries in some circumstances. This blog post by Fabio Akita has some good information on the change (see the section entitled "Optimized Eager Loading").

---

.joins will just joins the tables and brings selected fields in return. if you call associations on joins query result, it will fire database queries again

:includes will eager load the included associations and add them in memory. :includes loads all the included tables attributes. If you call associations on include query result, it will not fire any queries

---

The difference between joins and include is that using the include statement generates a much larger SQL query loading into memory all the attributes from the other table(s).

For example, if you have a table full of comments and you use a :joins => users to pull in all the user information for sorting purposes, etc it will work fine and take less time than :include, but say you want to display the comment along with the users name, email, etc. To get the information using :joins, it will have to make separate SQL queries for each user it fetches, whereas if you used :include this information is ready for use.

Great example:

http://railscasts.com/episodes/181-include-vs-joins

---

I was recently reading more on difference between :joins and :includes in rails. Here is an explaination of what I understood (with examples :))

Consider this scenario:

A User has_many comments and a comment belongs_to a User.

The User model has the following attributes: Name(string), Age(integer). The Comment model has the following attributes:Content, user_id. For a comment a user_id can be null.

Joins:

:joins performs a inner join between two tables. Thus

Comment.joins(:user)

#=> <ActiveRecord::Relation [#<Comment id: 1, content: "Hi I am Aaditi.This is my first   comment!", user_id: 1, created_at: "2014-11-12 18:29:24", updated_at: "2014-11-12 18:29:24">, 
     #<Comment id: 2, content: "Hi I am Ankita.This is my first comment!", user_id: 2, created_at: "2014-11-12 18:29:29", updated_at: "2014-11-12 18:29:29">,    
     #<Comment id: 3, content: "Hi I am John.This is my first comment!", user_id: 3, created_at: "2014-11-12 18:30:25", updated_at: "2014-11-12 18:30:25">]>

will fetch all records where user_id (of comments table) is equal to user.id (users table). Thus if you do

Comment.joins(:user).where("comments.user_id is null")

#=> <ActiveRecord::Relation []>

You will get a empty array as shown.

Moreover joins does not load the joined table in memory. Thus if you do

comment_1 = Comment.joins(:user).first

comment_1.user.age
#=>←[1m←[36mUser Load (0.0ms)←[0m  ←[1mSELECT "users".* FROM "users" WHERE "users"."id" = ? ORDER BY "users"."id" ASC LIMIT 1←[0m  [["id", 1]]
#=> 24

As you see, comment_1.user.age will fire a database query again in the background to get the results

Includes:

:includes performs a left outer join between the two tables. Thus

Comment.includes(:user)

#=><ActiveRecord::Relation [#<Comment id: 1, content: "Hi I am Aaditi.This is my first comment!", user_id: 1, created_at: "2014-11-12 18:29:24", updated_at: "2014-11-12 18:29:24">,
   #<Comment id: 2, content: "Hi I am Ankita.This is my first comment!", user_id: 2, created_at: "2014-11-12 18:29:29", updated_at: "2014-11-12 18:29:29">,
   #<Comment id: 3, content: "Hi I am John.This is my first comment!", user_id: 3, created_at: "2014-11-12 18:30:25", updated_at: "2014-11-12 18:30:25">,    
   #<Comment id: 4, content: "Hi This is an anonymous comment!", user_id: nil, created_at: "2014-11-12 18:31:02", updated_at: "2014-11-12 18:31:02">]>

will result in a joined table with all the records from comments table. Thus if you do

Comment.includes(:user).where("comment.user_id is null")
#=> #<ActiveRecord::Relation [#<Comment id: 4, content: "Hi This is an anonymous comment!", user_id: nil, created_at: "2014-11-12 18:31:02", updated_at: "2014-11-12 18:31:02">]>

it will fetch records where comments.user_id is nil as shown.

Moreover includes loads both the tables in the memory. Thus if you do

comment_1 = Comment.includes(:user).first

comment_1.user.age
#=> 24

As you can notice comment_1.user.age simply loads the result from memory without firing a database query in the background.

---

In addition to a performance considerations, there's a functional difference too. When you join comments, you are asking for posts that have comments- an inner join by default. When you include comments, you are asking for all posts- an outer join.

---

tl;dr

I contrast them in two ways:

joins - For conditional selection of records.

includes - When using an association on each member of a result set.

Longer version

Joins is meant to filter the result set coming from the database. You use it to do set operations on your table. Think of this as a where clause that performs set theory.

Post.joins(:comments)

is the same as

Post.where('id in (select post_id from comments)')

Except that if there are more than one comment you will get duplicate posts back with the joins. But every post will be a post that has comments. You can correct this with distinct:

Post.joins(:comments).count
=> 10
Post.joins(:comments).distinct.count
=> 2

In contract, the includes method will simply make sure that there are no additional database queries when referencing the relation (so that we don't make n + 1 queries)

Post.includes(:comments).count
=> 4 # includes posts without comments so the count might be higher.

The moral is, use joins when you want to do conditional set operations and use includes when you are going to be using a relation on each member of a collection.

---

.joins works as database join and it joins two or more table and fetch selected data from backend(database).

.includes work as left join of database. It loaded all the records of left side, does not have relevance of right hand side model. It is used to eager loading because it load all associated object in memory. If we call associations on include query result then it does not fire a query on database, It simply return data from memory because it have already loaded data in memory.

---

'joins' just used to join tables and when you called associations on joins then it will again fire query (it mean many query will fire)

lets suppose you have tow model, User and Organisation
User has_many organisations
suppose you have 10 organisation for a user 
@records= User.joins(:organisations).where("organisations.user_id = 1")
QUERY will be 
 select * from users INNER JOIN organisations ON organisations.user_id = users.id where organisations.user_id = 1

it will return all records of organisation related to user
and @records.map{|u|u.organisation.name}
it run QUERY like 
select * from organisations where organisations.id = x then time(hwo many organisation you have)

total number of SQL is 11 in this case

But with 'includes' will eager load the included associations and add them in memory(load all associations on first load) and not fire query again

when you get records with includes like @records= User.includes(:organisations).where("organisations.user_id = 1") then query will be

select * from users INNER JOIN organisations ON organisations.user_id = users.id where organisations.user_id = 1
and 


 select * from organisations where organisations.id IN(IDS of organisation(1, to 10)) if 10 organisation
and when you run this 

@records.map{|u|u.organisation.name} no query will fire

---