为什么在WHERE中null等于整数? [英] Why does null equal integer in WHERE?

问题描述

我正在使用简单连接进行查询，目的是查找用户尚未投票的最新记录:

I am doing a query with a trivial join, with the intention of finding the newest record that the user hasn't voted on yet:

SELECT
    v.user_id,
    v.version_id,
    vv.user_id
FROM versions v
LEFT JOIN versions_votes vv ON v.version_id = vv.version_id
WHERE vv.user_id != 39;

奇怪的是，如果vv.user_id为null，则此不返回任何行.我公认的行人对问题的理解是NULL 不能等于-这就是为什么首先要测试IS NULL而不是=NULL.

Curiously, this returns no rows if vv.user_id is null. My admittedly pedestrian understanding of the problem is that NULL cannot be equal to anything - that's why we have to test for IS NULL rather than =NULL in the first place.

但是，我们到了-如果我按如下所示修改WHERE子句:

And yet, here we are - and if I modify the WHERE clause as follows:

WHERE (vv.user_id != 39 OR vv.user_id IS NULL)

该查询似乎正常工作(并且还似乎确认NULL的评估结果为39.

the query appears to work properly (and also appears to confirm that NULL is evaluating to 39.

推荐答案

您说对了NULL cannot be equal to anything.
您所缺少的是 NULL也不能不相等

You are right that NULL cannot be equal to anything.
What you are missing is that NULL cannot be unequal, either.

NULL始终为NULL.当前的问题是您弄错了LEFT JOIN.这应该起作用:

NULL compared to anything is always NULL. The problem at hand is that you got the LEFT JOIN wrong. This should work:

SELECT v.user_id, v.version_id, vv.user_id
FROM   versions v
LEFT   JOIN versions_votes vv ON v.version_id = vv.version_id
                             AND vv.user_id = 39
WHERE  vv.version_id IS NULL
ORDER  BY v.created
LIMIT  1;

在WHERE子句中，还有一个引用vv的附加条件:AND vv.user_id != 39.可能期望NULL != 39符合条件，但事实并非如此.此相关答案中的更多详细信息:
使用LEFT JOIN查询不返回计数为0的行

You had an additional condition referencing vv in the WHERE clause: AND vv.user_id != 39. Probably expecting that NULL != 39 would qualify, but it doesn't. More details in this related answer:
Query with LEFT JOIN not returning rows for count of 0

基本上有三种技术可以做到这一点:
选择其他表中不存在的行

There are basically three techniques to do this:
Select rows which are not present in other table

这篇关于为什么在WHERE中null等于整数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！