Re: query results

On Wed, 12 Nov 2003 08:47:17 -0500, Fernando Nunes wrote: > 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. Agreed. Art S. Kagel