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.

Click to Download Scripts

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

About these ads

4 thoughts on “SQL SERVER- Solution – SQL Puzzle of SET ANSI_NULL

  1. Not related to this post.

    Did you recently change the theme of your blog? Also I notice a change in subtitle. Is that a change in direction? I look forward to your posts on BigData.

  2. Hello Pinal,

    Thank you for your excellent newsletter!

    I noticed something in the explanation of a puzzle in Monday’s newsletter that I think needs to be tweaked, unless I be misunderstanding the puzzle.

    >> 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 = ‘Authority’ AND Condition = NULL In this case if ALL of the above condition returns true it the query will return value ‘SQLAuthority’

    ‘Authority’ NOT IN (‘S’,’Q’,’L’,NULL) is equivalent to ‘Authority’ ‘S’ AND ‘Authority’ ‘Q’ AND ‘Authority’ ‘L’ AND ‘Authority’ NULL .

    The reason it fails is because of the single comparison at the end to NULL, which will always fail, whether checking for equality or inequality, according to the documentation in SQL Server 2005 Books Online:

    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 nonnull values in column_name.

    So, if ANSI_NULLS were set to OFF, all of the conditions would be true, and the query results would be the same; since ANSI_NULLS is off, it fails to produce any rows.

    I presume that this is what you meant, but just slipped a cog in the memory… ;)

    René A Valencourt, CCP, MCTS IS Dept. CTB, Inc. A Berkshire Hathaway Company

    [fax address removed]

  3. Hello Pinal Dave,

    IF
    ‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL)
    evaluates to FALSE,
    THEN
    NOT (‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL))
    would evalute to
    NOT (FALSE) ==>
    TRUE
    This is not the case in the example.

    Three valued logic should be used.

    Classic Boolean rules:
    B1 valuex = valuex results into TRUE
    B2 valuex = valuey results into FALSE (where valuex and valuey are B3 different)
    B4 TRUE OR TRUE results into TRUE
    B5 TRUE AND TRUE results into TRUE
    B6 TRUE OR FALSE results into TRUE
    B7 TRUE AND FALSE results into FALSE
    B8 FALSE OR FALSE results into FALSE
    B9 FALSE AND FALSE results into FALSE
    B10 NOT (FALSE) results into TRUE
    B10 NOT (TRUE) results into FALSE

    Extra rules for three valued logic:
    R0 value = NULL results into NULL
    R1 NULL AND NULL results into NULL
    R2 TRUE and NULL results into NULL
    R3 FALSE OR NULL results into NULL
    R4 NULL OR NULL results into NULL
    R5 NOT(NULL) results into NULL
    R6 FALSE AND NULL results into FALSE <<<<—- Often forgotten.
    R7 TRUE OR NULL results into TRUE <<<
    NOT (‘Authority’ = ‘S’ OR ‘Authority’ = ‘Q’ OR ‘Authority’ = ‘L’ OR ‘Authority’ = NULL) ==>
    B2 NOT (FALSE OR FALSE OR FALSE OR ‘Authority’ = NULL) ==>
    B8 NOT (FALSE OR ‘Authority’ = NULL) ==>
    R0 NOT (FALSE OR NULL) ==>
    R3 NOT (NULL) ==>
    R5 NULL

    A WHERE NULL wil not display the row. (WHERE NULL is treated equivalent to WHERE FALSE).

    A NULL should not be treated as equal to FALSE until the complete statement is evaluated.
    Because of R6 and R7, it can not be stated that any expressing with a NULL will result in a NULL, because R6 and R7 do not evaluate to a NULL. R0 to R5 do evaluate to a null.

    Thanks for your column,
    Ben Brugman

  4. SELECT ‘SQLAuthority’ AS Statement12
    WHERE ‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL)
    NULL Is Not a String Data Type.

    Same result
    Select ‘1’ as irfan Where ‘1’ Not IN (‘2′,’3′,’4′,1)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s