# Difference between include and joins in find?

**URL:** <https://www.ruby-forum.com/t/difference-between-include-and-joins-in-find/153508>\
**Category:** Rails\
**Created:** [November 18, 2008, 5:52am UTC](https://www.ruby-forum.com/t/difference-between-include-and-joins-in-find/153508 "2008-11-18T05:52:59Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [November 18, 2008, 5:52am UTC](https://www.ruby-forum.com/t/difference-between-include-and-joins-in-find/153508/1 "2008-11-18T05:52:59Z")

</div>

I had real weird problem here.

If I use “joins” in find, both development and production environment  
give right answers.  
But, when I use “include” in find, the development environment goes  
all right. However, the find method fails in production enviroment.

Let me describe this in detail.

I have two tables.

###### table 1: companies

id int  
…

###### table 2: sections

id int  
ref\_company\_id int  
ref\_meta\_id int  
…

A company will have one section, and a section may have sub-sections.  
when ref\_meta\_id is 0, the section is the main section of a company  
whose id is ref\_company\_id.  
when ref\_meta\_id is not 0, the section is a sub-section of a company  
whose id is ref\_company\_id.

And here are the two models

# class Company \< ActiveRecord

# has\_one :main-

section, :class=\>“Section”, :foreign\_key=\>“ref\_company\_id”,  
:conditions=\>“ref\_meta\_id=0”

# has\_many :all-

sections, :class=\>“Section”, :foreign\_key=\>“ref\_company\_id”

# end

# class Section \< ActiveRecord

# 

belongs\_to :company, :class=\>“Company”, :foreign\_key=\>“ref\_company\_id”

# end

All these things are good in both development and production  
environment.

# Company.find(1).main-section

# Company.find(1).all-sections

# Section.find(1).company

Now comes to the find method used in controller.  
First use joins, as I said before, the following methods went well in  
both development and production enviroment.

# Company.find(:all, :select=\>‘companies.\*’, :joins=\>[:all-

sections], :conditions=\>“companies.id\<500”)

# Company.find(:all, :select=\>‘companies.\*’, :joins=\>[:all-

sections], :conditions=\>“sections.id\<500”)

Then use include,

# Company.find(:all, :select=\>‘companies.\*’, :joins=\>[:all-

sections], :conditions=\>“companies.id\<500”)  
this went well in both development and production enviroment.

However,

# Company.find(:all, :select=\>‘companies.\*’, :joins=\>[:all-

sections], :conditions=\>“sections.id\<500”)  
this went well in development environment, but in production  
environment, I get this error.

* * *

Unknown column ‘companies.ref\_company\_id’ in ‘field list’:  
SELECT `companies`.`id` AS t0\_r0,  
`companies`.`ref_company_id` AS t0\_r16,  
`companies`.`ref_meta_id` AS t0\_r17,  
`sections`.`id` AS t1\_r0,  
`sections`.`ref_company_id` AS t1\_r1,  
`sections`.`ref_meta_id` AS t1\_r2,  
FROM `companies` LEFT OUTER JOIN `sections` ON  
sections.ref\_meta\_id = companies.id  
WHERE ( sections.id\<500 )

* * *

And this is definetely a wrong SQL statement

Can anybody explain this?  
And Can anybody please explain what is the difference between include  
and join?

