query results

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...? sending to informix-list

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...? When you do a sub-query the column name domain includes the exterior tables. Besides this and as a side note that query can be extremely inefficient. In each sub-query execution the engine allocates temporary space and gets all rows from the interior table. Only after that does it check the condition. This can be a killer for big interior tables. A much better way: select acct_nbr, open_date, transfer_nbr from acct t1 where exists (select t2.col_acct_nbr from xref t2 where t2.col_acct_nbr = t1.acct_nbr) Specially if the xref table has an index on column col_acct_nbr. Regards.