SOLVED: Difference between NOT IN and MINUS

From: yong huang <yong321_at_yahoo.com>
Date: Fri, 1 Sep 2000 11:14:12 -0700 (PDT)
Message-Id: <10606.116087@fatcity.com>

I posted the message a few hours ago. The problem turned out to be a missing null check inside the subquery:

SQL> select asset_id from easset where asset_id not in (select asset_id from table_object where asset_id is not null);

 ASSET_ID

---

     4186
     4843
     5050
     5180
     5253
     5209
     5212
     5240
     5242
     5243

which is the same result as using MINUS between two subqueries (unless there're duplicates). Without "where asset_id is not null" in this subquery, it returns no rows. The table table_object has some null's in the asset_id column.

I know Oracle has a lot of idiosyncrasy in dealing with nulls. But I didn't expect this one.

Yong Huang
yong321_at_yahoo.com

---

Do You Yahoo!?
Yahoo! Mail - Free email you can access from anywhere! Received on Fri Sep 01 2000 - 13:14:12 CDT