difference between having a filter condition in the where clause as against a on join clause

Analytics

difference between having a filter condition in the where clause as against a on join clause

Hello everyone,

  I have SQL question, about how does having a filter condition like (say) prod_cat = 7980 in the following two conditions will be treated

1. In a where clause of multiple joins

    something like a inner join b

                               on [join cond]

                               inner join c

                               on [join cond]

                               inner join d

                               on [join cond]

                               where prod_cat = 7980

2. directly in on clause of a complex join. something like

    something like  a inner join b

                               on [join cond]

                               inner join c

                               on [join cond]

                               and prod_cat = 7980

                               inner join d

                               on [join cond]

                                I am concerned, as sometimes explain plan shows lesser time using approach-1, and sometimes explain plan shows lesser time using approach-2. Please help me understand this concept.

Thanks.

Deepak

http://bit.ly/Next-Gen-BI

Tags (4)
1 REPLY

Re: difference between having a filter condition in the where clause as against a on join clause

Since all the joins are inner joins, the optimizer will filter the rows of table 'a' as per the condition prod_cat = 7980. The timing will not get affected by where you put it. Check the EXPLAIN plan to confirm it.