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
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
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
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)
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
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
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
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 !!!!
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
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));
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.
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
ANSI_NULLS ON follows ANSI-92 standards whereby queries returning NULL values evaluates to FALSE and returns 0 rows.
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
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
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
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
err…because they are on, ANSI_NULLs are ON.
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
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.
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.
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