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: 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
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
Null is the culprit
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
The editor removes de ” :(
The editor removes the “less then” and “greater” symbols.
Sorry the spam
–+++++++++++++++++++++++++++++++++++++++++++++++++++++
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
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
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
Answer to Q1: Comparing a value with NULL results in “UNKNOWN” instead of “TRUE” or “FALSE”.
Answer to Q2: 11687 KB
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
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
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
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.
https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-2017
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.
I have forgost to response step 2.
The file size is 11 687 Ko
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
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
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
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);
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.
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.