SQL SERVER- Solution – SQL Puzzle of SET ANSI_NULL

Earlier I have posted a puzzle which received so many valid responses and got a fantastic explanation to the questions as well. I encourage all of you to read the original puzzle here.

First run following script:

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

You will get following result:

You can clearly see that in the first case we are getting different results.

Question: Why do Query 1 return results but Query 2 does not return any result?

The answer is very simple and easy. Let us see the answer

Solution:

In case of the Query1
SELECT ‘SQLAuthority’ AS Statement11
WHERE ‘Authority’ IN (‘S’,’Q’, ‘L’, ‘Authority’, NULL);

Explanation:
IN Operator is equivalent to
Condition = ‘S’ OR Condition = ‘Q’ OR Condition = ‘L’ OR Condition = ‘Authority’ OR Condition = NULL

Above query represents in our case as

‘Authority’ = ‘S’ OR ‘Authority’ = ‘Q’ OR ‘Authority’ = ‘L’ OR ‘Authority’ = ‘Authority’ OR ‘Authority’ = NULL

Returns FALSE OR FALSE OR FALSE OR TRUE OR FALSE so the final result is TRUE.

In this case if ANY of the above condition returns true it the query will return value ‘SQLAuthority’

In case of the Query2
SELECT ‘SQLAuthority’ AS Statement12
WHERE ‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL);

Explanation:
NOT IN Operator is equivalent to
Condition != ‘S’ AND Condition != ‘Q’ AND Condition != ‘L’ AND Condition != NULL

Above query represents in our case as

‘Authority’ != ‘S’ AND ‘Authority’ != ‘Q’ AND ‘Authority’ != ‘L’ AND ‘Authority’ != NULL

Returns TRUE AND TRUE AND TRUE AND FALSE so the final result is FALSE.

In this case if ALL of the above condition returns true it the query will return value ‘SQLAuthority’

I guess this solves the puzzle. However do not forget to note that in my query I have ANSI_NULL ON. When ANSI_NULLS is ON, any comparison operation with one of the operands is NULL will evaluate to UNKNOWN. For the result to include a record, all the operands for that record should evaluate to TRUE. We can absolutely get different results if we have ANSI_NULLS OFF.

We will continue the discussion in future blog posts. Additionally, the winners of the contests will be announced in the original blog posts.

Reference: Pinal Dave (https://blog.sqlauthority.com)