NULL Equals NULL?
-
What can I say, it was a long train ride home. BTW I copy-and-pasted the table creation script from Cimode's prior post.
I definitely should have checked them Your query explicitly casts the data type of the terminated_date to varchar. If I don't want to cast it to varchar, and I also do not want the string value 'Still Active' passed back to my application (since it is expecting a date/time value), we're in a bit of a quandry here.
Date previously wrote that the current generation of RDBMS software is not up to the task of implementing his grand vision of the Relational Model. SQL Server, for instance, does not implement D. It also does not have several of the key components in place to implement his vision without, for lack of a better word, a bunch of "hacks" like the complex queries needed to replace simple constructs like a basic SQL LEFT JOIN query.
A lot of Date's recent work is aimed specifically at the "next generation of RDBMS", while what I have written is aimed specifically at the "current generation of SQL Server."
Completely and totally banishing NULL from SQL in all forms throughout all databases everywhere is an admirable goal, but without the proper tools for the job (and I maintain that the current generation of SQL is not the tool for the job) it is not going to be an easy sell to people who need databases that are performant and easy to maintain today. It also won't be an easy sell for those who don't have huge budgets to completely overhaul their existing databases, middle tiers, and front end applications.
-
OK, quite a debate and perhaps a new record on posts to an article
I did indeed send a note to cimode and did not receive a response, which is what I expected.
I have also banned his/her username.
I think others did not behave well in a few posts, but those others have shown they can be professional and not continue to flame others.
I did think there were good arguments to be made, but unprofessional, abusive, flaming diatribe is not the way to achieve this. These comments remind me of another well known database professional who has similar problems writing polite responses to posts.
I have not edited any of cimode's posts since this thread would be rendered useless by doing so. There is still some good information in here.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Mike, not my post. Judging from the tone of your reply you must be mistaking me for a previous poster. Dunno who originated that script 'cos I don't have the energy to go back on the thread to find out.
I neglected to mention there's no way I'd use that strategy in real life, I was simply contributing to the discussion in the original spirit of exploration of ideas (before the thread got hijacked). I was simply posting an example of how it might be done, just to prove a point (that you can live without NULL logic, but at a cost).
There are several ways of "improving" it, but of course it would still be awkward. I'll leave it there - perhaps the temperature has risen past the point of objectivity.
I did consider using a temp table, but I didn't want to annoy someone who was bitten by one as a child.
Returning NULLs to client code is a whole different set of issues. Some languages do not deal with them at all well, and require either casting to a more friendly data type, or replacement by "special" known date values (1-Jan-1800 anyone?).
-
Hi SDM,
Sorry, I thought you were Cimode (didn't look at the heading close enough)
You did make my point to Cimode for me though, which is if you want to completely eliminate NULL from SQL, you have to jump through hoops to replicate SQL's outer join functionality. I think you can probably simplify it a little bit to something like this:SELECT t.employee_ID, employee_name, hire_date, convert(varchar(50),terminated_Date)
FROM active_personnel a
INNER JOIN terminated_personnel t
ON a.employee_id = t.employee_id
UNION ALL
SELECT employee_id, employee_name, hire_date, 'Arbitrary String Indicator for No Termination Date'
FROM active_personnel a
WHERE a.employee_id
NOT IN (
SELECT t.employee_id
FROM terminated_personnel t
)
But there's really no way around the UNION and it gets worse as you try to join more tables. Try Date's Sixth Normal Form on for size. Just imagine converting 20 tables with 20 columns each into 400 tables with a maximum of 2 columns each, and then try to retrieve 50 columns worth of data using these NULL-avoidance techniques. While it's good to minimize NULLs in your SQL databases, I don't think it's practical, or even advisable, to eliminate *all* NULLs from SQL using the current SQL RDBMS technology.
Sorry again for the confusion
-
FYI - Those two tables are the work of RM Expert Cimode in his attempt to demonstrate eliminating *all* NULLs from the database and query results.
-
Has anyone brought up the "IS" keyword?
DECLARE @var nvarchar(32)
SET @var = NULL
IF @var IS NULL
BEGIN
PRINT 'True'
END
ELSE
BEGIN
PRINT 'False'
END
-
"You will soon realize that I have not so far insulted anybody..."
Really? You called me "liar", folk.
-
I don't want to start a flame war but IMHO you are describing a platonic ideal. Sometimes NULLS can not be avoided.
-
Hi JT, IS NULL and IS NOT NULL are covered in the original "4 Rules" article (link in this article).
-
Hey, he only called me "self-aggrandizing", "ignorant (x10)", "liar (x4)", "intellectually dishonest (x4)", and I think he even slipped "spade" in there somewhere. And that was just the warm-up, before he really got around to talking about the article
I really wish he had brought up some actual points from the article itself to demonstrate whatever it was he was trying to say, instead of re-presenting the same information I put in the aggregate functions article a year or so ago. It could have been a very constructive conversation... Ah well, coulda, woulda, shoulda...
-
OK... I'm new to this forum, and happy to see it isn't ... boring!
It would be great to do away with nulls altogether, especially since the ANSI committee in their wisdom introduced three-valued logic.
But first, as Mr. Coles and others point out, something else would need to be devised (and implemented!) that would make it possible to:
1) have foreign keys that do not require a "dummy" row in the parent table for "no-value";
2) have some way of representing "no numeric value" and "no datetime value", akin to an empty string;
3) have something to indicate the equivalent of nulls in outer joins.
Chris Date would agree here, I think: nulls are bad, but they'll have to be replaced, not just jettisoned.
Ron Rice
-
Thanks for the info - some of the NULL fog is beginning to lift. Any hints as how to concatenate (MS SQL) a string that has some null values and some text?
James
-
sjwegg (10/5/2007)
Thanks for the info - some of the NULL fog is beginning to lift. Any hints as how to concatenate (MS SQL) a string that has some null values and some text?James
James,
You need to use either Coalesce or IsNull to concatenate a null value with a string. Like:
Select LastName + ', ' + FirstName + ' ' + Coalesce(MiddleName, '') From employees
You just need to provide a character value to the concatenation.
As a side note, I did enjoy the article and much of the forum related to it. I think most, if not everyone, understands that in a perfect world Nulls would be eliminated, but perfection is not where I live. I appreciate Mike C for posting the article(s) on dealing with Nulls. I come to SSC to get practical solutions to everyday problems. If I want to learn theory I buy a book on theory. Don't misunderstand me, I think it is important to understand the theory and appreciate when someone posts or writes an article dealing with theory and provides examples to go along with the theory, which is where Cimode fell short. As Mike C mentioned, if Cimode had come in and said I disagree with X, Y, Z; here is why; and here is an alternate solution I think all of us would have appreciated his contibution and been open to try it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
I just wanted to add a little comment that it is often better to avoid using Coalesce or Case statements to check for null values. From a performance standpoint these two functions seem to make it very difficult for SQL Server to utilize indexes on the referenced tables.
Here a link where I got this information from:
-
Jereme,
I am pretty sure using Case or Coalesce in the select list will not change the indexes used by the Optimizer, but using a case or function against a column in the where clause will almost always cause an index scan or table scan. The article you reference is talking about the criteria in the Where clause also. Thus in the example I made for James the Coalesce function should not affect the query plan.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 76 through 90 (of 117 total)
You must be logged in to reply to this topic. Login to reply