Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

IN vs. EXISTS ...... NOT IN vs. NOT EXISTS

0 views

Skip to first unread message

AMARENDRA B NETTEM

unread,

Feb 12, 1998, 3:00:00 AM2/12/98

to Kal Khatib

Kal Khatib wrote:

> I have statements where i use the above oracle functions.
> IN and EXISTS seem _not_ to be interchangable (same for negation)
> Could some explain the differnece in behavior between those functions
> (beside performace.. IN is slower I think)?.
> I find it tricky to use one vs. the other. I end up having to
> experimenting with permutation
> to get the logic right.
> thanks
> Kal.
> --
> http://members.tripod.com/~kalk

You can use NOT EXISTS in place of NOT IN. NOT IN Operator doesn't work
if your subquery is returning NULL values (because NOT IN looks for AND
condition),
Where as NOT EXISTS operator looks for either TRUE OR FALSE condition.

check this:

SELECT * FROM EMP WHERE DEPTNO NOT IN (10,20,NULL)

Hope this helps

AMARENDRA
Oracle DBA
Whittman-Hart Inc.

--
*****************************************************************
AMARENDRA B NETTEM
Oracle Certified DBA (OCP)
Whittman-Hart Inc.,
311 South Wacker Drive, Suite 3500
Chicago, IL 60606.

Residence:
-----------
5039 N E River Road,
Apt. 1A
NORRIDGE, IL 60656

Ph.No. (708) 583 9870 (H)
(312) 913 6758 (W)

E-mail:net...@charlie.cns.iit.edu,
ane...@whittman-hart.com

Homepage: http://www.iit.edu/~nettama

****************************************************************
Opinions are mine and do not necessarily reflect those
of Whittman-hart Inc.

0 new messages