SQL SERVER – SQL Puzzle of SET ANSI_NULL – Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C

Note: This contest is over, so enjoy the brain teaser about ANSI_NULL. We all love puzzles and here is interesting puzzle which you can play with me and win Amazon Gift Cards and Bubble Copter R/C. The contest for Amazon Gift Card is open worldwide, however, Bubble Copter winner will be chosen from USA only.

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 the following result:

SQL SERVER - SQL Puzzle of SET ANSI_NULL - Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C emptyresult

You can clearly see that in the first case we are getting different results. Here are the questions you need to answer to win the Amazon Gift Cards and Bubble Copter R/C.

There are two steps to take participate in the contest:

Step 1: Answer the Question

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

Answer this question in comments area along with the question in Step2.

Giveaway:

  • 2 lucky winners will get USD 25 worth Amazon Gift Card (Open worldwide, Total giveaway value USD 50)
  • One lucky winner from USA will get  Bubble Copter R/C (Shipping from USA to other countries is not possible)

SQL SERVER - SQL Puzzle of SET ANSI_NULL - Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C wincopteramazon

Rules and Conditions:

  • Contest open till May 25, 2013 12:00 GMT. 
  • Please leave your answer in the comment area in following format:
    • Answer to Q1:
  • Please note that winner will be selected after May 25th by random selection and will be posted as a comment to this blog.
  • The answers will be kept hidden till the winner will be announced to have fair competition.

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

Quest

SQL Scripts
Previous Post
SQL SERVER – How to use xp_sscanf in Real World Scenario?
Next Post
SQL SERVER – Solution to Puzzle – REPLICATE over 8000 Characters

Related Posts

