Re: What is the difference?

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message 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)
>

Daniel,

Let me show you by example and see if the answer pops out :)

SQL> create table abc as select rownum a from user_tables;
SQL> create table xyz as select rownum x from user_tables where 1=2;
SQL> set feedback on
SQL> select count(*) from abc;

  COUNT(*)

       548

1 row selected.

SQL> select count(*) from xyz;

  COUNT(*)

         0

1 row selected.

SQL> select count(*) from abc where a not in (select x from xyz);

  COUNT(*)

       548

1 row selected.

SQL> select count(*) from abc where not exists (select 1 from xyz where xyz.x = abc.a);

  COUNT(*)

       548

1 row selected.

SQL> insert into xyz values (null);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from abc where a not in (select x from xyz);

  COUNT(*)

         0

1 row selected.

SQL> select count(*) from abc where not exists (select 1 from xyz where xyz.x = abc.a);

  COUNT(*)

       548

1 row selected.

HTH Anurag Received on Sun Sep 28 2003 - 15:50:26 CDT