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:
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
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
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
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
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.
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
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
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
Answer to Q1:
That empty result is because of the ‘NULL’ used in where clause.
Answer to Q2:
11683.84KB
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
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
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
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
Answer to Q1: ANSI-standard comparisons to null are neither true nor false but unknown
Answer to Q2: 11687kB crTU59l$!!2SEzIi
Agreed, I was just correcting where I had “(because ANSI_NULLs are off)” in the original post.
The reason is because when ANSI_NULLS is ON, the comparision of the string ‘Authority’ with NULL will always returns zero rows.
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
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);
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
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