Hi,

I am working on a query like the following:

select a.f1,b.f2,c.f3
from t1 as a
join t2 as b
on a.id=b.id
left join t3 as c
on b.id2=c.id2
where a.type in ('a','b','c');

someone told me that it is not efficient because all rows in table a will be readed before the where statement is used to narrow down the population. Is this true? If so, if I change the query as the following, will the performance be improved?

select a.f1,b.f2,c.f3
from t1 as a
join t2 as b
on a.id=b.id
and a.type in ('a','b','c');
left join t3 as c
on b.id2=c.id2
;

Thanks for your help : )

Hi,

According to my knowleadge the SQL's are the same. Ther DB2 rewriter will most probably rewrite the SQL. Try using SQL explain to see the access path or use the visual explain from Command Center.

You can also read the following thread about Performance.

Hope this helps,
Grofaty

Your explain will also give you the Optimized SQL ... This will help you to understand how DB2 has 'rewritten' your statement

Cheers
Sathyaram