问题描述
此查询为什么返回0行?
Why is this query returning 0 rows?
select t.f1, t.f2
from (select null f1, 'a' f2 from dual) t
where t.f1<>t.f2;
这是我拥有的复杂查询的简化版本.我想比较包含一对一相关数据的两个表，我想选择某些字段包含不同值的行.但也有可能其中一张表中缺少一行. LEFT JOIN正确为这些行返回空值，但随后WHERE子句错误地(或意外地)将这些行过滤掉了.
This is a distilled version of a complex query I have. I want to compare two tables containing one-to-one related data and I want to select those rows that contain different values for certain fields. But also there can be the case where one row is missing in one of the tables. The LEFT JOIN correctly returns null values for these rows, but then, the WHERE clause is incorrectly (or unexpectedly) filtering these rows out.
为什么-在这种情况下-'null'与任何非null值(例如'a')没有区别?
让我发疯的是这个
select t.f1, t.f2
from (select null f1, 'a' f2 from dual) t;
返回1行(正如我预期的那样)，但这
returns 1 row (as I expected) but this
select t.f1, t.f2
from (select null f1, 'a' f2 from dual) t
where t.f1=t.f2;
返回0行！ 因此null不等于'a'并且null与'a'相同！
请...有人可以解释吗?
Please... Can anybody explain this?
推荐答案
完全正确. NULL表示未知值，不是任何特定值(与C中的NULL或Ruby中的nil不同，等等.)在SQL中，如果将某些内容与未知值进行比较，则结果为也未知.并且您将不会获得WHERE条件未知的行.
Exactly. NULL represents an unknown value, not any specific value (it is not the same as NULL in C, or nil in Ruby, etc.) In SQL, if you compare something to the unknown value, the result is also unknown. And you will not get the rows where WHERE condition is unknown.
尝试一下:
SELECT NULL <> 2;
，您将看到NULL作为结果.
尝试一下:
SELECT * FROM t WHERE NULL;
即使表t很大，也不会有行出现.
and no rows will come out, even if the table t is huge.
如果您确实需要您想要的内容(我不主张这样做)，则可以执行以下操作:
If you really need what you said you wanted (and I am not advocating this), you can do something like this:
SELECT T.f1, T.f2
FROM (SELECT NULL f1, 'a' f2) T
WHERE ((T.f1 IS NULL OR T.f2 IS NULL)
AND (T.f1 IS NOT NULL OR T.f2 IS NOT NULL))
OR T.f1 <> T.f2
这篇关于MySQL之谜:空值与非空字符串没有区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！