NULL Equals NULL?

Question

NULL Equals NULL?

Viewing 15 posts - 31 through 45 (of 117 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Mike C,

Man, nice job. I absolutely agree with Tim's assesment of the "AA" he speaks of. The real fact is "NULLs Happen" in a database... if you don't know how to deal with them, you article helps a great deal.

Should have called your article "NULL is NOT Nothing"

--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)

Cimode Ten Centuries Points: 1333 More actions · Answer 2

//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 have not called ignorant anybody in the comments made. I just pointed out some fundamental issues to help some people here get out from the usual crap put in *learn databases in 21 days* books.

//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.//

Classical *theory vs practical* argument. If you don't think the problem of NULLS is not practical and real enough in your cherished db's, gosh that must be sad for your users...

//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.//

Now, that's a personal attack!!!

I provided examples and logical arguments to backup all I claimed but you say that I don't state my opinion like an intelligent person...

You basically produced but meaningless gibbrish and insults..so far...

F. Dwarf SSCommitted Points: 1598 More actions · Answer 3

Thanks for your OPINION Cimode. However I think you should take a look to the books online and figure out how SUM works.

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 !

That's not a big surprise when you understand how aggregation functions work.

And, IMHO, I see no reason to use

select * from table1 where field1 = field2

union

select * from table1 where field1 <> field2

union

select * from table1 where field2 is null

instead of a "select * from table1" statement. I don't know, perhaps I have no clue about database fundamental

Mike C SSC-Insane Points: 23224 More actions · Answer 4

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?

Hi Tim,

For one reason, COALESCE is ANSI-defined, so it is more portable (if portability is an issue) and presumably more stable in its definition than vendor-defined extensions like ISNULL. Also COALESCE is more flexible in that you can list several arguments. For instance:

WHERE COALESCE(column1, column2, column3, '') <> ''

Will work when you want the value of the first non-NULL column from a list of several columns (or an empty string if they are all null). ISNULL is limited to only two arguments. To get the previous example to work with ISNULL would require nested ISNULL functions:

ISNULL(ISNULL(ISNULL(column1, column2), column3), '')

Additionally, COALESCE is defined by ANSI as "shorthand" for a CASE expression, and the data type of the result is implicitly chosen by COALESCE to accomodate all possible values in the argument list. ISNULL returns the data type of the first argument every time. Here's a short sample:

DECLARE @i TINYINT

SET @i = NULL

-- This will work

SELECT COALESCE(@i, 9999999999)

-- This will not work

SELECT ISNULL(@i, 9999999999)

There is some evidence that ISNULL is optimized a little more than COALESCE (probably because of the two-argument limitation and the limitation on the data type of the return value), but from what I've seen the speed difference is negligible. Another thing to keep in mind is that COALESCE is defined in the ANSI SQL-92 standard, so it is not implemented on legacy SQL 6.5 systems, if backwards compatibility is an issue.

Cimode Ten Centuries Points: 1333 More actions · Answer 5

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...

Looking at the response style, the writer simply diverts attention from debate of NULLS on terminology issues or offtopic issues. Which simply confirms the initial premice...

I let people judge for themselves.

#1) //In your first example, you refer to columns in a table as "fields". What is wrong with this? See below for answer. Before you go into "personal attack mode", you should ensure that you know what it is you're talking about./

Answer to #1: To quote Joe Celko, "Columns are not fields, rows are not records, and tables are not files." As Celko has also pointed out, newbies often expose their inexperience by confusing "columns" with "fields" and "rows" with "records".

CELKO would not know RM i

I do not do personal attacks but I don't mind pointing out absurdities.

Is that the best you can do? A silly etymology issue...(I remind people the issue at hand is NULLS and using them or NOT)

Answer to #1: To quote Joe Celko, "Columns are not fields, rows are not records, and tables are not files." As Celko has also pointed out, newbies often expose their inexperience by confusing "columns" with "fields" and "rows" with "records".

CELKO is considered a charlatan in the RM knowledgeable audience. It is no surprise you call his name...

--> CONCLUSION: AN OFFTOPIC ETYMOMOLOGICAL RESPONSE BASED ON THE SAYING OF SOMEBODY WHO HAS NO CLUE ABOUT RM

//#2) As a guy who obviously "has a clue about database fundamental theory", 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 what? See below for answer.//

Trying to divert attention on etymological issues..rather than defending the points I criticized is one beautiful proof of his ignorance of RM concepts..And I like the see below answer..

Answer to #2: A table is not a table without a Primary Key.