115 Comments. Leave new

  • ANSI_NULLS ON

    This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value

    SAME IF WILL OFF WILL GET THE RETURN SET
    SET ANSI_NULLS ON;
    — Query1
    SELECT ‘SQLAuthority’ AS Statement11
    WHERE ‘Authority’ IN (‘S’,’Q’, ‘L’, ‘Authority’, NULL);
    — Query 2
    SET ANSI_NULLS OFF;
    SELECT ‘SQLAuthority’ AS Statement12
    WHERE ‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL);

    Reply
  • K.V. Ram Kumar
    May 17, 2013 1:21 pm

    Answer to Q1:
    ——————
    — Query1
    SELECT ‘SQLAuthority’ AS Statement11
    WHERE ‘Authority’ IN (‘S’,’Q’, ‘L’, ‘Authority’, NULL);

    [Ram] The Query1 equates to
    SELECT ‘SQLAuthority’ AS Statement11
    WHERE ‘Authority’=’S’ OR ‘Authority’=’Q’ OR ‘Authority’=’L’ OR ‘Authority’=NULL
    Any one condition can be true for OR operator.So, this query gives the result.

    — Query 2
    SELECT ‘SQLAuthority’ AS Statement12
    WHERE ‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL);

    [Ram] The Query2 equates to
    SELECT ‘SQLAuthority’ AS Statement11
    WHERE ‘Authority’=’S’ AND ‘Authority’=’Q’ AND ‘Authority’=’L’ AND ‘Authority’=NULL
    Every condition should be true for AND operator. So, this query doesn’t display any result.

    Answer to Q2:
    ——————
    The size of file is 11683.84 KB

    Reply
  • ANSI_NULL is ON; In this case it willn’t handle NULL, Hence remove the NULL in the second query or set ANSI_NULL OFF

    Reply
  • since not in is a logical operator

    Logical Operators
    Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE or FALSE.

    When

    SET ANSI_NULLS ONit means ISO Standard is being followed.
    means
    = and should not be used for null comparison.

    If you want to use = or for null comparison use

    SET ANSI_NULLS OFFit means do not follow ISO Standard.

    SET ANSI_NULLSshould be set to ON for executing distributed queries
    for maintaining compatibility of queries accross Servers.

    Question 2) i am not authorized to download from office , i shall do it from home

    Reply
  • Vivek Grover (@GroverVivek)
    May 17, 2013 3:08 pm

    Answer to Q1:

    As SET ANSI_NULLS ON;
    is TOP statement.

    So, when Authority is compared to NULL as follows..
    i.e Authority NULL evaluates to false always…This created problem. So no result come to you.

    ‘Authority’ ‘S’ – False
    ‘Authority’ ‘Q’ – False
    ‘Authority’ ‘L’ – False
    ‘Authority’ NULL – False

    As SET ANSI_NULLS OFF will fetch result for second statement.

    SELECT ‘SQLAuthority’ AS Statement11
    WHERE ‘Authority’ IN (‘S’,’Q’, ‘L’, ‘Authority’, NULL);

    This give result because ‘Authority= ‘Authority’ which evaluates to true among

    ‘Authority’ = ‘S’ – False
    ‘Authority’ = ‘Q’ – False
    ‘Authority’ = ‘L’ – False
    ‘Authority’ = ‘Authority – True
    ‘Authority’ = NULL – False

    Answer to Q2:

    11683.84 KB

    Reply
  • B Jagan Mohan Rao
    May 17, 2013 3:24 pm

    Answer to Q1 : SET ANSI_NULLS OFF is missing
    Answer to Q2 : 11683.8 KB

    Reply
  • Answer to Q1:

    ‘Authority’ is there the list given list (‘S’,’Q’, ‘L’, ‘Authority’, NULL);

    Answer to Q2

    SQL server considers null as unknown.

    Reply
  • Answer to Q1:
    The NULL value affects the outcome of the NOT IN operator. This is because the operator compares each Value in the list; like ‘Authority’ ‘S’ and ‘Authority’ ‘Q’ and ‘Authority’ ‘L’ and ‘Authority’ NULL
    We know Null is an unknown value so ‘Authority’ NULL condition fails then there no record return. Mean time IN works fine because NULL NULL so the first statement returns value.

    Answer to Q2: 11,686.1083984375 KB

    Reply
  • Hi Pinaldave
    I think the answer is in your another article in this blog with heading “SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation”. in this you clearly mentioned the explation like “This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.”

    Reply
  • Query 1 is same as:

    select ‘SQLAuthority’ as statement1
    where ‘Authority’ = ‘S’ or ‘Authority’=’Q’ or ‘Authority’ =’L’ or ‘Authority’=’Authority’ or ‘Authority’=NULL
    WHICH IS FALSE or FALSE or FALSE or TRUE or UNKNOWN respectively
    WHICH evaluates to true and we get the result

    Query 2 is same as:

    select ‘SQLAuthority’ as statement1
    where ‘Authority’ ‘S’ and ‘Authority’ ‘Q’ and ‘Authority’ ‘L’ and ‘Authority’ Null
    WHICH IS TRUE and TRUE and TRUE and UNKNOWN respectively
    which evaluates to UNKNOWN.

    So
    1.)when ansi_nulls is ON, ‘Authority’ NULL is UNKNOWN, so the predicate evaluates to UNKNOWN and we dont get any result.
    2.)when ansi_nulls is OFF,’Authority’ NULL is TRUE, so the predicate evaluates to TRUE and we get the results.

    Reply
  • Answer to Q1: when you set ansi_nulls_on, management studio cannot compare null value to result true or false. it results to unknown. when we set ansi null off. it begins to compare null value to true or false.

    Reply
  • Aakash Patel
    May 18, 2013 3:28 am

    Answer to Q1: In ANSI_NULL, any condition compared to NULL returns false.
    Fundamental difference between IN and NOT IN is combination of conditions with OR and AND respectively. One of the condition has to be true in IN clause while all conditions have to be true in NOT IN clause to be able to return results. Since comparison condition with NULL in first query is false but comparison condition with “Authority” is true, it will return result set, while in the second query it won’t return resultset.

    Answer to Q2: 11687 KB is the size of the file.

    Reply
  • Step:1
    query1 returned result because it satisfied the where condition ‘Authority’=’Authority’
    while the query2 doesn’t, there is no match for ‘Authority’ in second query.

    Step:2
    Size of DevArt Schema Compare installation file in KB is as
    12185.6KB

    Reply
  • Tulika Agrawal
    May 20, 2013 5:12 pm

    Answer to Q1:It is because the ANSI_NULLS is ON and NOT IN operator does not work with Null values. the null value affects the outcome of the NOT IN operator.
    Query 1 is the same as:

    select ‘true’ where ‘Authority’ = ‘S’ or ‘Authority’ = ‘Q’ or ‘Authority’ =’L’ or ‘Authority’ =’Authority’ or ‘Authority’=null
    since ‘Authority’ =’Authority’ and you get a result.

    Query 2 is the same as:
    select ‘true’ where ‘Authority’ S and ‘Authority’ Q and ‘Authority’ L and ‘Authority’ null
    When ansi_nulls is on, ‘Authority’ null is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don’t get any rows.
    When ansi_nulls is off, ‘Authority’ null is true, so the predicate evaluates to true, and you get a row.

    Answer to Q2:11683.84 KB

    Reply
  • Answer to Q1: Due to Setting ANSI_NULLS ON Both queries performing different. When ANSI_NULLS is on it consider NULL as unknown and ignore result containing NULL values and when ANSI_NULLS set to OFF it consider NULL values.

    Answer to Q2:
    File Size is :- 11, 686.11 KB

    Reply
  • Answer to Q1:

    The Query1 return results because the WHERE clause matches the literal value with one of the list value included with IN predicate.
    If, the value to be compared was not exists in the list of IN predicate then it would be sure no results will appear, this is because comparision with NULL value evaluates to UNKNOWN due to SET ANSI_NULLS ON statement.

    The Query2 does not return results because of ANSI_NULLS is set to ON. This is because the comparision against NULL value evaluates to UNKNOWN.

    This is an ISO compliant behavior of comparision operators when they are used with null values.

    Answer to Q2:
    The size of the DevArt Schema Compare Installation file is 11,687 KB.

    Reply
  • Ans 1:
    query 1 : where condition is ture so result is expected whereas in
    query 2 .. where conditon is false .

    Ans 2 : 11683.84 kb

    Reply
  • Answer to Q1:

    query 1 : where condition is ture so result is expected whereas in
    query 2 .. where conditon is false .

    Answer to Q2:

    11683.84 kb

    Reply
  • Pinaldave sir .. What is the answer for the above one ?

    Reply
  • Smokefighter
    May 22, 2013 5:32 pm

    Answer to Q1: The first query will handled internally like:
    SELECT ‘SQLAuthority’ AS Statement11
    WHERE ‘Authority’ = ‘Authority’ OR …. OR ‘Authority’ = NULL …..
    So a null will not create a problem here as the first operands will either evaluate to true OR false. But the operand ‘Authority’ = null will neither evaluate to true nor false. It will evaluate to null only. So TRUE OR FALSE OR NULL is True.

    The second query will be handled as below. Since we are using an “AND” operator and anything other than true in any of the operand will not give me any output.
    SELECT ‘SQLAuthority’ AS Statement11
    WHERE ‘Authority’ ‘S’ … AND ‘Authority’ NULL …..

    ‘Authority’ S is TRUE
    ‘Authority’ Q is TRUE
    ‘Authority’ L is TRUE
    ‘Authority’ NULL is NULL
    So TRUE AND TRUE AND TRUE AND NULL –> NULL is not true so we have no output

    Answer to Q2: 11686 KB

    Reply

Leave a Reply