NULL Equals NULL?
-
Gosh, some people can be very strident. "Just don't use nulls!" ? Really, show me the db that can be guaranteed to have a value in every row, every column. Some people don't have a 2nd address line ... some people don't have a middle name ... And numerically "no value" means 0.00, not null, anyway. Simple really.
Or are you advocating that for text "no value" should always be a blank, not a null? I'd like a detailed answer on the overall impact that has, especially on a large database.
It just sounds to me like some people have got tripped up by nulls in the past and now harbor serious grudges against them.
-
Actually, "no value" does not mean 0.00 and NULL definitely does not mean 0.00. NULL means unknown or no value.
I have worked with, and continue to work with, several databases with tables that have numeric fields where a NULL is necessary. They are necessary because in these fields a 0 (zero) value means zero (production, amount, etc.). This means we get a reading, but the reading is zero (a known value). The NULL indicates that the value is unknown. For whatever reason, we did not get a value to populate with. This could be due to equipment malfunction, communication failures, or any number of reasons.
In these cases we don't want to put a zero because that may mean we had zero production when, in fact, there is production, but for whatever reason we do not have that value at a particular point in time.
I agree that working with NULLs can cause problems but to simply say "never use them" is irresponsible. Just like everything else, use them properly and know your system and data and you can work with the system you have.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
-
For quite some time we have been using a particular syntax which has worked quite well for us in determining NULL matches.
In our where clause we would use...
Where ISNULL(column,'') <> '' -- this will match all non-NULL columns
In the article, you mention that ISNULL() is not preferred to use. Can you explain why?
-
//Can you take 5 minutes of your time and write an article that will "correct" the situation and share it with the world... you will also be paid for sending the article in.//
The fundamental problem with NULLS and 3VL is not a problem that can be taken care of in 5 minutes...For more info about NULL implications, lots of work made by knowledgeable audiences have been made among which Chris DATE (Codd's assistant)
Check thethirdmanifesto.com or
Practical Issues in Database Management (Fabian PASCAL) on Amazon...
-
//One thing that frustrates me about how SQL Server handles NULL within unqiue constraints is that it does only allow one NULL value (as the article does a nice job of illustrating). It makes sense that it would allow >1 NULL value because they are in fact different values. Other RDBMS do allow this, so I kinda wish that SQL Server did also.//
NULLS are SQL's way of handling missing data and it is the worst one to do it. There are other ways to do it but current DBMS do not support it...Be it on ORACLE, DB2 or SQL Server, they are an aberration...
-
//Or are you advocating that for text "no value" should always be a blank, not a null? I'd like a detailed answer on the overall impact that has, especially on a large database. //
I advocate that anybody who studies seriously relational model can design databases with NO NULL values. Databases with NULL values everywhere are designed by people who ignore fundamentals database design.
//It just sounds to me like some people have got tripped up by nulls in the past and now harbor serious grudges against them.//
NULL values were put in databases by people who had no clue about database management fundamental issues...
Such *grudge* is no coincidence. Studying RM in detail reveals the absurdity of using NULL values...
Inntroduction to Database Systems (CJ DATE)....
-
//There's a workaround on this one... but it comes with a cost ://
You call a workaround. I call it a hack.
-
//I agree that working with NULLs can cause problems but to simply say "never use them" is irresponsible. Just like everything else, use them properly and know your system and data and you can work with the system you have.//
The task of NOT using NULLS seems impossible to you for a simple reason: you ignore the data fundamentals that would allow you to do so.
I personally designed several databases with NO NULL values allowed and my world simply get easier as much as users are happy NOT to get false results.
-
//For quite some time we have been using a particular syntax which has worked quite well for us in determining NULL matches.
In our where clause we would use...
Where ISNULL(column,'') '' -- this will match all non-NULL columns
In the article, you mention that ISNULL() is not preferred to use. Can you explain why?//
If one does not allow NULLS in the first place in the database, one won't have to deal with all these problems AT ALL. One won't have to invent several hacks to maintain a system that will produce false results no matter what hack is applied.
-
There are my two cents in support of NULLs.
I totally agree with hawq and others - NULLs are the part of database logic and one, sometimes, cannot avoid them, but treat them as a special value by itself. Yes, it has unusual logic and adds some additional checks to the code, but it is totally necessary, and the guidelines in the article are excellent for those who are confused or do not understand the logic of nulls to it's full extent.
And, saying "do not use nulls" is so wrong. Use them where appropriate, and, when using, expect them to be a part of your result set and write your code with this in mind.
-
Cimode
I take great exception to the tone you have used in your responses. This is a forum for people to share ideas and experiences and you calling us ignorant because we don't share your opinion is uncalled for.
I do not "study" RDM's I implement production level, highly tuned business systems that use nulls to indicate unknown values. Unless you have detailed knowledge of the application, it's data and it's use you have no right to say it is stupid to use nulls and that any other solution would be more appropriate. As most of us know the theory set forth in books is just that a theory and reality is for the most part a very different world.
If you stated your "opinion" like an intelligent individual instead of attacking like a someone with too much book study and not enough real knowledge your opinions might count actually count for something.
-
I'll attempt to address your concerns one at a time here:
People do not confuse, people are either ignorant or misled by ignorants. That the case of the person who wrote this stupid article.
Obviously the article is posted by somebody who has no clue about database fundamental theory....The writer just made up the *Four Rules* to make it sound scientific but in fact, most of what's written is just a bunch of crappola...
#1) In your first example, you refer to columns in a table as "fields". Columns are not fields. Before you go into "personal attack mode", you should ensure that you know what it is you're talking about.
First, let's create a simple table and fill it with data
create table table1(field1 int, field2 int)
go
<<snip>>
#2) I'm sure you can tell us all what's wrong with the sample you posted. Here's a hint - a table is not a table without a primary key.
select sum(field1 + field2) from table1
--> it returns 10
then run...
select(field1) + sum(field2) from table1
which should produce the same righ. Wrong!!
--> it return 14 !
#3) This phenomenon is discussed at great length in my article on SQL aggregate functions. You get exactly what you describe, because that's how the aggregate functions are defined by ANSI, plain and simple.
So use of NULLS will mess up your sum results...
#4) And a stove will burn down your house if you don't know how to use it properly. I can't speak on your behalf, but personally I'm not going to stop cooking my food because I'm scared my house will burn down every time I turn the stove on.
select * table1 where field1 = field2
<<snip>>
As you can see the system does consider that 4 = NULL a total onsense. 3VL will somebody say but 3VL does not apply in relational modeling. Only 2VL logic applies in Relation Model else it is nothing but relational.
#5) The reason for this result is described in detail in the 4 Rules article. Please review as necessary.
If you have NO NULLS in the first place, you won't have to deal with all these problems...
#6) [or "Look Ma, No NULLs!"] Here are two properly defined tables that do not allow NULLs to be stored in them. How do you advise people to handle the results of the query in the following example, in which NULLs are produced by the LEFT JOIN?
CREATE TABLE #Users (UserID INT NOT NULL PRIMARY KEY,
UserName VARCHAR(10) NOT NULL)
CREATE TABLE #UserPhone (UserID INT NOT NULL,
PhoneType VARCHAR(5) NOT NULL,
PhoneNumber VARCHAR(20) NOT NULL,
PRIMARY KEY (UserID, PhoneType))
INSERT INTO #Users (UserID, UserName)
VALUES (1, 'Cimode')
INSERT INTO #Users (UserID, UserName)
VALUES (2, 'Boudjakdji')
INSERT INTO #Users (UserID, UserName)
VALUES (3, 'Celko')
INSERT INTO #UserPhone (UserID, PhoneType, PhoneNumber)
VALUES (2, 'Home', '1-900-4867')
INSERT INTO #UserPhone (UserID, PhoneType, PhoneNumber)
VALUES (2, 'Work', '1-900-1234')
INSERT INTO #UserPhone (UserID, PhoneType, PhoneNumber)
VALUES (3, 'Home', '1-900-5678')
SELECT u.UserID, u.UserName, up.PhoneType, up.PhoneNumber
FROM #Users u
LEFT JOIN #UserPhone up
ON u.UserID = up.UserID
DROP TABLE #Users
DROP TABLE #UserPhone
The results of this code sample are as follows:
UserID UserName PhoneType PhoneNumber
1 Cimode NULL NULL
2 Boudjakdji Home 1-900-4867
2 Boudjakdji Work 1-900-1234
3 Celko Home 1-900-5678
#7) If you feel that any part of the information I have given is incorrect, please feel free to write and submit an article correcting any misinformation you feel I may have provided...
-
Good set of rules - let me add one more: "Keep in mind that different implementors will implement NULLS in different ways: a practical reason to avoid them." I maintain code that went from Britton Lee (NULLS not allowed) to Sybase (NULLS implemented in a very permissive kind of way, including allowing tests for equality even tho its theoretically incorrect) to SQL Server (by default, implement as Codd envisioned them).
I well understand Date's "we were wrong to allow them" argument and mostly avoid them - but sometimes it's the way you'll get the work done and often you inherit them anyway. But when porting from one platform to another (the ANSI standards are newer than most of the code I work with) they can really get you badly.
Roger L Reid
-
Speaking of "no clue"...
select(field1) + sum(field2) from #table1
... will yield ...
Server: Msg 8118, Level 16, State 1, Line 1
Column '#table1.field1' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Now, maybe it was just a cut and paste error, but if you're going to flame like that, at least get your examples right.
That author also clearly stated that NULLs should not be used in place of zeroes... seems like your examples would fall into that category.I read you other posts, as well... you have some good points and some bad... too bad that most of them are lost on folks because you're flaming so bad. Having a bad day or what?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Way to go Mike C. Excellent article, if for no other reason than to help enlighten people with additional knowledge.
There is nothing worse in this world than an arrogant asshole who *thinks* he knows everything, and not only tries to berate others, but does so with inadequate knowledge of the subject at hand.
Life is about education. We should all be willing to share our knowledge and discuss professionally the benefits and downfalss of differing methods of accomplishment.
Viewing 15 posts - 16 through 30 (of 117 total)
You must be logged in to reply to this topic. Login to reply