SQL 为空且 = 空 [英] SQL is null and = null
问题描述
可能的重复:
什么是=null"和“为空”
IS NULL 和 =NULL 有什么区别
有什么区别
where x is null
和
where x = null
为什么后者不起作用?
推荐答案
在 SQL 中,null
值与任何其他值(包括另一个 null
)之间的比较使用比较运算符(例如 =
、!=
、<
等)将导致 null
,即在 where 子句中被认为是 false
(严格来说,它是not true",而不是false",但效果是一样的).
In SQL, a comparison between a null
value and any other value (including another null
) using a comparison operator (eg =
, !=
, <
, etc) will result in a null
, which is considered as false
for the purposes of a where clause (strictly speaking, it's "not true", rather than "false", but the effect is the same).
推理是 null
表示未知",因此与 null
进行任何比较的结果也是未知".因此,通过对 where my_column = null
进行编码,您将不会遇到行.
The reasoning is that a null
means "unknown", so the result of any comparison to a null
is also "unknown". So you'll get no hit on rows by coding where my_column = null
.
SQL 提供了用于测试列是否为 null
的特殊语法,通过 is null
和 is not null
,这是一个特殊条件测试 null
(或不是 null
).
SQL provides the special syntax for testing if a column is null
, via is null
and is not null
, which is a special condition to test for a null
(or not a null
).
这里有一些 SQL 显示了各种条件及其效果,如上所述.
Here's some SQL showing a variety of conditions and and their effect as per above.
create table t (x int, y int);
insert into t values (null, null), (null, 1), (1, 1);
select 'x = null' as test , x, y from t where x = null
union all
select 'x != null', x, y from t where x != null
union all
select 'not (x = null)', x, y from t where not (x = null)
union all
select 'x = y', x, y from t where x = y
union all
select 'not (x = y)', x, y from t where not (x = y);
仅返回 1 行(如预期):
returns only 1 row (as expected):
TEST X Y
x = y 1 1
这篇关于SQL 为空且 = 空的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!