# Joins vs include? **URL:** \ **Category:** Rails\ **Created:** [September 22, 2008, 8:43pm UTC](https://www.ruby-forum.com/t/joins-vs-include/149244 "2008-09-22T20:43:55Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [September 22, 2008, 8:43pm UTC](https://www.ruby-forum.com/t/joins-vs-include/149244/1 "2008-09-22T20:43:55Z")
I know this is probably a noob question, but I needed a little clarification. I did the following in the console of my app: > > User.find(:all, :conditions =\> “orders.id \> 1”, :joins =\> :orders).size > > =\> 1254 > > User.find(:all, :conditions =\> “orders.id \> 1”, :include =\> :orders).size > > =\> 934 A user has many orders. Why am I getting different results? When using fields in a has\_many relationship should I ALWAYS use the :include option. I did the reverse too: > > Order.find(:all, :conditions =\> “users.id \< 100”, :joins =\> :user).size > > =\> 172 > > Order.find(:all, :conditions =\> “users.id \< 100”, :include =\> :user).size > > =\> 172 I’m getting the same result. What is the general rule of thumb on when to use joins or includes? Thanks for your help! ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [September 22, 2008, 10:00pm UTC](https://www.ruby-forum.com/t/joins-vs-include/149244/2 "2008-09-22T22:00:45Z")
How did you populate your tables? I am sure it is an issue about LEFT JOIN, i.e: all records from left table will appear in the result even if they don’t have matches on the right table. As an order always has a user, :joins and :include return the same number of rows. Whereas, a user doesn’t necessarily have an order, but he will still appear in the result with a left join. ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [September 22, 2008, 10:06pm UTC](https://www.ruby-forum.com/t/joins-vs-include/149244/3 "2008-09-22T22:06:13Z")
On 22 Sep 2008, at 19:43, Ben J. wrote: > =\> 934 > > > > =\> :user).size > > > =\> 172 > > I’m getting the same result. What is the general rule of thumb on when > to use joins or includes? See > **[The difference between :include and :joins - Space Vatican](https://www.spacevatican.org/2008/6/22/the-difference-between-include-and-joins/)** > > I think some people occasionally mix up :include and :joins (or possibly don’t know of the existance of :joins). :include is for loading … . The difference between your two cases is that a user can have many orders: for each user you’ll get a result for each object, whereas an order can only ever have one user. Fred ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [September 22, 2008, 10:16pm UTC](https://www.ruby-forum.com/t/joins-vs-include/149244/4 "2008-09-22T22:16:56Z")
Thanks for your help. I am still somewhat confused though. So is the general rule of thumb to use joins on belongs to relationships and include on has\_many. It looks as if joins returns flawed results is used on a has\_many relationship. Is this correct? I would prefer to avoid include because it is so much slower, but joins doesnt seem to return the correct records. Thanks. ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [September 22, 2008, 10:29pm UTC](https://www.ruby-forum.com/t/joins-vs-include/149244/5 "2008-09-22T22:29:10Z")
On Sep 22, 9:16 pm, Ben J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Thanks for your help. I am still somewhat confused though. > > So is the general rule of thumb to use joins on belongs to relationships > and include on has\_many. It looks as if joins returns flawed results is > used on a has\_many relationship. Is this correct? No. Use :joins when you want a join. Use include when you want to avoid the n+1 problem (ie you want rails to load and populate the associations). Fred ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [September 22, 2008, 10:40pm UTC](https://www.ruby-forum.com/t/joins-vs-include/149244/6 "2008-09-22T22:40:04Z")
It also seems that the following works just fine: User.find(:all, :conditions =\> “orders.id \> 1”, :joins =\> :orders, :group =\> “users.id”).size =\> 934 I apologize for my ignorance in this situation, but is the above search “improper”? Is there a better way to do this? Thanks. ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [September 22, 2008, 10:35pm UTC](https://www.ruby-forum.com/t/joins-vs-include/149244/7 "2008-09-22T22:35:21Z")
Frederick C. wrote: > On Sep 22, 9:16�pm, Ben J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) > wrote: > > > Thanks for your help. I am still somewhat confused though. > > > > So is the general rule of thumb to use joins on belongs to relationships > > and include on has\_many. It looks as if joins returns flawed results is > > used on a has\_many relationship. Is this correct? > > No. Use :joins when you want a join. Use include when you want to > avoid the n+1 problem (ie you want rails to load and populate the > associations). > > Fred Thanks for your help. I agree, I would prefer to use joins, but they do not return the proper results. It seems that joins return duplicate results: > > User.find(:all, :conditions =\> “orders.id \> 1”, :joins =\> :orders).size > > =\> 1254 > > User.find(:all, :conditions =\> “orders.id \> 1”, :include =\> :orders).size > > =\> 934 In the above example the first query is returning duplicate results. I am getting users with the same ID. I’m confused why it would return duplicate results. Any idea why this is? Seeing as this is a problem it seems that :include is my only option. Thanks. ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [September 22, 2008, 10:53pm UTC](https://www.ruby-forum.com/t/joins-vs-include/149244/8 "2008-09-22T22:53:48Z")
On Sep 22, 9:35 pm, Ben J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Frederick C. wrote: > > > On Sep 22, 9:16 pm, Ben J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) > > wrote: > =\> 934 > > In the above example the first query is returning duplicate results. I > am getting users with the same ID. I’m confused why it would return > duplicate results. Any idea why this is? Seeing as this is a problem it > seems that :include is my only option. If your users table had exactly one user, and that user had 2 orders, then select \* from users inner join orders on orders.user\_id = users.id would return the result set (i’ve made up some column names) users.id | users.name | orders.id | orders.name| 1 | bob | 1 | a tv 1 | bob | 2 | a dvd This is just how joins are supposed to work. I’m still not sure what you’re trying to achieve in the first place (but grouping or using select distinct may be options) :include is different (and doesn’t even use joins half the time). It’s doing a lot of work behind the scenes wiring up associations. Fred