Nulls

Question

Nulls

Viewing 4 posts - 46 through 48 (of 48 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 1

mtillman-921105 (12/15/2010)
The opposite could also be asked... Why do you have missing data?

Because incomplete data is a fact of life.

Companies that only do business when all data their database has a column for is known, will soon not do business anymore. Many fields are mandatory, but not all are.

If a company collects data about household situation and income of their customers, for statistical analysis and maybe some data mining and targeted advertising, and I leave the fields for number of children and monthly household income blank, would you really enter both as 0? I do have children. And I do have income. I just refuse to share that information with companies that have no need for this information, and no legal basis for asking me about it.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 2

Hugo Kornelis (12/15/2010)
mtillman-921105 (12/15/2010)
The opposite could also be asked... Why do you have missing data?
Because incomplete data is a fact of life.
Companies that only do business when all data their database has a column for is known, will soon not do business anymore. Many fields are mandatory, but not all are.
If a company collects data about household situation and income of their customers, for statistical analysis and maybe some data mining and targeted advertising, and I leave the fields for number of children and monthly household income blank, would you really enter both as 0? I do have children. And I do have income. I just refuse to share that information with companies that have no need for this information, and no legal basis for asking me about it.

Fair enough points and I agree.

I also have a story about how I thought that NULLs would help, but in the end didn't matter. This is oversimplifying, but we had multiple managers responsible for multiple totals. I was resposible for loading the SQL tables and made sure that NULLs were entered if any of the manager's totals were missing. (I was proud of my error-catching design.) Then one day one or two of the managers couldn't provide their information. Upper management's solution? Ignore them and proceed anyway. So the code ended up changing the NULLs to zeros in the end.

Now I know that this doesn't really prove anything in a way since at times NULLs are helpful. But it does show that at times, NULLs are nothing but an obstacle.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

SQLZealot Valued Member Points: 63 More actions · Answer 3

Thanks for the details, it has so much more meaning now!

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 4

vk-kirov (12/8/2010)
Good question.
But this is not an explanation at all:
Explanation: If a subquery returns any null value the main query will not return any rows
I suggest the following explanation.
The SELECT query can be modified as follows:
select *
from dbo.Employees
where EmployeeID not in (SELECT NullableColumn FROM NullOperation)
-->
select *
from dbo.Employees
where EmployeeID not in (NULL, 0, 1)
-->
select *
from dbo.Employees
where (EmployeeID <> NULL) and (EmployeeID <> 0) and (EmployeeID <> 1)
The result of the comparison "EmployeeID <> NULL" is undefined for all values of EmployeeID (unless you set the ANSI_NULLS option to OFF). So the result of the WHERE clause is undefined (i.e. not true), and no rows are returned.

[font="Verdana"]

or even this way as well...

select * from dbo.Employees where EmployeeID not in

(SELECT NullableColumn FROM NullOperation where NullableColumn is not Null)

go[/font]

MH-09-AM-8694