Re: What is wrong with this query?

In article <3p8bd4$nj2_at_news3.digex.net> joe_at_access1.digex.net (Joe Nardone) writes:

> As part of an error trapping routine, I'm having a problem with
> what should be a fairly simple query.
>
> First, the query:
>
> select bill_acct_id from transmission
> where bill_acct_id not in (select acct_id from convert_acct);
>
> Basically, I want a list of all 'bill_acct_id's in the 'transmission'
> table that are not in the conversion table (convert_acct). I know
> for a fact that there are bill_acct_id's not in the conversion
> table, yet the query returns no rows. Why?

This query should work just fine at first glance. But depending on the number of rows in convert_acct, the following SQL statement might be preferable due to performance considerations:

select bill_acct_id
from transmission t
where not exists (

	select null
	from convert_acct
	where acct_id = t.bill_acct_id

or

select bill_acct_id
from transmission t, convert_acct c
where t.bill_acct_id = c.acct_id (+)
and c.rowid is null;

By using "NOT IN", you are basically walking down the entire list of acct_id in convert_acct in order to identify a "mismatch", something which is definitely not desirable if you have a large number of rows in convert_acct.

--
"And this is all I have to say about that..."   - F. Gump
      ___
     (o o)
+-oo0-\_/-0oo---------------------------------------------------------------+
|  Alvin W. Law ..... Oracle Corporation ....... Email: alaw_at_us.oracle.com  |
+---------------------------------------------------------------------------+

ORA-03113: end-of-file on communication channel