-
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
);
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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!
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
|