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
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
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 :)
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
https://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
https://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.
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……
Congrats chandrika and Cláudio Silva
Thanks Ram Kumar :)
Congrats to Chandrika!
You too Silva
Thank you Ram
Hi Pinal,
Thank you for the prize!
Keep the excelent work!