-
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.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|