Re: query results

Art S. Kagel wrote: > On Fri, 07 Nov 2003 10:00:42 -0500, Campbell, John (CONS FIN , MCS) wrote: > > > >>Solaris 2.8, IDS 9.30.UC6W1 >> >>The following user query - >> >>select acct_nbr, open_date, transfer_nbr from acct where acct_nbr in (select>>acct_nbr from xref); >> >>The column acct_nbr does not exist in the table xref. Yet the query continues >>to run and returns many more rows than expected as if ignoring the >>subselect...? > > > Fernando is correct. You are returning every row from acct because the > acct_nbr the sub_query is seeing is the one from the outer select. However, > even better than the EXISTS clause Fernando suggested would be a straight > forward join: > > select a.acct_nbr, a.open_date, a.transfer_nbr > from acct a, xref x > where a.acct_nbr = x.ref_acct_nbr ... <other filters> ... ;> > This one will fly, and as Fernando points out especially if there are other > selection criteria and xref.ref_acct_nbr has an index. > > Fernando's EXISTS sub-query is a correlated sub-query which is always slower > than a join and can always be converted into a join. The optimizer (since 7.30 > anyway) will try to unfold the sub-query into a join but it never hurts to do > the deed yourself, just to be safe. > > You need to give your users a class in crafting efficient SQL. > > Art S. Kagel > > Art S. Kagel You have a point... But if the "interior" table has duplicates the join will turn your result set in a night mare of duplicates :) The point of using EXISTS is to avoid this. If the column has no duplicates then use the join of course. Regards.