How come you do have to resort to divert the discussion from NULLS to primary key. You are simply offtopic. Primary key issues have nothing to do with NULLS. A second confirmation your treatment of NULLS do not rely on much...

--> CONCLUSION: AN OFF TOPIC RESPONSE ABOUT PRIMARY WHEN THE INITIAL ISSUE WAS NULLS.

#3) This phenomenon is discussed at great length in my article on SQL aggregate functions. You get exactly what you describe, why? See below for answer.

//So use of NULLS will mess up your sum results...//

Which of course is not grave in your perspective...

Answer to #3: The results described occur because ANSI defines that the SUM aggregate function eliminates NULLs. This is described in my other article on Aggregate Functions and NULLs.

As I said, SQL Comittee standard is filled up with people who have no clue about database fundamentals...That is why people like DARWEN dumped several years ago...

--> CONCLUSION: Given the amount of gibberish produced so far, I doubt anything else you have produced deserves additional viewing. I suggest to people to simply ignore your comments...

#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

according to the data, it returns all matching records between field1 and field2...The query returns

field1 field2

1 1

Now run

select * from table1 where field1 field2

to return non matching records in column field1 and column of field2...Where on woulmd expect the three last records, the system returns

field1 field2

2 1

3 2

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.

--> CONCLUSION : NO RESPONSE ON THIS

#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...

--> CONCLUSION: NO RESPONSE ON THIS

#6) //[or "Look Ma, No NULLs!"] Here are two properly defined tables that do not allow NULL values 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? You state that NULLs and 3VL have no place in the "Relation Model" [sic]. Following your logic a little further, because outer joins are likely to produce NULLs in their result sets, do you advise that outer joins have no place in the "Relation Model" [sic] as well?

[Snipped]//

If you had any education in RM theory you would have known that LEFT OUTER JOIN is not a part of RM and was merely an attempt to force presentation layer into data layer...Such problems do not exists with INNER JOIN closer to what a NATURAL JOIN...

--> CONCLUSION: I LET PEOPLE JUDGE FOR THEMSELVES

#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. After all, I wrote the original 4 Rules article for that exact reason myself. I for one am very eager to share in the wealth of your knowledge and experience.

--> CONCLUSION:

I have provided responses to your nonsense, examples and proofs...

I have provided books that people can read if they want to progress and understand...

I suggest to people to simply IGNORE your article...Being ignorant of RM concepts, you are in NO position to educate people about such concepts..

Because my time is as limited as yours that's already a big effort..

Frédéric van der Plancke SSC Enthusiast Points: 198 More actions · Answer 6

Yes, nice article indeed.

I've got a few questions though (more related to the discussion with Cimode...):

* why does SUM behave this way with NULLs ? To be consistent with the interpretation of NULL as "unknown value" I would want SUM to return NULL as soon as it meets a NULL (like + does), but I suppose there are good DB-related reasons for SUM to ignore NULLs instead -- can you give a link to your article on aggregate functions, does it answer this question ?

* (ahum) why is column field, row record ? is this a matter of logical result (of a query) implementation ?

Cimode Ten Centuries Points: 1333 More actions · Answer 7

//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. [Wink] 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? [Big Grin] //

If you plan on quoting somebody's code, at least do it right and do not attribute to me some code I have not written.....People can judge for themselves....from initial posts....

You posted the writer's code not mine ...The code I posted Neither included #table1 nor it grouped anything and runs perfectly...

create table table1(field1 int, field2 int)

go

insert table1

select 1, 1

insert table1

select 2, 1

insert table1

select 3, 2

insert table1

select 4, NULL

select sum(field1 + field2) from table1

select(field1) + sum(field2) from table1

select * table1 where field1 = field2

Cimode Ten Centuries Points: 1333 More actions · Answer 8

//Thanks Tim. As soon as I saw Cimode's reference to "database fundamental theory" combined with his poorly designed examples that don't even run, which only provided proof of what I said in these particular articles, I found it a little hard to take him seriously. The over-the-top personal attacks don't really add to his credibility either. After all, we're not performing at the Apollo here//

I encourage people to read for themselves and make up their mind...

A BIG LIE!!! the EXAMPLES RUN PERFECTLY AND WERE TESTED BEFORE GETTING posted...That liar of Gonzalez mistook the writers example with mine....

The examples were amply sufficient to prove all what was said by the wirter was basically crappola...His nonsense illogical response confirmed my initial impression...

Cimode Ten Centuries Points: 1333 More actions · Answer 9

