NOT IN query... odd results

Question

I need a list of users in one database that are not listed as the new_user_id in another. There are 112,815 matching users in both databases; user_id is the key in all queries tables.

Query #1 works, and gives me 111,327 users who are NOT referenced as a new_user_Id. But it requires querying the same data twice.

-- 111,327 GSU users are NOT listed as a CSS new user 
--   1,488 GSU users ARE listed as a new user in CSS
--
select count(gup.user_id)
from   gsu.user_profile gup
  join (select cud.user_id, cud.new_user_id, cud.user_type_code
        from   css.user_desc cud) cudsubq
    on gup.user_id = cudsubq.user_id
where  gup.user_id not in (select cud.new_user_id 
                           from   css.user_desc cud
                           where  cud.new_user_id is not null);

Query #2 would be perfect... and I'm actually surprised that it's syntactically accepted. But it gives me a result that makes no sense.

-- This gives me 1,505 users... I've checked, and they are not
-- referenced as new_user_ids in CSS, but I don't know why the ones 
-- that were excluded were excluded.
--
-- Where are the missing 109,822, and whatexcluded them?
-- 
select count(gup.user_id)
from   gsu.user_profile gup
  join (select cud.user_id, cud.new_user_id, cud.user_type_code
        from   css.user_desc cud) cudsubq
    on gup.user_id = cudsubq.user_id
where  gup.user_id not in (cudsubq.new_user_id);

What exactly is the where clause in the second query doing, and why is it excluding 109,822 records from the results?

Note The above query is a simplification of what I'm really after. There are other/better ways to do the above queries... they're just representative of the part of the query that's giving me problems.

Answer 1

Read this: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:442029737684

For what I understand, your cudsubq.new_user_id can be NULL even though both tables are joined by user_id, so, you won't get results using the NOT IN operator when the subset contains NULL values . Consider the example in the article:

select * from dual where dummy not in ( NULL )

This returns no records. Try using the NOT EXISTS operator or just another kind of join. Here is a good source: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

And what you need is the fourth example:

SELECT COUNT(descr.user_id)
FROM 
    user_profile prof
    LEFT OUTER JOIN user_desc descr
        ON prof.user_id = descr.user_id 
WHERE descr.new_user_id IS NULL
    OR descr.new_user_id != prof.user_id