SQL Server 2000 strange subquery problem

There's a strange behaviour that is drive me crazy.

I've a table of users and a table of permissions in an old SQL Server 2000 database. This database is a mess, many tables have no PK and there are no relations between the tables... but I couldn't fix it (and honestly I don't think this is related to the problem I have).

Users:
IDRecord -> PK money
-- other fields

Permissions:
IDRecord -> money (is not a PK)
IDUser   -> money (refers to Users.IDRecord WITHOUT FK)
Function -> varchar
-- other fields

I want to get the User's ids of the users without any permission.

My first approach was to write something as:

select distinct IDRecord 
from Users
where IDRecord not in (
    select IDUser from Permissions
)

That returns me no rows.

But I KNOW there are users without permissions, so I write a second query:

select distinct U.IDRecord 
from Users U
left join Permissions P
    on P.IDUse开发者_高级运维r = U.IDRecord
where P.IDRecord is null

that correctly returns the users without permissions.

So, where's the problem?

Why the first doesn't work?

This is expected behavior.
It is expected because SQL has three-valued logic.
In other words: for those users who have no permissions, there is no result (NULL) returned by your subquery.
Your WHERE condition is not satisfied in those cases because a value can never equal nor not equal NULL.

Alternatives:
1) use a LEFT JOIN (as you have done), or
2) use NOT EXISTS, e.g.:

SELECT DISTINCT IDRecord 
  FROM Users u
 WHERE NOT EXISTS (
         SELECT 1 
           FROM Permissions p 
          WHERE p.IDUser = u.IDRecord
       );

Edit: More detail on how 3VL can bite you if you're not careful:
A possibly counter-intuitive result occurs if you do something like this...

...
WHERE a_column <> 'some value';

Suddenly rows where a_column is NULL disappear from your results.
To get them back you can do this:

...
WHERE (a_column <> 'some value' OR a_column IS NULL);