The tale of two queries

From: krao <krao_at_callidussoftware.com>
Date: Thu, 7 Dec 2000 14:15:26 -0800
Message-Id: <10703.123965@fatcity.com>

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); Received on Thu Dec 07 2000 - 16:15:26 CST