During my 8 years of career, I have been involved in many interviews. Quite often, I act as the  interview. If I am the interviewer, I ask many questions – from easy questions to difficult ones. When I am the interviewee, I frequently get an opportunity to ask the interviewer some questions back. Regardless of the my capacity in attending the interview, I always make it a point to ask the interviewer at least one question. Let’s learn how to fix Error 8117.
What is NULL?
It’s always fun to ask this question during interviews, because in every interview, I get a different answer. NULL is often confused with false, absence of value or infinite value. Honestly, NULL is a very interesting subject as it bases its behavior in the server settings. There are a few properties of NULL that are universal, but the knowledge about these properties is not known in a universal sense.
Let us run this simple puzzle. Run the following T-SQL script:
SELECT SUM(data) FROM (SELECT NULL AS data) t
It will return the following error:
Msg 8117, Level 16, State 1, Line 1
Operand data type NULL is invalid for sum operator.
Now the error makes it very clear that NULL is invalid for sum Operator. Frequently enough, I have shown this simple query to many folks whom I came across. I asked them if they could modify the subquery and return the result as NULL. Here is what I expected:
Even though this is a very simple looking query, so far I’ve got the correct answer from only 10% of the people to whom I have asked this question. It was common for me to receive this kind of answer – convert the NULL to some data type. However, doing so usually returns the value as 0 or the integer they passed.
SELECT SUM(data) FROM (SELECT ISNULL(NULL,0) AS data) t
I usually see many people modifying the outer query to get desired NULL results, but that is not allowed in this simple puzzle. This small puzzle made me wonder how many people have a clear understanding about NULL.
Well, here is the answer to my simple puzzle. Just CAST NULL AS INT and it will return the final result as NULL:
SELECT SUM(data) FROM (SELECT CAST(NULL AS INT) AS data) t
Now that you know the answer, don’t you think it was very simple indeed? This blog post is especially dedicated to my friend Madhivanan who has written an excellent blog post about NULL. I am confident that after reading the blog post from Madhivanan, you will have no confusion regarding NULL in the future.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
select sum(data)
from (select 1+ null as data)t
I Did it like this
SELECT SUM(Data) FROM (SELECT NULL+1 AS Data ) t
it worked..Whata play with null;sir
–Kamesh
It is because 1+null will return to NULL and it’s datatype would be INT. Refer this
Thank you Pinal for this post
Casting null as some datatype, I didn’t knew that.
unable to solve this puzzle first answer that came to my mind was this ;)
SELECT SUM(data)
FROM (SELECT 1 as data where 1=2) t
After reading the correct answer I wont use this. Thanks Sir.
Hehe :)
I had the same idea: subquery should have 0 rows, I wrote:
SELECT SUM(data)
FROM (SELECT CASE WHEN 0>1 THEN 1 END AS data) AS t
There are some good stoies from my ANSI X3H2 committtee days, The conflict is that SQL is strongly typed and NULL offically has no type. This is why we prefer CAST(NULL AS ) to get an explicit NULL in a column.
SELECT SUM(data)
FROM (SELECT cast(NULL as decimal) AS data) t
null is noting but its roll is very valuable in sql.
thanks
select sum(data)
from (select nullif(0, 0) as data) t
select (10-NULL), the result is NULL ,
Can any one explain how this happens
Any arithmetic operation done on NULL is always NULL. Read this to know more about NUL Lhttp://beyondrelational.com/modules/2/blogs/70/posts/14865/null-null-null-and-nothing-but-null.aspx