sql-server - `SET ANSI_NULLS OFF` 有什么作用？

Question

SET ANSI_NULLS OFF 的作用是什么？

Answer 1

From MSDN:

The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE.

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

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with non-null values in the column. In addition, a SELECT statement using WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.