Re: IN vs. EXISTS ...... NOT IN vs. NOT EXISTS

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:nettama_at_charlie.cns.iit.edu,
        anettem_at_whittman-hart.com

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

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