sql-server - SET ANSI_NULL 行为

Question

SET ANSI_NULLS ON;
-- Query1
SELECT 'StackOverFlow' AS Statement11
WHERE 'Name' IN ('S','Q', 'L', 'Name', NULL);
-- Query 2
SELECT 'StackOverFlow' AS Statement12
WHERE 'Name' NOT IN ('S','Q', 'L', NULL);

为什么查询 1 返回结果而查询 2 不返回任何结果？

Answer 1

来自 SET ANSI_NULLS (Transact-SQL)

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

现在，如果你要使用

SET ANSI_NULLS OFF;
-- Query1
SELECT 'StackOverFlow' AS Statement11
WHERE 'Name' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 2
SELECT 'StackOverFlow' AS Statement12
WHERE 'Name' NOT IN ('S','Q', 'L', NULL);

第二个查询将返回一个结果。

SQL Fiddle DEMO

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL.

编辑

正如@Damien_The_Unveliever 所提到的

来自 IN (Transact-SQL)

Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results.