July 20, 2008 at 11:08 pm
Hello again,
I know there's 2 versions of inner joins:
1:
select * from r_user ru
inner join r_contact rc on ru.contact_fk = rc.id
inner join r_address ra on rc.contact_address_fk = ra.id
inner join r_country rctry on rctry.id = ra.country_fk
where ru.id = 1
2:
select * from r_user ru
inner join r_contact rc
inner join r_address ra
inner join r_country rctry on rctry.id = ra.country_fk
on rc.contact_address_fk = ra.id
on ru.contact_fk = rc.id
where ru.id = 1
I tried the execution plan but the 2 plans are exactly the same.
Any difference between them?
Cheers,
Elton
July 20, 2008 at 11:29 pm
Hi
Execution plans seem to be the same. But one thing i found that the first join condition that we write with the "on" clause
should be between the last and the second last tables in the from clause.
In your ex: its "on rctry.id = ra.country_fk''.
"Keep Trying"
July 21, 2008 at 1:04 am
Both are same. No difference.
July 21, 2008 at 5:01 am
These two syntax differences will always generate the same execution plans as long as you have no outer joins. Once you have outer joins, the order in which you build the second syntax will impact the query definition, possibly negating the outer join.
I would avoid the second syntax as it becomes increasingly hard to read with more complex joins.
July 21, 2008 at 5:55 pm
Hi Michael,
---These two syntax differences will always generate the same execution plans
I got some idea from the execution plan and I agree with this.
---Once you have outer joins, the order in which you build the second syntax will impact the query definition, possibly negating the outer join.
But I don't understand why the second syntax will affect the query definition the out join. Coz the result set of the inner joins are identical for both syntax, right?
Elton
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply