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

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

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.

Step 2: Identify File Size

Question: What is the size of the DevArt Schema Compare installation file in KB? Please leave a note in the comment area.

Please note the size of the file should be KB and not in MB. You can download the file from here.

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)

Rules and Conditions:

  • Contest open till May 25 12:00 GMT. 
  • Please leave your answer in the comment area in following format:
    • Answer to Q1:
    • Answer to Q2:
  • 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.

Click to Download Scripts

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

About these ads

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

  1. Answer to Q1:
    This is because “SET ANSI_NULLS ON;” , the ANSI_NULLS is set to on. According to (http://msdn.microsoft.com/en-us/library/aa259229(v=sql.80).aspx) When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN.

    Let me apply De Morgan’s laws into the query:
    SELECT ‘SQLAuthority’ AS Statement12
    WHERE ‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL);
    is now equal to
    SELECT ‘SQLAuthority’ AS Statement12
    WHERE ‘Authority’ ‘S’ AND ‘Authority’ ‘Q’ AND ‘Authority’ ‘L’ AND ‘Authority’ NULL
    so ‘Authority’ NULL result will be UNKNOW. In this WHERE query filters, UNKNOW is treat as FALSE. So, the condition filter will be FALSE. Hence, query return no result.

    Answer to Q2:11683.84 kb

  2. Question 1 answer….
    The reason the first statement returns something, is because we’re using a condition that proves to be true – therefore, we can select select statement11 and return the result set. In query 2, the statement is actually not true…the addition of the NULL with a negative statement such as NOT IN makes this statement false. Remove the “NULL” from the second query, you’d return your result

    The second question is…. 11686.108 kilobytes to be exact….

    Now, gimmie some Amazon goodies. ;)

  3. Answer to Q1:

    2nd query uses NOT IN operator and in its list of values there is NULL. NOT IN checks that ‘Authority’ is not equal to any of value from the list. It’s logically equal to this statement:
    WHERE ‘Authority’ ‘S’ AND ‘Authority’ ‘Q’
    AND ‘Authority’ ‘L’ AND ‘Authority’ NULL

    And result of ‘Authority’ NULL is unknown. So NOT IN does not return TRUE in this case.

    Answer to Q2: 11687 KB

  4. Answer to Q1: 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.
    Answer to Q2: 11,966,575

  5. Answer to Q1:
    Reason1:
    In both cases Condition = NULL becomes false because
    The ANSI and SQL-92 standard requires that an equals (=) or not equal to () comparison against a null value evaluates to FALSE.

    –See script and explanation
    SET ANSI_NULLS ON;
    – Query1
    SELECT ‘SQLAuthority’ AS Statement11
    WHERE ‘Authority’ IN (‘S’,’Q’, ‘L’, ‘Authority’, NULL);

    Explanation:
    IN Operator is equivalent to
    Condition = Value1 OR Condition = Value2 OR Condition = Value3 OR Condition = Value4 OR Condition = Value5
    So if ANY ONE OF THE ABOVE CONDITION is true, then it will be in the result

    – Query 2
    SELECT ‘SQLAuthority’ AS Statement12
    WHERE ‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL);
    Explanation:
    NOT IN Operator is equivalent to
    Condition = Value1 AND Condition = Value2 AND Condition = Value3 AND Condition = Value4
    So if ALL CONDITIONS are true, then only it will be in the result.

    Answer to Q2:
    The size of the DevArt Schema Compare installation file is 11683.84 Kilo Bytes

  6. step 1: the word ‘authority’ it is searching in the list in the provided strings to check exists or not. 1st query the word ‘authority’ is there so it displays the results and in query 2 in the list ‘authority’ is not there so it is not displays any thing. step 2 the installation size is 40140.8 kb.

  7. Step-1: Reason 1: As there is no ‘authority’ word among (‘S’,’Q’, ‘L’, NULL).
    Reason 2: Even if we place word ‘authority’ inside(), as we have written ‘not in’ command we wont be getting the result.

    Step-2: The installation size is 40140.8 kb

  8. Answer to Q1: This follows the ISO standard. All comparisons against a NULL value evaluate to unknown. For the second Query there are four comparisons to be evaluated:
    S Authority AND Q Authority AND L Authority AND NULL Authority . As Authority NULL evaluates to unknown, the WHERE – Clause evaluates to unknown. In Query 1 only one of five Comparisons must evaluate to true:
    S = Authority OR Q = Authority OR L = Authority OR NULL = Authority . As Authority = NULL OR Authority = Authority
    Therefor in Query 1 the where – Clause evaluates to true.
    Anweser to Q2: 11687 KB

  9. Step 1:
    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. (Reference: http://msdn.microsoft.com/en-us/library/aa259229(v=sql.80).aspx)

    Coming to the question, in the first query, since ‘Authority’ is part of the IN clause and IN clauses are executed as logical ORs, the WHERE clause evaluates to TRUE and you see the result.

    In the second query, since we have a NOT IN in the WHERE clause, all the operands are executed as logical ANDs and since one of the operand produces UNKNOWN, the result is UNKNOWN and the record is filtered out.

    The correct way of writing that query is
    – Query 2
    SELECT ‘SQLAuthority’ AS Statement12
    WHERE ‘Authority’ NOT IN (‘S’,’Q’, ‘L’) AND ‘Authority’ IS NOT NULL

    Step2: 11,687KB

  10. Answer to Q1: If we want result in query2 then this will be rewritten as –

    SET ANSI_NULLS OFF;
    SELECT ‘SQLAuthority’ AS Statement12
    WHERE ‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL);

    In query1 “Authority” word is found in existing string so first query return result but in query2 when there is no match found then SQL don’t compare with existing string due to “SET ANSI_NULLS ON”

    Answer to Q2: 11686.11 KB

  11. the null blocks the mechanism as shown by the fact that the following query returns no result
    select ‘found’ as result where null in (null);
    to say that a value is in the list we do not need to evaluate the null but to say that a value is not in the list it has to be compared to null which returns a null so that the sum of all the comparisons returns a null

    the installation .exe is 11687 Kb

  12. Answer to Q1:
    • Query 1 returns results because the search value ‘Authority’ is IN the given search list.
    • Query 2 returns no results because, with ANSI_NULL behaviour set, the NULL value in the search list cannot be determined to exclude the search value ‘Authority’.

    (It is not really a puzzle, it is a straight-forward technical question, albeit generally not known, because few people use the irrational results that ensue from ANSI_NULL. With ANSI_NULL cleared, Query 2 would return the serach value.)

    Answer to Q2:
    The file schemacomparesql.exe when downloaded on an Unix box is 11,687 KB or 11,996,575 bytes.

    Cheers
    Derek

  13. Answer to Q1: When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL.

    Answer to Q2: 11686 KB

  14. Hi,

    Question 1: Because of first statement, SET ANSI_NULLS ON; a SELECT with WHERE NULL (as in the second query) will return zero rows, so this is why there is no output. Changing the first statement to SET ANSI_NULLS OFF will give the answer as people might expect, the same as in query 1.

    Question 2: The file has 11,686 KB.

  15. Just a small correction to question 1: It should be NOT NULL, but I did not use the NOT keyword. Please make corrections there. Thank you.

  16. Answer to Q1:

    – Otherway of query 1

    select ‘SQLAuthority’ AS Statement11
    where ‘Authority’ = ‘S’ or ‘Authority’ = ‘Q’ or ‘Authority’ = ‘L’ or ‘Authority’ = ‘Authority’ or ‘Authority’ = NULL

    – ‘Authority’ = ‘Authority’ is a matched record that’s why it returns ‘SQLAuthority’

    – Otherway of query 2

    select ‘SQLAuthority’ AS Statement12
    where ‘Authority’ ‘S’ and ‘Authority’ ‘Q’ and ‘Authority’ ‘L’ and ‘Authority’ NULL

    – When SET ANSI_NULLS is ON and NULL value is compared in a query then it wont return any row. so because of ‘Authority’ NULL , Query 2 does not return any record

  17. Answer to Q1: Here in the first Query where condition returns true result so it will return data. When ANSI_NULL ON and if we compare
    Any value with NULL then it will return 0.

    If we write the second query as below
    SELECT ‘SQLAuthority’ AS Statement12
    WHERE ‘Authority’ NOT IN (‘S’,’Q’, ‘L’);
    then it will return the data.

    Answer to Q2: The size in KB is 11683.84 KB.

  18. Answer to Q1:

    Though the expression contains NULL value, IN will return TRUE when the value is found. That is the reason we got the result for query 1.

    NOT IN will not return any value when expression contains at least one NULL value. That is the reason we did not get the result for query 2.

    Answer to Q2:

    11687 KB

  19. Answer to Q1: Reason 1: As there is no ‘authority’ word among (‘S’,’Q’, ‘L’, NULL).
    Reason 2: Even if we place word ‘authority’ inside(), as we have written ‘not in’ command we wont be getting the result.

    Answer to Q2: the installation size is 40140.8 kb

  20. Answer to Q1: When ANSI_NULLS is on, any comparison with null returns to unknown result.

    In case of query 1 ‘where’ condition reveals to something like this

    ‘Authority’ = ‘S’ or ‘Authority’ = ‘Q’ or ‘Authority’ = ‘L’ or ‘Authority’ = ‘Authority’ or ‘Authority’ = null

    false or false or false or true or unknown returns true. That’s why we are getting ‘SQLAuthority’ in the resultset.

    In case of query 2 ‘where’ condition reveals to something like this

    ‘Authority’ ‘S’ and ‘Authority’ ‘Q’ and ‘Authority’ ‘L’ and ‘Authority’ null

    true and true and true and unknown returns false. so we are not getting resultset.

    Answer to Q2:

    1 MB = 1024 KB. file size = 11.41*1024 = 11683.84 KB

  21. Hi,

    Answer 1
    When “SET ANSI_NULLS” is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows.
    Due to this 1st Query returns values but 2nd one did not.

    Answer 2
    Size of DevArt Schema Compare installation file is 1459.2 KB.

    Thanks
    Vinay Kumar

  22. Step1:
    If we unravel the Query1 we will get:

    SELECT ‘SQLAuthority’
    WHERE ‘Authority’ = ‘S’
    OR ‘Authority’ = ‘Q’
    OR ‘Authority’ = ‘L’
    OR ‘Authority’ = ‘Authority’
    OR ‘Authority’ = NULL

    so as we have ‘Authority’ = ‘Authority’ and we have ‘or’ condition this will return results.

    However when we unravel the Query2 we can see this:
    SELECT ‘SQLAuthority’
    WHERE ‘Authority’ ‘S’
    AND ‘Authority’ ‘Q’
    AND ‘Authority’ ‘L’
    AND ‘Authority’ ‘Authority’
    AND ‘Authority’ NULL

    and with this when ANSI_NULLS in on, the comparation “‘Authority’ NULL” will return UNKNOWN so we don’t get any rows (be cause the “and” condition).
    On the other hand, when ANSI_NULLS in off, the comparation “‘Authority’ NULL” will return TRUE and together with the other comparisons we will get results.

    Step2:
    11686,1084 KB

  23. –+++++++++++++++++++++++++++++++++++++++++++++++++++++
    Answer to Q1:
    –+++++++++++++++++++++++++++++++++++++++++++++++++++++
    ANSI NULL ON/OFF:
    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.

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

    IN operator works like ANY or ALL for list
    while
    NOT IN operator will compare individualy thats why it will return FALSE

    –=====================================================
    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);

    Output:
    Statement11
    SQLAuthority

    Statement12

    –=====================================================
    SET ANSI_NULLS OFF;
    – 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);

    Output:
    Statement11
    SQLAuthority

    Statement12
    SQLAuthority

    –+++++++++++++++++++++++++++++++++++++++++++++++++++++
    Answer to Q2:
    –+++++++++++++++++++++++++++++++++++++++++++++++++++++
    size of the DevArt Schema Compare installation file in KB
    11686.1084 KB

  24. Step1:
    If we unravel the Query1 we will get:

    SELECT ‘SQLAuthority’
    WHERE ‘Authority’ = ‘S’
    OR ‘Authority’ = ‘Q’
    OR ‘Authority’ = ‘L’
    OR ‘Authority’ = ‘Authority’
    OR ‘Authority’ = NULL

    so as we have ‘Authority’ = ‘Authority’ and we have ‘or’ condition this will return results.

    However when we unravel the Query2 we can see this:
    SELECT ‘SQLAuthority’
    WHERE ‘Authority’ ‘S’
    AND ‘Authority’ ‘Q’
    AND ‘Authority’ ‘L’
    AND ‘Authority’ ‘Authority’
    AND ‘Authority’ NULL

    and with this when ANSI_NULLS in on, the comparation “‘Authority’ NULL” will return UNKNOWN so we don’t get any rows (be cause the “and” condition).
    On the other hand, when ANSI_NULLS in off, the comparation “‘Authority’ NULL” will return TRUE and together with the other comparisons we will get results.

    Step2:
    11686,1084 KB

  25. Answer to Q1:

    Since ‘Authority’ IN( ‘Authority’) is true , we get result.

    But,

    When ansi_nulls is on, ‘Authority’ NO IN (NULL) is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don’t get any rows.

    When ansi_nulls is off, ‘Authority’ NO IN (NULL) null is true, so the predicate evaluates to true, and you get a row.

    SET ANSI_NULLS OFF; is worst practice.

    Answer to Q2:

    the size of the DevArt Schema Compare installation file in KB : 11,687 KB

  26. Answer to Q1: Comparing a value with NULL results in “UNKNOWN” instead of “TRUE” or “FALSE”.

    Answer to Q2: 11687 KB

  27. Answer to Q1:
    Firstly lets look into the “SET ANSI_NULLS ON/OFF” to answer the question.
    When its set to “ON”, the query comparison wrt NULL will return “0″. On the other hand; when its set to “OFF” the query comparison wrt NULL will return a “NULL” value.
    Hence the first query is satisfied with the “WHERE” condition and hence output is displayed. Whereas, in second query “WHERE” condition is satisfied wrt to the “NULL” but since ANSI_NULL is set to ON.. it does not return any result. When the ANSI_NULL is set to “OFF” the where condition is satisfied during null comparison.
    Answer to Q2:
    The size of the DevArt Schema Compare installation file is
    Size : 11686.108398 KB
    Size on disk : 11688 KB

  28. Answer to Q1: When ANSI_NULLS is ON – as SQL-92 standard states – a not equal to (and an equal to) comparison against a NULL value must return FALSE. SQL Server will not return any rows with this compatibility setting on even when there’s a non-null value in the statement.
    Answer to Q2: 11688 kB

  29. Question: Why do Query 1 return results but Query 2 does not return any result?
    Answer to Q1: Query 1 return results because the value ‘Authority’ does exist in available options (‘S’,’Q’, ‘L’, ‘Authority’, NULL) and it matches with ‘IN’ operator.

    Query2 won’t return anything as the value ‘Authority’ doesn’t exist in available options (‘S’,’Q’, ‘L’, NULL), irrespective of ‘NOT IN’ operator which is really irrelevant here and doesn’t play any role.

    Answer to Q2:: Filesize is – 11687 KB

  30. The first observation I make is that putting off to the parameters ANSI_NULL is that both queries bring a row.

    Answer to Q1

    The first query is like this :

    SET ANSI_NULLS ON ;
    SELECT ‘SQLAutorithy’ AS Statement11
    WHERE ‘Authority’ = ‘S’
    OR ‘Authority’ = ‘Q’
    OR ‘Authority’ = ‘L’
    OR ‘Authority’ = ‘Authority’
    OR ‘Authority’ IS NULL

    So, I got a value because ‘Authority’ = ‘Authority’ is true

    Answer to Q2

    The second query can be write like this :
    SET ANSI_NULLS ON ;
    SELECT ‘SQLAutorithy’ AS Statement12
    WHERE ‘Authority’ ‘S’
    AND ‘Authority’ ‘Q’
    AND ‘Authority’ ‘L’
    AND ‘Authority’ NULL

    When ANSI_NULLS is ON, ‘Authority’ NULL is unknown, so an evaluation to unknown don’t bring any rows.

    When ANSI_NULLS is OFF, ‘Authority’ NULL is true and SQL bring a row.

    I notice that SET ANSI_NULLS OFF is a deprecated feature (see the link below), so I don’t recommend to use it.

    http://msdn.microsoft.com/en-us/library/ms143729.aspx

  31. Step 1 : ANSWER
    WHEN SET ANSI_NULLS ON, comparison in which any expression contains NULL doesn’t produces any return. It will be always produce UNKNOWN.

  32. Answer to Q1: Because in second query ‘Authority’ string is not available in group of string (‘S’,’Q’, ‘L’, NULL)

    Answer to Q2: 11686.108 KB

  33. Question 1
    SET ANSI_NULL ON is the default configurations of the sql server. When you have set ANSI_NULL to ON you cant equate values to NULL. If you Set ANSI_NULL to ON you will get the same result as statement 1.

    Question 2

    Size of the file is 11686.1 kb

  34. 1.
    When ansi_nulls is on , sql server can’t say if something is not in a subset that containts NULL . it;s evaluated to Unknown , so it’s not True.

    2.11683.84 KB

  35. Answer to Q1:

    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.

    So When it is off .. It will return results for both query 1 and 2.

    SET ANSI_NULLS OFF;
    – 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);

  36. Hi.
    When ANSI_NULLS is ON then ‘Authority’ null is UNKNOWN, because this expression become ‘Authority’ ‘S’ AND ‘Authority’ ‘Q’ AND ‘Authority’ ‘L’ AND ‘Authority’ NULL , so the predicate evaluates to UNKNOWN, and you don’t get any rows.

  37. Hi.
    When ANSI_NULLS is ON then ‘Authority’ != null is UNKNOWN, because this expression become ‘Authority’ !=‘S’ AND ‘Authority’!= ‘Q’ AND ‘Authority’ !=‘L’ AND ‘Authority’ != NULL , so the predicate evaluates to UNKNOWN, and you don’t get any rows.

  38. Ans1: When ANSI_NULLS is on, any comparison with null
    returns to unknown result.In case of query 1 ‘where’ condition
    reveals to something like this‘Authority’ = ‘S’ or ‘Authority’ = ‘Q’ or ‘Authority’ = ‘L’ or ‘Authority’ = ‘Authority’ or ‘Authority’ = null false or false or false or
    true or unknown returns true. That’s why we are getting ‘SQLAuthority’ in the resultset.In case of query 2 ‘where’ condition reveals to something like this‘Authority’ ‘S’ and ‘Authority’ ‘Q’ and ‘Authority’ ‘L’ and ‘Authority’ nulltrue
    and true and true and unknown returns false. so we are not getting resultset.
    Ans2: 11.41*1024 = 11683.84 KB

  39. Answer to Q1: By definition: When ANSI_NULLS is on, any comparison operation where (at least) one of the operands is NULL produces the third logic value – UNKNOWN (as opposed to TRUE and FALSE). UNKNOWN value causes the row to be filtered out. And in case of Query 2, the result of the where clause is neither True nor False and so the row is filtered out.

    Answer to Q2: File size is – 11686.108 Kb

  40. Answer to Q1:
    Due to the specification of ansi nulls, they are supposed to be treated as UNKNOWN values and thus evaluate to False in terms of the ‘=’ and ” operators. The NOT IN/IN clauses evaluate these operators and thus will always return FALSE and not provide rows. Since a null value exists in the set, it is “Unknown” if Authority exists and in turn returns false.

    Answer to Q2:
    11,686.11 KB due to the fact that there are 1024 Bytes in a KB (1000 Bytes in a kB)

  41. Answer 1: It has to do with the NULL value. When a NULL value is returned by the IN subquery it is compared against the “test expression” but using a IN/NOT IN can return “UNKNOWN” which can cause unwanted behavior. It is best practice not to use NULL values with IN/NOT IN statements.

    Answer 2: 11,687KB

  42. Answer to Q1:
    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. So for Query 1 we have NULL value in where clause will return zero but “Authority” will match condition, hence we are getting “SQLAuthority” as result.

    A SELECT statement using WHERE column_name NULL returns zero rows even if there are nonnull values in column_name, which is why Query 2 gives empty result. If we change our code like below, we will get result.
    – Query 2
    SET ANSI_NULLS OFF;
    SELECT ‘SQLAuthority’ AS Statement12
    WHERE ‘Authority’ NOT IN (‘S’,’Q’, ‘L’,NULL);
    GO

    Answer to Q2:
    11,687 KB

  43. ANSWER TO the QUESTION1

    Query1

    Authority IN (‘S’,’Q’,’L’,’Authority’,NULL)

    is translated as follows

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

    Comment:
    So any one value matched the Query1 returns the result string ‘SQLAuthority’

    Query2

    Authority NOT IN (‘S’,’Q’,’L’,NULL)

    is translated as follows

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

    Comment:

    in the above Authority NULL returns NULL and since AND is used to
    combine all the conditions “Authority NULL ” causes the statement to return neither true nor false. hence the where condition fails and the query 2 returns empty
    ———————————————————-

    ANSWER TO the QUESTION2

    Size of schemacomparesql.exe
    Size
    11687 KB

  44. Answer to Q1: The statement SET ANSI_NULLS ON, specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to () comparison operators when used with null values. The SQL-92 standard requires that an equals (=) or not equal to () comparison against a null value evaluates to FALSE. So the statement ‘Authority’ NOT IN (NULL) is evaluated to FALSE.

    Answer to Q2: 11412 0 Kb = 11412 KB = 11.412 MB
    But there is a little fault in the source page. Instead 11.41MB, it indicates 11.41Mb !!!!

  45. Step 1: Answer the Question

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

    Answer: The second query doesn’t return any result because the ‘Authority’ evaluates to NULL.
    NULL doesn’t equal to anything and NULL is just NULL. When the subquery compares between ‘Authority’ and NULL,
    the query result returns NULL.

    Step 2: Identify File Size

    Question: What is the size of the DevArt Schema Compare installation file in KB?
    The Devart Schema Compare installation file in KB is 11683.84

  46. It’s the inner workings of NOT IN vs NOT EXISTS

    SELECT ‘SQLAuthority’ AS Statement12
    WHERE NOT EXISTS ( SELECT ‘test’ WHERE ‘Authority’ IN (‘S’,’Q’, ‘L’, NULL));

  47. When ansi_nulls is set to ‘on’, comparing a value to a NULL returns neither a true nor a false result. Query 1 returns a result because the comparison ‘Authority’ = ‘Authority’ returns true, which is enough to make the in statement true even though the comparison ‘Authority’ = NULL is neither true nor false. Query 2 does not return a result because ‘Authority’ NULL is neither true nor false, so the ‘not in’ statement can’t determine if ‘Authority’ is not in the array of values.

  48. Answer to Q1: Since Null is not a member of any data domain, it is not considered a “value”, but rather a marker (or placeholder) indicating the absence of value. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown.

    Answer to Q2: 285 KB

  49. ANSI_NULLS ON follows ANSI-92 standards whereby queries returning NULL values evaluates to FALSE and returns 0 rows.

  50. Answer to Q1: For the first statement, it finds a match for the IN statement (‘Authority’ = ‘Authority’), so then it returns the results given.

    Now for the 2nd statement, when it’s running through the NOT IN operator and it gets to the comparison ‘Authority’ NULL, that type of operation is unknown, so this leads to the statement returning no results.

    Answer to Q2: 11,687KB

  51. Answer to Q1:

    When ANSI_NULLS is set to ON comparisons of values with null returns 0 when OFF it returns null.

    So,

    on Query 1 when check if ‘Authority’ is in:
    ‘S ‘ = false
    ‘Q’ = false
    ‘L’ = false
    ‘Authority’ = true
    NULL = 0 (aka false)

    ‘Authority’ is found and hence shows SQLAuthority on resultset.

    On Query 2 when check if ‘Authority’ is not in:
    ‘S ‘ = true
    ‘Q’ = true
    ‘L’ = true
    NULL = 0 (aka false)

    Even ‘Authority’ is not found on list, ANSI_NULLS is ON making the comparison between ‘Authority’ and NULL equal to 0 (zero)

    Making the whole comparison FALSE hence not showing SQLAuthority on resultset.

    Answer to Q2:
    11673.6 KB

  52. Answer to Q1:
    The IN is synonymous to an OR operator, therefore only 1 comparison must return a TRUE result for the query to display the text ‘SQLAuthority’.
    With ANSI_NULLS ON any comparison to a NULL returns UNKNOWN. UNKNOWN values propagate through boolean operators (AND, OR) or (NOT). The value of the WHERE clause must be TRUE for a row to not be filtered out.

    Let’s review each comparison and the result.
    ‘Authority’ = ‘S’ RESULT = FALSE
    OR
    ‘Authority’ = ‘Q’ RESULT = FALSE
    OR
    ‘Authority’ = ‘L’ RESULT = FALSE
    OR
    ‘Authority’ = ‘Authority’ RESULT = TRUE
    OR
    ‘Authority’ = NULL RESULT = UNKNOWN

    Answer to Q2:
    The NOT IN is synonymous to an AND operator, therefore all comparisons must return a TRUE result for the query to display the text ‘SQLAuthority’.
    With ANSI_NULLS ON any comparison to a NULL returns UNKNOWN. UNKNOWN values propagate through boolean operators (AND, OR) or (NOT). The value of the WHERE clause must be TRUE for a row to not be filtered out.

    Let’s review each comparison and the result.
    ‘Authority’ ‘S’ RESULT = TRUE
    AND
    ‘Authority’ ‘Q’ RESULT = TRUE
    AND
    ‘Authority’ ‘L’ RESULT = TRUE
    AND
    ‘Authority’ NULL RESULT = UNKNOWN

    The size of the DevArt Schema Compare installation file in KB is 11,687

  53. Q1: I believe this is because the former uses short circuit logic, so once a “hit” is achieved it returns success. In the latter it attempts a string to NULL comparison which is neither true nor false (because ANSI_NULLs are off) and so there is no result.

    Q2: 11687KB

  54. Hi Pinal,

    1.)
    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.

    2.)
    11687 KB is the size of the file.

    BTW, I am impressed with your affiliate marketing skills. This is something innovative.

    Thanks,
    Aakash Patel

  55. As mentioned in msdn, “Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.”

    “IN determines if a given value matches any value in a subquery or a list”

    First query returns value since IN clause matches at fourth parameter of in clause

    While in case of second query we get no result, as NOT IN is just the negation applied to IN operator’s result and IN operation in this case returns UNKNOWN, resulting in NEGATION of UNKNOWN which evaluates to UNKNOWN.

  56. Hi Pinal ,

    First Question :

    One important we need to note here is comparison of NULL with anything is NULL. This is the thing that happens in NOT IN clause having NULL . When there is a NULL value in NOT IN clause , the NOT IN clause evaluates the expression by comparing all the values mentioned in NOT IN with the column that is used for comparison. When there is a NULL in list of comparisons the entire NOT IN clause evaluates to NULL as comparison of NULL with anything is NULL, there would be no output display .
    To eliminate that we need to LEFT OUTER JOIN as

    select sub.Authority
    From
    (select ‘SQLAuthority’ AS Authority) Sub
    Left outer join
    (select ‘S’ as Authority
    union
    select ‘Q’ as Authority
    union
    select ‘L’ as Authority
    union
    select NULL as Authority
    ) sub1
    on sub.Authority = sub1.Authority
    where sub1.Authority is NULL

    Second Question :

    The file size of DevArt SchemaComparer file in KB is 11687 KB

    Rajesh Nedunuri.

  57. Query 1 can also be considered as
    SELECT ‘SQLAuthority’ AS Statement1
    where ‘Authority’ = ‘S’
    OR ‘Authority’ = ‘Q’
    OR ‘Authority’ = ‘L’
    OR ‘Authority’ = ‘Authority’
    OR ‘Authority’ = NULL

    When ANSI_NULLS is so et off ‘SQLAuthority’ NULL is true the predicate ‘Authority’ = ‘Authority’ evaluates to true we get the resulted row for Query 1

    Query 2 is the same as:

    SELECT ‘SQLAuthority’ AS Statement2
    where ‘Authority’ ‘S’
    And ‘Authority’ ‘Q’
    And ‘Authority’ ‘L’
    And ‘Authority’ NULL

    When ANSI_NULLS is set as on ‘SQLAuthority’ NULL is UNKNOWN thus predicate evaluates to UNKNOWN and we don’t get any row query 2

  58. Answer to Q1:

    The SELECT ‘SQLAuthority’ AS Statement11
    WHERE ‘Authority’ IN (‘S’,’Q’, ‘L’, ‘Authority’, NULL);
    returns a result set because of the matching keyword authority in the IN clause.

    The Second Select statement does not return any result because while comparing Authority to NULL, it returns a unknown state which cause no results to be returned.
    In case NULL were removed from the IN clause you would get a result.

    Answer to Q2:
    The size of file in KB is 11683.8 KB.

    Ramdas

  59. Answer to Q1:On the first one it returns ” Authority” because it is an element/member of in (),
    On the second case we only have four possible out puts , which are :
    ‘S’,’Q’, ‘L’ and NULL(NULL is the result of when empty string is compared instead of ‘Authority’, as a result we don’t expect another NULL out put , since every NULL is unique , NULL NULL)

    Answer to Q2: 11683.84KB

  60. after where clause the mentioned string consider as column as well act as a data to compares with IN clause data.so it will disply the result but in second qustion the compared string compare with not in so it wont display the result

  61. Answer :1
    when we set
    ANSI_NULLS to OFF, we could
    compare a NULL value by using
    the comparison operators like =,.by default the ANSI_NULLS is ON which makes us to use only IS NULL and IS NOT NULL.it gives the diff of implicit and explicit NULL.
    Since their is a NULL in not in condition the characters are compared with NULL and doesnt satisfy and ANSI_NULLS is ON so the result cant be NULL so oly empty result.
    Answer :2
    11410 KB

  62. Answer to Q1:
    When ANSI_NULLS is ON, the query SELECT ‘SQLAuthority’ AS Statement12 WHERE ‘Authority’ NOT IN (‘S’.’Q’,’L’,NULL) will return no result because when the above statement is expanded to the form of its logical meaning it will be
    SELECT ‘SQLAuthority’ AS Statement12
    WHERE ‘Authority’ ‘S’
    AND ‘Authority’ ‘Q’
    AND ‘Authority’ ‘L’
    AND ‘Authority’ NULL

    Here, the last condition ‘Authority’ NULL is a ambiguous statement which returns neither true nor false. Hence, the NOT IN condition will not return a true result. This is the reason why no result is displayed for the second query. If you SET ANSI_NULLS OFF or remove NULL from the NOT IN condition the query returns the result.

    Answer to Q2: 11,687 KB.

  63. Because when ANSI_NULLs is on then (‘Authority’ != NULL) will return “FALSE”
    while when ANSI_NULLS is off then (‘Authority’ = NULL) will return “TRUE”

    so in your 1st query irrespective of ANSI_NULLS on or off it’ll return ‘SQLAuthority’
    because of the availability of ‘Authority’ in your where clause along with other options (‘S’,’Q’, ‘L’, ‘Authority’, NULL);

    In your 2nd query when
    2a. ANSI_NULLS ON
    then the criteria (‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL) ) will become false so no result
    2b. ANSI_NULLS OFF
    then your criteria will become true (it’ll be something like “‘Authority’ != NULL “)
    hence return the ‘SQLAuthority’

    Installation file size in kB : 11687

  64. Answer to Q1 : while checking where condition in first case the parameters in bracket are user defined parameters and not from fixed table,so there could be infinite parameters but since ‘authority’ is matching with ‘authority’ therefore it is displaying the output but similarly in second statement ‘authority’
    is not in the bracket and infinite parameter could be there therefore it is displaying nothing.

    Answer to Q2: 11687 KB

  65. Answer to Q1: “IN” acts as “OR” hence the condition evaluates to true in statement11 while “NOT IN” acts as “AND” so the condition will always evaluate to false and not return any rows
    Answer to Q2: 11.41MB=11410KB

  66. 1. WHERE ‘Authority’ is bevaing as single value column. If it value matches with the values in the “In” list then slected string is returned else empty result set is returned.
    following 2 queries will also not be returning any result because of above reason
    —————————————————————
    SELECT ‘SQLAuthority’ AS Statement12
    WHERE ‘Authority’ not IN (‘S’,’Q’, ‘L’, ‘Authority’, NULL);
    —————————————————————-
    SELECT ‘SQLAuthority’ AS Statement12
    WHERE ‘Authority’ IN (‘S’,’Q’, ‘L’, NULL);

    2. DevArt Schema Compare installation file size 11684 kb

  67. Ans 1: A null is neither equal or unequal to anything, therefore when we ask if ‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL), the database can’t say that’s strictly true.

    Ans 2: 11688

  68. Answer to Q1: In the first case the ‘null’ is after the ‘authority’ and the comparison is not happening with ‘null’ so result is coming, in the second case since it is ‘not in’ condition the comparison is happening with ‘null’ as well and nothing is equal to ‘null’ and condition is failing, so no record is returned.
    Answer to Q2: size in KB is 11686.1084 KB

  69. Q1. query one returns because the ANSI NULL comparisons. When it is on the query will compare the value with a NULL returns a 0. Since the first query is IN (=) the NULL it can return but the second query has the NOT IN (!=)and even with the NULL it will return the 0

    Q2. 11.41 * 1024 = 11,683.8

  70. The reason is because when ANSI_NULLS is ON, the comparision of the string ‘Authority’ with NULL will always returns zero rows.

  71. Answer to Q1:

    if SET ANSI_NULLS is On then a query with where clause evaluates to FALSE having an equals (=) or not equal to () comparison against a null value.

    Hence, second query returns null because of ansi_nulls set to on.
    First query checks the ‘authority’ word which comes in list, If it compares with equals to null then it will also returns false.

    Answer to Q2:
    Size of installation file: 11,687 KB

  72. 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);

  73. 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

  74. 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

  75. 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

  76. 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

  77. Answer to Q1:

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

    Answer to Q2

    SQL server considers null as unknown.

  78. 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

  79. 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.”

  80. 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.

  81. 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.

  82. 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.

  83. 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

  84. 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

  85. 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

  86. 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.

  87. 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

  88. 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

  89. 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

  90. When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name NULL returns zero rows even if there are nonnull values in column_name

  91. 1. Query1 evaluates { where ‘Authority’ = ‘S’ OR ‘Authority’ = ‘Q’ OR ‘Authority’ = ‘L’ OR ‘Authority’ = ‘Authority’ } Since the last condition is true it returns true.

    Query2 evaluates { where ‘Authority’ ‘S’ OR ‘Authority’ ‘Q’ OR ‘Authority’ ‘L’ OR ‘Authority’ null } Since ansi_nulls is turned on ‘Authority’ null is unknown, so it evaluates to unknown and you get zero rows.

    With Ansi_nulls on all comparisons against a null value evaluate to Unknown.

    2. 11686.108398 Kilobyte

    This was a fun exercise. Thanks for posting it :)

    • Looks like open/close brackets were taken out.

      1. Query1 evaluates { where ‘Authority’ = ‘S’ OR ‘Authority’ = ‘Q’ OR ‘Authority’ = ‘L’ OR ‘Authority’ = ‘Authority’ } Since the last condition is true it returns true.

      Query2 evaluates { where ‘Authority’ != ‘S’ OR ‘Authority’ != ‘Q’ OR ‘Authority’ != ‘L’ OR ‘Authority’ != null } Since ansi_nulls is turned on ‘Authority’ != null is unknown, so it evaluates to unknown and you get zero rows.

      With Ansi_nulls on all comparisons against a null value evaluate to Unknown.

      2. 11686.108398 Kilobyte

      This was a fun exercise. Thanks for posting it :)

  92. Hi All,

    The winners have been chosen based on the randomly from the correct answers.

    Follwing winners will each get USD 25 Amazon Cards
    Chandrika
    http://blog.sqlauthority.com/2013/05/15/sql-server-sql-puzzle-of-set-ansi_null-win-usd-250-worth-amazon-gift-cards-and-bubble-copter-rc/#comment-475512

    Cláudio Silva
    http://blog.sqlauthority.com/2013/05/15/sql-server-sql-puzzle-of-set-ansi_null-win-usd-250-worth-amazon-gift-cards-and-bubble-copter-rc/#comment-475652

    The winner of the RC Chopper has been informed using email. The name of the winner will be announced here once he/she is validated for US Residency.

    Winners please contact me using email at pinal at sqlauthority.com to claim your gifts.

  93. Hi Pinal,

    Thank you so much. You know I’m very excited to get this prize….
    Really glad to be of SQLAUTHORITY reader and participator……

  94. Pingback: SQL SERVER- Solution – SQL Puzzle of SET ANSI_NULL | Journey to SQL Authority with Pinal Dave

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