Re: The tale of two queries

Hi,
chech next sql:
select * from dual where dummy not in ( select 'a' from dual union select '' from dual);
Root node have NULL parent.

krao wrote:

> Hello all,
>
> I'm trying to find the leaf nodes in a tree structure described via the
> following table (XYZ). And, the way I look for leaf nodes is by identifying the
> nodes that don't appear as the parent of another node. Sounds simple enough.
> But, I'm surprized to find that the two queries below don't give the same
> result. In fact, the first query returns nothing! Is there something missing
> in Query 1? (I trust the results given by Query 2.)
>
> SQL> desc XYZ;
> Name Null? Type
> ------------------------------- -------- ----
> XYZSEQ NOT NULL NUMBER(38)
> NAME NOT NULL VARCHAR2(50)
> XYZ_PARENTSEQ NUMBER
> <and some more fields>
>
> Query 1:
>
> select distinct C1.name
> from XYZ C1
> where C1.xyzseq not in (select C2.xyz_parentseq from xyz C2);
>
> Query2:
>
> select distinct C1.name
> from xyz C1
> where not exists (select 1 from xyz C2 where C2.xyz_parentseq = C1.xyzseq);
>
> thanks in advance,
> - kr
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: krao
> INET: krao_at_callidussoftware.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Sat Dec 09 2000 - 07:22:07 CST