SQL Server 2000奇怪的子查询问题

Question

有一种奇怪的行为让我发疯。

我在旧的 SQL Server 2000 数据库中有一个用户表和一个权限表。 这个数据库一团糟，很多表没有PK，表之间没有关系......但我无法修复它（老实说，我不认为这与我遇到的问题有关）。

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

我想在没有任何许可的情况下获取用户的用户ID。

我的第一个方法是写一些东西：

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

不返回任何行。

但我知道有些用户没有权限，所以我编写了第二个查询：

select distinct U.IDRecord 
from Users U
left join Permissions P
    on P.IDUser = U.IDRecord
where P.IDRecord is null

正确返回没有权限的用户。

那么，问题出在哪里呢？

为什么第一个不起作用？

Answer 1

这是预期的行为。
这是预期的，因为 SQL 具有三值逻辑。
换句话说：对于那些没有权限的用户，子查询不会返回任何结果（NULL）。
在这些情况下，您的 WHERE 条件不满足，因为值永远不能等于或不等于 NULL。

替代方案：
1）使用LEFT JOIN（正如您所做的那样），或者
2) 使用 NOT EXISTS，例如：

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

---

编辑：如果您不小心，3VL 将如何咬您的更多详细信息：
如果您执行以下操作，可能会出现违反直觉的结果...

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

突然，a_column 为 NULL 的行从您的结果中消失。
要找回它们，您可以这样做：

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