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:
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)
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)
115 Comments. Leave new
Answer to Q1:
This is because “SET ANSI_NULLS ON;” , the ANSI_NULLS is set to on. According to ).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
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. ;)
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
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
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
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.
even though in queary 2 ‘authority’ is there the result wont display.
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
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
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:
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
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
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
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
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
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.
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.
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
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.
Answer to Q2:
11684 KB
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
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