//* why does SUM behave this way with NULLs ? To be consistent with the interpretation of NULL as "unknown value" I would want SUM to return NULL as soon as it meets a NULL (like + does), but I suppose there are good DB-related reasons for SUM to ignore NULLs instead -- can you give a link to your article on aggregate functions, does it answer this question ?//

Because NULLS are SQL's only way to handle missing data and it's a poor way...

The writer of the article obviously has no clue about the issues at hand. That's why he divert constantly to offtopic responses and resorts to lie saying that the code IO provided does not work. Should someone like that be thrusted. I suggest you ignore his comments and turn to knowledgeable audiences who have treated the problem of NULLS seriously, I have been putting some pointers if interested...

//* (ahum) why is column field, row record ? is this a matter of logical result (of a query) implementation ?//

Don't yourealize that's an attempt to divert debate from NULLS to offtopic terminology issues. THe writer has not presented ONE relevant response...

Frédéric van der Plancke SSC Enthusiast Points: 198 More actions · Answer 10

Cimode,

Given the tone of your posts from the first one on, no wonder the discussion gets heated. Some of the arguments brought against you were mean, but yours were hardly palatable from the start on.

Frédéric

Cimode Ten Centuries Points: 1333 More actions · Answer 11

Now, because I have pointed out for the readers the incoherence of the article -->I am the *bad guy*.

You will soon realize that I have not so far insulted anybody...

But I vehemently attacked ideas and misconception and ignorance...

If not convinced produce one proof of the opposite...Several people now have lied as to the code provided or treated me an **hole or *mean*... Why don't you do morals to them..?

Tim D-404826 SSC Enthusiast Points: 199 More actions · Answer 12

Cimode,

If i made the statement "Only complete morons would so vehemently attack ideas and misconceptions" would you be offended?

I mean, i wasn't directly attacking you, only the idea that anyone who is "attacking" anything is a moron. It is this such mentality that has lead everyone in this thread to feel you are grossly inept in your public communication, and should limit your public communication to preaching at your *likely* cult meetings on imperialism.

You imply ignorance of the writer in your comments about the subject matter, as if you would be ignorant to be intested in anything this man would write. You should try some anger management classes, or perhaps an AA seminar.

Either way, lighten up. You should learn to address your concerns about misconceptions in a constructive and productive manner, not in one that assumes an attacking position.

Cimode Ten Centuries Points: 1333 More actions · Answer 13

<>

I have proved my points one by one. When somebody stands in public and vehiculates misconceptions and fallacies, he should be willing to take the beating or back up his statement with solid argument. The writers and some dishonnest people around did not do much but you lecture me...

<>

If you think that my attack on the writers's ideas is grave what about the fallacies and bullshit he writes. Do you have any idea of what damage that will do for instance to a thrustful unexperienced user...

<>

I do not imply ignorance in writer's comment. I simply demonstrated that what he wrote is simply BS and I backed up what I said. Until now the writer has not given one decent answer.... As for anger management I am fine thanks

//Either way, lighten up. You should learn to address your concerns about misconceptions in a constructive and productive manner, not in one that assumes an attacking position.//

So far I have produced:

--> Code examples to establish the dangers of NULLS

--> Educated advice onto how to design good databases (NOT USING NULLS)

--> Pointers to books

--> Response to nonsense point by point

--> A harsh critic to somebody's nonsensical article for the good of the community...

Question: Except a moralistic tone, insults(**hole, *mean* and personal attacks), you have not applied what exactly have you or the writer produced??? Not much I am afraid...

In other words, let's let people judge for themselves...

Mike C SSC-Insane Points: 23224 More actions · Answer 14

I do not do personal attacks but I don't mind pointing out absurdities.

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...

I find your conflicting statements "absurd". To take your rhetoric to it's logical conclusion, you must feel that Dr. Codd had "no clue about database fundamental theory" either, as NULLs were his creation. Correct?

Looking at the response style, the writer simply diverts attention from debate of NULLS on terminology issues or offtopic issues. Which simply confirms the initial premice...

Is that the best you can do? A silly etymology issue...(I remind people the issue at hand is NULLS and using them or NOT)

If you're going to make personal attacks on other people, you should come to the party prepared to defend your position intelligently.

I let people judge for themselves.

Based on the number of views, the ratings people have applied, and the volume of comments on the article in question, it appears that people have already judged for themselves.

Answer to #2: A table is not a table without a Primary Key.

