DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: nested query using in clause not returning expected results

Hybrid View

  1. #1
    Join Date
    Apr 2010
    Posts
    3

    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.

  2. #2
    Join Date
    Apr 2010
    Posts
    3
    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/

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  4. #4
    Join Date
    Apr 2010
    Posts
    3
    Quote Originally Posted by PAVB View Post
    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
  •  


Click Here to Expand Forum to Full Width