Re: query results
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