Page 1 of 1

What is the use of NULL/NOT NULL in DB2?

PostPosted: Sun May 09, 2010 12:25 am
by maxcc
What is the use of NULL/NOT NULL in DB2? Why do we use it?

Re: NULL

PostPosted: Sun May 09, 2010 12:54 am
by dick scherrer
Hello,

If you are not already familiar with the concept of NULL, i'd suggest you invest the time in things that will be more useful to you. None of the systems i work with these days permit NULLs. Everything is NOT NULL.

NULL is the concept of a non-existent value. For example one byte can contain from x'00' to x'FF' for 256 possible values. NONE of these 256 values is NULL. NULL is the absence of a value, so a NULLable 1-byte field requires an additional indicator to say whether the column has a value of not (this is known as the "NULL Indicator"). A NULLable multi-byte column also requires a NULL indicator, but the indicator is for the entire column (not each byte).

For theory, it is fine, but is problematic for most applications. Using NULL typically adds confusion and extra code (that is often incorrectly implemented). . .

Re: NULL

PostPosted: Sun May 09, 2010 1:56 pm
by maxcc
That is much clear now. In addition to it my question, What is the advantage overall in a program of using null or not null? or if it was used earlier? I was asked in an interview..

Re: NULL

PostPosted: Sun May 09, 2010 7:27 pm
by dick scherrer
Hello,

As i mentioned earlier, NULL brings more problems than solutions.

I see no "advantage" but that is probably not the answer some interviewer is looking for. . .

Re: NULL

PostPosted: Sun May 09, 2010 7:29 pm
by Robert Sample
There are some cases (survey responses for example) where it can be important to distinguish between having data and not having data. These cases are pretty rare, however, and most of the time NULL will cause more problems than it solves.

Re: What is the use of NULL/NOT NULL in DB2?

PostPosted: Tue May 11, 2010 3:26 pm
by GuyC
NULL will only cause problems with programmers who are to lazy to learn how to use it. Since all programmers all lazy this would be the case most of the times.

Even when none of your columns are NULLS allowed programmers still have to know how to handle them in case of a left or outer join.
Or has those not allowed either? you might as well go back to sequential file handling then.

Systems that don't allow nulls are very simple ; violate some very basic R.I. rules or have a kind of default row for owner entities : like you can't hire someone until you know in which department he works, what office he's in or what's his internal phonenumber.

Re: What is the use of NULL/NOT NULL in DB2?

PostPosted: Wed May 12, 2010 1:01 am
by dick scherrer
Hello,

NULL will only cause problems with programmers who are to lazy to learn how to use it.
Opinions vary. . . But i do agree, that there are more lazy programmers than ever before :)

Systems that don't allow nulls are very simple ; violate some very basic R.I. rules or have a kind of default row for owner entities :
Again, an opinion not a fact. . . However; if a simple solution provides perfect results, why not use the simple solution. . .

Knowing how to use a thing does not make it a good thing to use. . .

like you can't hire someone until you know in which department he works, what office he's in or
I've only implemented these kinds of "rules" a few thousand times and never needed to use NULL to accomplish it. . . Indeed, we did this quite nicely for many years before there was a NULL . . .

Long ago i was more of a purist. For the last 25 years or more, my clients have preferred implementations that always worked, were easily maintainable (often by beginners), and were conservative of system resources. . .

One of the biggest problem areas for missed communication/information is with the users who neither need nor relate to dealing with NULLs. And more and more of our users generate their own queries.

As Robert mentioned, there are a few cases when using NULL is best, but i have seen very few.

Re: What is the use of NULL/NOT NULL in DB2?

PostPosted: Wed May 12, 2010 7:39 pm
by GuyC
dick scherrer wrote:we did this quite nicely for many years before there was a NULL . . .

You fail to mention how (in a relational database), but every solution (even NULL) has its disadvantages.