Re: SOLVED: Difference between NOT IN and MINUS

From: William Beilstein <BeilstWH_at_OBG.com>
Date: Fri, 01 Sep 2000 14:57:11 -0400
Message-Id: <10606.116091@fatcity.com>

Try the following query

select e.asset_id from easset e where e.asset_id not exist (select * from table_object t where e.asset_id =3D t.asset_id);

>>> yong huang <yong321_at_yahoo.com> 09/01/00 03:17PM >>>
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=20

---

Do You Yahoo!?
Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/=20
--=20
Author: yong huang
  INET: yong321_at_yahoo.com=20

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 Fri Sep 01 2000 - 13:57:11 CDT