nested query using in clause not returning expected results

Hello all, thank you for trying to help my with my SQL question.

First the SQL:

--show you the number of rows, and show you that there are no null records:

--query 1
select count(*) from table1;
--34701398 rows returned

--query 2
select count(*) from table1 where key is null;
--0 rows returned

--query 3
select count(*) from table1 where key is not null;
--34701398 rows returned

--What I want to do is figure out the records in table 1 that have an "invalid" key:

--query 4
select count(*) from table1 where key in (select distinct key from table2);
--34595659 rows returned
--okay, so there are 34701398-34595659 = 105739 rows that have an "invalid" key.

--let's verify that:
--query 5
select count(*) from table1 where key not in (select distinct key from table2);
--0 rows returned

Wait, what? I should have 105739 rows in this select, right? There are no nulls in table1.key (shown above in queries 2 and 3). I know there are nulls in table2.key, but that shouldn't matter right?

Can someone please help me with this query? I don't understand why query 5 returns 0 rows. I believe it should return 105739 rows.

Did some more googling and found out oracle does look at the 2nd table's keys...

http://jonathanlewis.wordpress.com/2007/02/25/not-in/

Would you mind in running...

Code:

---

select count(*) from (
select distinct key from table1
minus
select distinct key from table2
);

---

Quote:

---

Originally Posted by PAVB

Would you mind in running...

Code:

---

select count(*) from (
select distinct key from table1
minus
select distinct key from table2
);

---

---

Hey - I figured out the right query:

select count(*) from table1 where key not in (select distinct key from table2 where key is not null);

But, in case you were interested, I ran your query and got count(*) = 26. Though I don't quite understand what a minus query does...

Thank you for your help, really appreciate the response!