Hi All,

I have a simple subquery:

select ckt_id, charges
from table A
where ckt_id not in (select distinct ckt_id from B) and charges > 0

I know of at least one record in A whose ckt_id is not present in
table B and charges > 0 which is not being returned.

Table A has about 15000 records and B has about 50000 records.

What am I missing here?

Thanks in advance,
ZSashi

On Apr 8, 10:05*am, Sashi <small... (AT) gmail (DOT) com> wrote:
We don't know because you haven't posted:

The Oracle release you're using
The DDL for the tables involved
Sample data for these tables

Provide the missing information and maybe someone can assist you.


David Fitzjarrell

On Apr 8, 11:05*am, Sashi <small... (AT) gmail (DOT) com> wrote:
First I suggest you label the chk_id column in the subquery (b.chk_id)
to take it out of the scope of table A. Second a
"not in" is not exactly the opposite of an "in" clause so never use
one where a NULL can be returned.

HTH -- Mark D Powell --

Sashi wrote:

While Oracle does attempt to automatically give your column names an
alias, you need to get in the habit of using EXPLICIT names like:

select a.ckt_id, a.charges
from tablea A
where a.ckt_id not in (select distinct b.ckt_id from tableb B)
and a.charges > 0

On Apr 8, 4:05*pm, Sashi <small... (AT) gmail (DOT) com> wrote:
Just to follow on from what Mark said about null values, try:

select ckt_id, charges
from table A
where ckt_id not in
( select b.ckt_id from b
where b.ckt_id is not null )
and charges > 0;

This is a bit of a guess in the absence of complete details, though.