How to puzzle SET ANSI_NULLS and resultset in sqlserver
When ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.
In earlier puzzle I had value of the ANSI_NULLS was set to ON. Now in this puzzle let us set the value of the ANSI_NULLS to OFF. When the value of ANSI_NULLS was off at that time, the result of the both the queries is almost identical. The key reason behind this behavior is ANSI_NULLS setting.
However, the puzzle is to answer that why the different queries are producing almost same results when there is a different setting of ANSI_NULL.
SET ANSI_NULLS ON;
-- Query1
SELECT 'MyCodetips' AS Statement11
WHERE 'Authority' IN ('m','y', 'c', 'tips', NULL);
-- Query 2
SELECT 'MyCodetips' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);
-- New puzzle
SET ANSI_NULLS OFF;
-- Query3
SELECT 'MyCodetips' AS Statement11
WHERE 'Authority' IN ('m','y', 'c', 'tips', NULL);
-- Query 4
SELECT 'MyCodetips' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);
Discover more from mycodetips
Subscribe to get the latest posts sent to your email.
