Adhu oru rathiri neram...

Adhu oru rathiri neram..
all are sitting together in my room...
10 eyes towards one rectangle box and a single finger is making
some actions. All were shouting whether is it an Apple or a Boy or a Cat
or a Dog.

Literally our brains are outside our head and was in a bench...

Boojiyam, saiber, zero, muttai..
adherku matru oru payer dhaan NULL in one language...
there were some chaos in choosing the thing bcoz of this void matter called "NULL"

I want to post an article on this to make it clear for me and also for others
reference on this Fifth state of void matter so called NULL.

Null Values
A NULL in a database denotes (shock, horror!) a missing value. Such things have a way of happening.

Nulls pose the following problems:

* If you inner join columns and one of the values is NULL, that row will be ignored. Note that fixes exist, but those that use or or in, cannot be combined with an outer join (which cannot be used with or or in).
* NULLs in aggregate functions are ignored; for example, if you're adding up and averaging say, incomes, the person with a null income will be ignored. Well and good if his income was null; not so hot if some silly person forgot to enter the value! Note that there is a useful property of the count function - if you provide it with a constant (eg 1) as an argument, it counts all the rows in the table, but if you provide it with an expression, it looks for all column names within that expression, and then excludes rows containing NULL values for any one of those columns!
* Conversion functions {??? QV xref} provided with null, return null.
* NULLs mess around with Boolean logic (Well, actually, they extend it to something you don't want to know a lot about, Lukasiewiczian logic)!
* All operators (apart from || concatenation) return NULL if you (anywhere, just anywhere) give them a NULL to play around with!

There is only one rule for NULL values, and that is:

Avoid NULL values. Okay, you should also avoid anthrax and casual sex...

If, however, you through some defect in your psychological makeup cannot do so (avoid the NULLs, that is) or you've inherited a database stuffed with NULLs, then you can do a variety of things:

* Curl into the foetal position and whimper quietly to yourself until they come and cart you away;
* Test for the NULLs using something along the lines of
where columname is NULL
(You can also use is not null for the opposite condition);
* Use order by on the offending column to list the null columns
* Use a quick fix:

Quick NULL fixes
If you know exactly what you're doing, you might want to take your data and in calculations use another value for NULL. This sort of approach should be taken with appropriate fear and trembling. Here's an example of the coalesce function that does the dirty deed, in this case replacing all NULLs in calculations with the value zero:

select avg ( coalesce (columname, 0)) from tablename;

You should only apply the above approach if you are sure that the substitution is appropriate, which is rarely the case! Note that different SQL dialects have different synonyms for coalesce - for example, Oracle uses nvl.

Even more contrary is to replace a known value with NULL ! SQL92 provides the nullif( columname, targetvalue) function to do so. Some SQL implementations lack this function, if not the functionality.



Courtesy: http://www.anaesthetist.com/mnm/sql/frills.htm