What is the difference?

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 28 Sep 2003 13:36:06 -0700
Message-ID: <1064781368.709803@yasure>

I know its Sunday and I should be out enjoying the 83 degree weather with beautiful blue skies ... but my brain is obviously fried as I can't figure out why these two SQL statements produce different results form the exact same data set.

SELECT person_id
FROM person
WHERE app_status = 'RA'
AND gender = 'F'
AND person_id NOT IN (
SELECT DISTINCT candidate_id
FROM examiner_candidate_ie);
-- returns 0 rows

SELECT person_id
FROM person p
WHERE app_status = 'RA'
AND gender = 'F'
AND NOT EXISTS (
   SELECT candidate_id
   FROM examiner_candidate_ie
   WHERE candidate_id = p.person_id);
-- returns the correct 17 rows

And if you think I should feel like fool for not knowing myself ... you're too late. I already do. It has got to be something blatantly obvious ... but I'm blatantly oblivious.

Thanks.

--

Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu

(replace 'x' with a 'u' to reply) Received on Sun Sep 28 2003 - 15:36:06 CDT