How come you do have to resort to divert the discussion from NULLS to primary key. You are simply offtopic. Primary key issues have nothing to do with NULLS. A second confirmation your treatment of NULLS do not rely on much...

--> CONCLUSION: AN OFF TOPIC RESPONSE ABOUT PRIMARY WHEN THE INITIAL ISSUE WAS NULLS.

The fact that you are a self-proclaimed expert who cannot even design a simple table, and whose shortest queries are infested with bugs, is extremely relevant to the conversation at hand.

If you want to be accepted as an expert in the field as you are claiming, it doesn't support your cause to post bug-riddled code samples that don't actually execute.

--> CONCLUSION: Given the amount of gibberish produced so far, I doubt anything else you have produced deserves additional viewing...

I see again that you resort to personal attacks, but I've gone halfway through your post already and found absolutely zero substance.

#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.

--> CONCLUSION : NO RESPONSE ON THIS

I expected as much.

#5) The reason for this result is described in detail in the 4 Rules article. Please review as necessary.

--> CONCLUSION: NO RESPONSE ON THIS

Again I expected as much.

If you had any education in RM theory you would have known that LEFT OUTER JOIN is not a part of RM and was merely an attempt to force presentation layer into data layer...Such problems do not exists with INNER JOIN closer to what a NATURAL JOIN...

--> CONCLUSION: I LET PEOPLE JUDGE FOR THEMSELVES

And we are dealing here with SQL, which you might not have noticed, is not the same thing as "Relation Model Theory" [sic]. You claim the two are not the same, and then scream that they are. You really should make a coherent argument somewhere.

Feel free to retrieve the results I retrieved, using the tables and sample data I supplied, without dealing with or retrieving any NULLs.

#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...

--> CONCLUSION:

I have provided responses to your nonsense, examples and proofs...

I have provided books that people can read if they want to progress and understand...

I suggest to people to simply IGNORE your article...Being ignorant of RM concepts, you are in NO position to educate people about such concepts..

Because my time is as limited as yours that's already a big effort..

First of all, your proofs are poorly laid out. The results you provide in your "proofs" do nothing to dispute or disprove anything I've said. In fact, if your sample code actually did run, the results you claim would support the information I provided.

Second, you are contradicting yourself. You claim that 3VL and NULL are not "RM concepts". Yet I provided information on 3VL and NULL. Now you claim that I am 'in no position to educate people about such ("RM") concepts.' You cannot have it both ways. Your logic is flawed and circular.

For the record, I was providing information on SQL concepts, and in SQL we have to deal with such things.

Third, I've read Date, Codd, et al., and it's wonderful reading. And at the end of the day I get to sit down at a SQL Server and my tables and code have to work on SQL Server; regardless of what Date wrote or what Cimode tries to sneak in between personal attacks. As such, I have to deal with NULLs on a daily basis; whether from some table someone designed with NULLable columns or from an outer join.

Feel free to put your money where your mouth is, write an article of substance, and submit it for publication.

I, for one, will be sorely disappointed if you don't share your vast knowledge with the world.

Mike C SSC-Insane Points: 23224 More actions · Answer 15

Yes, nice article indeed.

I've got a few questions though (more related to the discussion with Cimode...):

* why does SUM behave this way with NULLs ? To be consistent with the interpretation of NULL as "unknown value" I would want SUM to return NULL as soon as it meets a NULL (like + does), but I suppose there are good DB-related reasons for SUM to ignore NULLs instead -- can you give a link to your article on aggregate functions, does it answer this question ?

Hi Frédéric,

The link for the article on NULL-handling in aggregate functions is here: http://www.sqlservercentral.com/columnists/mcoles/gotchasqlaggregatefunctionsandnull.asp

ANSI defines most aggregate functions to ignore NULLs, discarding them during the aggregation process. The exception is COUNT(*) which does not discard NULLs.

* (ahum) why is column <> field, row <> record ? is this a matter of logical result (of a query) <> implementation ?

It's a matter of professionalism SQL (and the relational model) have a specific vocabulary for describing various concepts. Fields and records are used to define the old non-RM flat file systems. Normally I wouldn't even bother mentioning it, since you can easily tell from the context what someone is talking about most of the time. After all, if we're talking about a table in a SQL database and you say "give me the records for all sales over $1,000", I'll understand that you want the rows, no problem.

But there are those rare occasions that pop up, like when someone claims he's an expert in the field and then resorts to personal attacks. I suppose it pays to make sure you've got your ducks in a row before personally attacking what you perceive as someone else's level of knowledge.