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

From: Tad Davis <davist_at_isc.upenn.edu>
Date: 1998/02/10
Message-ID: <34E0A28A.6B711075@isc.upenn.edu>#1/1

Kal Khatib wrote:

> 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)?.

As I understand it, IN translates into a series of OR statements: "WHERE A IN (1,2,3,4)" becomes, essentially, "WHERE A = 1 OR A = 2 OR A = 3 OR A = 4".

EXISTS will stop as soon as it gets one hit -- one reason why it's probably faster. We use it a lot in views.

-- 
Tad Davis                  Lead Programmer Analyst
davist_at_isc.upenn.edu       Information Systems and Computing
voice 215-898-7864         Administrative Information Technologies
fax   215-898-0386         University of Pennsylvania

Received on Tue Feb 10 1998 - 00:00:00 CST