| Author |
 Topic  |
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
 Posted - 03/22/2004 : 18:00:31
|
>> Logic mistake - data should be represented in tables doesn't mean that everything in tables represents data
It does if that table wants to conform to the requirements for an R-table..
>> >> UNIQUE constraints should fail on NULLs in SQL, but do not
>>Don't see how you can say that. SQL server allows one null value - other database allow many - maybe some allow none.
Because a unique constraint in R-tables only works on values, and since null is not a value should be impossible. But he actually said SQL and since SQL supports null maybe they should be allowed in a SQL unique constraint.
As you say though, documentation of their use and intended meaning is vital if you are going to use them. Personally, I have found the amount of extra work to cater for them in a enterprise environment (back-end, middle-tier, front-end, OLAP and other reporting), coupled with an increased testing cycle, make them a really expensive item that would be best avoided at (just about) all cost..
DavidM
"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. " |
 |
|
|
mark1504
Posting Yak Master
United Kingdom
103 Posts |
Posted - 03/22/2004 : 18:14:37
|
One thing I particularly like about this site is how the merest mention of beer brings a flurry of posts :).
I'm trying to thing of an on-topic comment about NULL but I've drawn a blank, or should that be a NULL! (It must be the beer!) |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 03/22/2004 : 19:24:04
|
If vendors were just consistent on how NULLs are used things might be different, but there are so many inconsitencies that NULLs are just a pain. Especially in hetergenous environments and/or multi-tier environments like DavidM mentioned.
Here are a couple of examples from the Practical Issues in DB Management book I linked above.
1. Aggregate functions (SUM(), AVG(), etc.) ignore NULLs except for COUNT().
2. the expression "x IS NOT NULL" is not equal to "NOT(x IS NULL)"
3. SQL's NOT doesn't mean the same thing as "not" in natural language.
for example:
SELECT COUNT(*)
FROM customer;
COUNT(*)
--------
154168
SELECT COUNT(*)
FROM customer
WHERE salutation = 'mr';
COUNT(*)
--------
4338
SELECT COUNT(*)
FROM customer
WHERE salutation <> 'mr';
COUNT(*)
--------
6269
The sum of the last 2 queries does not equal the first.
-ec
Maybe we should move on to some other topic, like the pros/cons of using surrogate keys..
;)
|
Edited by - eyechart on 03/22/2004 19:31:05 |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 03/22/2004 : 19:38:19
|
I feel like I have a giant NULL where I missed this thread.
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 03/22/2004 : 21:10:40
|
quote:
Originally posted by eyechart
Maybe we should move on to some other topic, like the pros/cons of using surrogate keys..
OK If you like, but understand whats happening here...
You can't reference a value that doesn't exists...
That's why your results do that...
You can not evaluate null
because it's not there, even if the row is
Yes?
Brett
8-) |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 03/22/2004 : 21:28:40
|
>> >> Logic mistake - data should be represented in tables doesn't mean that everything in tables represents data
>> It does if that table wants to conform to the requirements for an R-table..
I was pointing out the erroneous logic leap made from a all data represented by table values to all data in tables representing data.
There may be definitions that preclude values being null but that isn't one of them.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 03/22/2004 : 21:31:09
|
Of course you can evaluate NULL. You can evaluate any piece of data that resides in SQL Server. NULL is a value. It's a value that represents an unknown value; however, it is still a value.
SELECT COUNT(*)
FROM customer
SELECT COUNT(*)
FROM customer
WHERE customer IS NOT NULL
SELECT COUNT(*)
FROM customer
WHERE customer IS NULL
Statement 2 + 3 = 1
--Therefore NULL has been evaluated.
SELECT COUNT(*)
FROM customer
WHERE salutation = 'mr'
COUNT(*)
--------
4338
SELECT COUNT(*)
FROM customer
WHERE salutation <> 'mr'
COUNT(*)
--------
6269
SELECT COUNT(*)
FROM customer
WHERE salutation IS NULL
Count of last three will equal total
--Therefore NULL has been evaluated.
We have now not only referenced a value that "doesn't exist". We have defined the scope of records that have an unknown value in the salutation column. This in itself can provide statistical value on the quality of our data and the metrics that drive our business.
Any data, including the unknown NULL, can be evaluated in SQL Server. Not understanding or abusing it is easy and dangerous though.
I seem to be on a theme tonight.
Don't you just want to kill me.
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 03/22/2004 : 21:40:26
|
quote:
Originally posted by derrickleggettDon't you just want to kill me.
Nah...I love a good bar fight....hockey player and all...
but you're not evaluating (null), your evaluating if the value exists or not...
that's different....
and I'm trying to do this in a multi-margarita-fog......
I'll have to re=read this AM
zzzzzzzzzzz
Brett
8-) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 03/22/2004 : 21:46:58
|
but you're not evaluating (null), your evaluating if the value exists or not...
Actually, I've evaluated (null) and the fact that the value exists or not. Reread and think about it.
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA. |
|
|
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
Posted - 03/22/2004 : 22:01:41
|
I think you have "catered" for null is about all you can say...
It is telling that you cannot use the equality operator ("=") for null expressions but must use the IS keyword..
Not all operatorions treat null like values...
create table TestNull(Data varchar(50) null )
go
insert TestNull
Select '1'
union all
select null
go
--NULL treated like value
select distinct data
from testnull
go
--Null not treated like a value
select data, Count(data)
from testnull
group by data
GO
drop table testnull
DavidM
"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. " |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 03/22/2004 : 22:13:16
|
If understanding how my language works is catering then so be it. Null is still a value that can be evaluated, analyzed, and abused, just like any other value.
create table TestNull(Data varchar(50) null )
go
insert TestNull
Select '1'
union all
select null
go
--NULL treated like value
select distinct data
from testnull
go
--Null not treated like a value
select data, Count(ISNULL(data,1))
from testnull
group by data
GO
drop table testnull
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA. |
|
|
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
Posted - 03/22/2004 : 22:49:18
|
>>> Null is still a value that can be evaluated, analyzed, and abused, just like any other value.
I'd like to know how you can abuse the value 1 or 'Dingo'...
A value requires a type.. null does not have a type...
A specific question..
For any string column, why would you choose a NULL over a empty string?
DavidM
"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. " |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 03/22/2004 : 22:52:18
|
One lead vendor didn't provide the data at all. The other company did with an empty string for when it wasn't filled out. You paid $30 more per lead for the ones with the value. The profit on the leads with the string filled was %50 more valuable then without. But the first two times they sent you leads, they didn't send any. Should you take leads from them without the value? How do you know who sent and who didn't? Is empty known if they never sent it or unknown? Gee, I don't know. You tell me.
If you control what constitutes a NULL on the lead table you increase the value of the leads and what analysis you can do on the leads. NULL is not received. '' is empty. value is value. Make sense?
NULL is a value of unknown. That value = $$$.
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA. |
Edited by - derrickleggett on 03/22/2004 23:00:13 |
|
|
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
Posted - 03/22/2004 : 23:54:48
|
I am struggling to understand what you just wrote..
So one vendor sends you a null and doesn't get paid (because null isn't a value?), while the other sends you an empty string and gets paid?
>>if you control what constitutes a NULL on the lead table you increase the value of the leads and what analysis you can do on the leads. NULL is not received. '' is empty. value is value. Make sense?
Partially.. but haven't you been arguing that null is a value? (value is value is null) What is stopping you from using empty string as the "rejection" value? And why choose one over the other (my original concern)?
DavidM
"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. " |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 03/23/2004 : 00:58:29
|
Because null being an unknown is a value when you are analyzing data. An empty string just means you got back empty data. Null means it didn't exist in the first place. Why would I put an empty string in there if I didn't get anything in the first place????
The value isn't empty. Empty is a value. I got nothing back. Zilch. They never tried to send me anything. I didn't set it to blank. The status of that field as it relates to the entity is completely unknown. Why would I throw something in to complicate the issue? They didn't send me a blank, they sent me nothing.
Am I going to make my numbers 0 since I'm making my strings empty btw??? Is that the numeric equivalent of an empty string? It isn't if you're an accountant. It's a very significant number. So do I have to define a rule for each datatype? No, I just need to know that NULL means I didn't receive anything and the value is unknown.
In SQL Server I have to then know how to handle NULL. There's no way around it. Learn how to handle NULL because it's a reality in SQL Server.
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 03/23/2004 : 02:17:00
|
NULL is not a value. The point of my last post was to illustrate that most databases erroneously evaluate NULL as a value some of the time.
Like with the aggregate functions. Why doesn't AVG() evaluate NULLs? Why does COUNT()? it is inconsistent.
-ec |
Edited by - eyechart on 03/23/2004 02:18:48 |
|
|
|
mohdowais
Sheikh of Yak Knowledge
United Arab Emirates
1456 Posts |
Posted - 03/23/2004 : 02:21:55
|
quote:
Am I going to make my numbers 0 since I'm making my strings empty btw??? Is that the numeric equivalent of an empty string? It isn't if you're an accountant. It's a very significant number. So do I have to define a rule for each datatype? No, I just need to know that NULL means I didn't receive anything and the value is unknown.
Excellent point. If you never use NULLs how do you cater for living persons in a table that has both DateofBirth and DateofDeath, for example. If the person is not yet dead, what value do you put in the DateOfDeath column? A default value 100 years into the future? Defaults have their place, but they are no substitute for NULLs.
Just my 2 fils. 
OS |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 03/23/2004 : 02:29:27
|
quote:
Originally posted by derrickleggett
SELECT COUNT(*)
FROM customer
SELECT COUNT(*)
FROM customer
WHERE customer IS NOT NULL
SELECT COUNT(*)
FROM customer
WHERE customer IS NULL
Statement 2 + 3 = 1
--Therefore NULL has been evaluated.
SELECT COUNT(*)
FROM customer
WHERE salutation = 'mr'
COUNT(*)
--------
4338
SELECT COUNT(*)
FROM customer
WHERE salutation <> 'mr'
COUNT(*)
--------
6269
SELECT COUNT(*)
FROM customer
WHERE salutation IS NULL
Count of last three will equal total
--Therefore NULL has been evaluated.
Again, I am very familiar with how this works. However, think about your end users. The ones running ad-hoc queries using crystal reports and tell me that they understand how to handle NULL values.
Do you wrap every SELECT argument with ISNULL or NVL? I didn't think so.
-ec |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
Switzerland
413 Posts |
Posted - 03/23/2004 : 02:32:32
|
I think I mentioned in my some previous posts, that the discussion on this topic has been around for quite a long time. It will definitely not be solved here nor anywhere else, but, once again, it turned out to be very funny again.
I strongly suggest reading this
http://www.baetzler.de/humor/null.html
This gives really deep insights into NULL and what smart brains can do with it.
--Frank
http://www.insidesql.de |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 03/23/2004 : 02:34:16
|
quote:
Originally posted by derrickleggett
Am I going to make my numbers 0 since I'm making my strings empty btw??? Is that the numeric equivalent of an empty string? It isn't if you're an accountant. It's a very significant number. So do I have to define a rule for each datatype? No, I just need to know that NULL means I didn't receive anything and the value is unknown.
Yes, or it could mean that the person doing data entry simply forgot to input that value. In that case it could mean two things. Or maybe it meant that the software you use to calculate the value messed up and didn't properly insert a valid value. Or it could be a dozen other reasons. The point is NULL can mean anything, not just one thing and that is the problem.
Even though you think you can define precisely what NULL means in your table, you really can't.
-ec |
|
|
|
Topic |
|
NULL opinion?
Forum Locked
