Re: Subquery not returning results

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 8 Apr 2009 09:11:06 -0700 (PDT)
Message-ID: <1523f915-ddf8-41fc-abf2-babd5ac70150_at_e21g2000yqb.googlegroups.com>



On Apr 8, 11:05 am, Sashi <small..._at_gmail.com> wrote:
> 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

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 -- Received on Wed Apr 08 2009 - 11:11:06 CDT

Original text of this message