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

在 SQLFiddle

这篇关于SQL 为空且 = 空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！