mysql ansi nulls_SQL学习笔记之ANSI_NULLS

SET ANSI_NULLS{ON|OFF}:用来指定对空值使用等于(=)和不等于(<>)比较运算符时，这些运算符应遵从什么行为.具体是什么样的行为呢？SQL-92要求如下：

SQL-92 标准要求对空值的等于 (=) 或不等于 (<>) 比较取值为 FALSE，是默认值。

当 SET ANSI_NULLS 为 ON 时，即使 column_name 中存在空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。即使 column_name 中存在非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍返回零行。

当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中含有空值的行；使用 WHERE column_name <> NULL 的 SELECT 语句返回列中含有非空值的行；使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有非 XYZ 值和非 NULL的行。

SQL Server联机丛书关于ANSI_NULLS里有这样一个代码示例，给出了直观的效果，我添加了输出提示，如下：

-- Create table t1 and insert values.

CREATE TABLE t1 (a INT NULL)

INSERT INTO t1 values (NULL)

INSERT INTO t1 values (0)

INSERT INTO t1 values (1)

GO

-- Print message and perform SELECT statements.

PRINT 'Testing default setting'

DECLARE @varname int

SELECT @varname = NULL

SELECT *

FROM t1

WHERE a = @varname     --结果为空

SELECT *

FROM t1

WHERE a <> @varname    --结果为空

SELECT *

FROM t1

WHERE a IS NULL        --结果为NULL

GO

-- SET ANSI_NULLS to ON and test.

PRINT 'Testing ANSI_NULLS ON'

SET ANSI_NULLS ON

GO

DECLARE @varname int

SELECT @varname = NULL

SELECT *

FROM t1

WHERE a = @varname     --结果为空

SELECT *

FROM t1

WHERE a <> @varname    --结果为空

SELECT *

FROM t1

WHERE a IS NULL        --结果为NULL

GO

-- SET ANSI_NULLS to OFF and test.

PRINT 'Testing SET ANSI_NULLS OFF'

SET ANSI_NULLS OFF

GO

DECLARE @varname int

SELECT @varname = NULL

SELECT *

FROM t1

WHERE a = @varname     --结果为NULL

SELECT *

FROM t1

WHERE a <> @varname    --结果为0，1

SELECT *

FROM t1

WHERE a IS NULL        --结果为NULL

GO

-- Drop table t1.

DROP TABLE t1

从上面代码的运行效果来看：

①当SET ANSI_NULLS ON时， =null或 <> null的查询条件时，结果都为空；如果为 IS NULL 才能显示NULL的行。

②不对ANSI_NULLS进行设置时，其执行效果与SET ANSI_NULLS ON时的效果一致，此处与SQL-92的要求之一“SQL-92 标准要求对空值的等于 (=) 或不等于 (<>) 比较取值为 FALSE，是默认值。”冲突哦？！

又怎么理解这个问题呢？

SQL Server联机丛书里ANSI_NULLS的知识文档里有答案：“SQL Server ODBC 驱动程序和用于 SQL Server 的 Microsoft OLE DB 提供程序在连接时自动将 ANSI_NULLS 设置为 ON。该设置可以在 ODBC 数据源、ODBC 连接特性或 OLE DB 连接属性(它们在连接到 SQL Server 之前在应用程序中设置)中进行配置。”

PS：其实，实际使用过程中，我们并没有考虑ANSI_NULLS是被设置为ON还是False,我们对空值的处理都是IS NULL 或者 IS NOT NULL