Re: What is the difference?

"Daniel Morgan" <damorgan_at_x.washington.edu> a écrit dans le message de news:1064781368.709803_at_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)
>

May be there is a NULL candidate_id.
In this case the NOT IN is never TRUE.
To have the same result, you have to use the NVL function in the subquery with NOT IN.

Hope this helps
Michel Cadot Received on Sun Sep 28 2003 - 16:02:40 CDT