Re: Sub query Vs. Join

krishnakn@yahoo.com wrote: > > Can anyone pl verify the following two queries and say whether > both of them are same... They look the same to me. > 1. select DISTINCT a.* > from t_aps_grp a, t_aps_usr b, t_bat_ent_log c, t_batch d, > t_aps_batch_item e, t_applacct f > where a.grp_id = b.grp_id > and b.login_id = c.login_id > and c.batch_id = d.batch_id > and d.batch_id = e.batch_id > and e.applacct_id = f.applacct_id > and f.applacct_id > 2000 ; > > 2. > select * from t_aps_grp > where grp_id in (select grp_id from t_aps_usr where login_id in > (select login_id from t_bat_ent_log where batch_id in > (select batch_id from t_batch where batch_id in > (select batch_id from t_aps_batch_item > where applacct_id in > (select applacct_id from t_applacct > where applacct_id > 2000))))); > If I remove the DISTINCT from the first query, it returns more rows than > the second.( I think it b'cos of cartesian product in case of join). It is the difference between the IN clause which will net the list of keys returned resulting in one copy of the outer table's row to be returned -vs- the join which will duplicate the independent table's row for each matching dependent table row. So your analysis is essentially correct. You could get the same result with nearly the same performance improvement by turning all of the dependent tables into a subquery as a join but keeping the IN (SELECT...) as the criteria in the outer SELECT. > Which one is faster?????? Only experimentation will tell which is best. However, typically, if the indexes needed to support the join are available the join will be faster than the sub-query version, if not the sub-query may actually be faster. Keep in mind IDS 7.3x will flatten the former query into the latter anyway internally. Art S. Kagel