---

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [November 18, 2008, 6:58am UTC](https://www.ruby-forum.com/t/difference-between-include-and-joins-in-find/153508/2 "2008-11-18T06:58:27Z")

</div>

OK.

I figured out that “join” is actually doing an “inner join” which  
filters the rows that don’t have association.  
And “include” is actually doing an ‘outter join’ which shows all the  
rows from tables.

But, however, I still can not figure out why that strange SQL  
statement comes out.

Can anyone please help me?

---

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [November 18, 2008, 9:13am UTC](https://www.ruby-forum.com/t/difference-between-include-and-joins-in-find/153508/3 "2008-11-18T09:13:36Z")

</div>

I finally get this right.

Here is the conclution.

In find method,

use “:join=\>[:association\_name]” will simply do a ‘full join’, which  
drop all rows that do not match the association conditions.

use “:join=\>[‘join table\_b on table\_a.id=table\_b.xx’]”, this is a  
‘full join’ too.

use “:join=\>[‘left(or right) join table\_b on table\_a.id=table\_b.xx’]”,  
this is the usual left or right join.

use “:include=\>[:association\_name]” will be supposed to do a ‘left  
outer join’, this will work most of the time.  
but I don’t know why it sometimes generates wierd SQL statement like  
this.

###### table 1: companies

id int  
…

###### table 2: sections

id int  
ref\_company\_id int  
ref\_meta\_id int

# class Company \< ActiveRecord

# 

has\_one :main\_section, :class=\>“Section”,  
:foreign\_key=\>“ref\_company\_id”, :conditions=\>“ref\_meta\_id=0”

# 

has\_many :all\_sections, :class=\>“Section”,  
:foreign\_key=\>“ref\_company\_id”

# end

# class Section \< ActiveRecord

# 

belongs\_to :company, :class=\>“Company”, :foreign\_key=\>“ref\_company\_id”

# end

Company.find(:all, :select=\>‘companies.\*’, :include=\>  
[:all\_sections], :conditions=\>“sections.id\<500”)

* * *

Unknown column ‘companies.ref\_company\_id’ in ‘field list’:  
SELECT `companies`.`id` AS t0\_r0,  
`companies`.`ref_company_id` AS t0\_r16,  
`companies`.`ref_meta_id` AS t0\_r17,  
`sections`.`id` AS t1\_r0,  
`sections`.`ref_company_id` AS t1\_r1,  
`sections`.`ref_meta_id` AS t1\_r2,  
FROM `companies` LEFT OUTER JOIN `sections` ON  
sections.ref\_meta\_id = companies.id  
WHERE ( sections.id\<500 )

* * *

---

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [November 19, 2008, 12:42am UTC](https://www.ruby-forum.com/t/difference-between-include-and-joins-in-find/153508/4 "2008-11-19T00:42:53Z")

</div>

yes, the compannies table does not have a columns called  
ref\_company\_id.  
It is the table which is referreced by the sections table that has a  
ref\_company\_id as a foreign key.  
Can you explain why that error comes out? 'cause I cannot find any  
clue about it.

And thank you for mentioning your blog post, and I now know why I feel  
my app is much faster using join than using include.  
Thank you.

On Nov 18, 7:37 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [November 18, 2008, 11:37am UTC](https://www.ruby-forum.com/t/difference-between-include-and-joins-in-find/153508/5 "2008-11-18T11:37:55Z")

</div>

Well to answer the question in the subject line, I wrote this a little  
while back:

> **[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 …

A key thing to note is that include in 2.1 and include in 2.0.2 are  
different (but the 2.1 code will fall back to the 2.0.2 code if  
necessary).  
Does the companies table not have columns called ref\_company\_id ?

Fred

---

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [November 19, 2008, 3:21am UTC](https://www.ruby-forum.com/t/difference-between-include-and-joins-in-find/153508/6 "2008-11-19T03:21:23Z")

</div>

Harold  
to me

As far as I know, the reason for :include is mainly for eager loading.  
If you know you will be querying the sections table for the companies  
you are finding, doing an :include will retrieve those sections in one  
query, ie, one trip to the DB. If you don’t pass in the :include  
option to the initial find, doing the\_company.all-sections would go to  
the database to retrieve the associated records and then build the  
section object.

I usually use :joins when I want to narrow down the search even  
further, for instance, to companies who have sections, persons who  
also have users, etc.

For example:  
Company.find(:all, :include =\> :all-sections, :conditions =\> ‘…’)  
Would fetch all companies meeting those conditions, along with the  
associated "all-section"s. Therefore it doesn’t make sense to force it  
to :select any specific columns, especially not columns on only one of  
the tables - it defeats the purpose of the :include.

On the other hand:  
Company.find(:all, :joins =\> :all-sections, :conditions =\>  
‘…’, :select =\> ‘company._’)  
works fine, however, :select =\> 'company._’ is redundant, and if you  
will need the returned companies’ sections, you will make a trip to  
the DB that may have been avoided by using :include.

---

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [November 19, 2008, 8:29am UTC](https://www.ruby-forum.com/t/difference-between-include-and-joins-in-find/153508/7 "2008-11-19T08:29:38Z")

</div>

On 18 Nov 2008, at 23:41, boblu wrote:

> yes, the compannies table does not have a columns called  
> ref\_company\_id.  
> It is the table which is referreced by the sections table that has a  
> ref\_company\_id as a foreign key.  
> Can you explain why that error comes out? 'cause I cannot find any  
> clue about it.

It’s very weird - in particular it’s weird that it goes t0\_r0, t0\_r16:  
the number after the r is generated by an each\_with\_index loop - it  
should skip over the numbers 1-15.  
Weird stuff might happen if you had overwritten the column\_names or  
columns methods on your ActiveRecord class but I would have expected  
that to cause problems elsewhere too.

Fred

---

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [November 20, 2008, 2:45am UTC](https://www.ruby-forum.com/t/difference-between-include-and-joins-in-find/153508/8 "2008-11-20T02:45:31Z")

</div>

Sorry, that was my typo.  
The correct error was:

###### table 1: companies

id int  
name string

###### table 2: sections

id int  
ref\_company\_id int  
ref\_meta\_id int  
sec\_name string

class Company \< ActiveRecord

has\_one :main\_section, :class=\>“Section”,  
:foreign\_key=\>“ref\_company\_id”, :conditions=\>“ref\_meta\_id=0”

has\_many :all\_sections, :class=\>“Section”,  
:foreign\_key=\>“ref\_company\_id”  
end

class Section \< ActiveRecord

belongs\_to :company, :class=\>“Company”, :foreign\_key=\>“ref\_company\_id”  
end

Company.find(:all, :select=\>‘companies.\*’, :include=\>  
[:all\_sections], :conditions=\>“sections.sec\_name=‘abc’”)

* * *

Unknown column ‘companies.ref\_company\_id’ in ‘field list’:  
SELECT `companies`.`id` AS t0\_r0,  
`companies`.`name` AS t0\_r1,

```
          `companies`.`ref_company_id` AS t0_r2,
          `companies`.`ref_meta_id` AS t0_r3,
          `companies`.`sec_name` AS t0_r4,

          `sections`.`id` AS t1_r0,
          `sections`.`ref_company_id` AS t1_r1,
          `sections`.`ref_meta_id` AS t1_r2,
          `sections`.`sec_name` AS t1_r3,

```

FROM `companies` LEFT OUTER JOIN `sections` ON  
sections.ref\_meta\_id = companies.id  
WHERE ( sections.sec\_name=‘abc’ )

* * *

the werid part is

`companies`.`ref_company_id` AS t0\_r2,  
`companies`.`ref_meta_id` AS t0\_r3,  
`companies`.`sec_name` AS t0\_r4,

For now, though I am using :join and :group to get what I want.  
I still got no idea where these errors came from when using :include.

On Nov 19, 4:28 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)