MySQL: why assign NULL as default value When we define a table, multiple times we assign default value of a column as NULL. I have


How NULL is different than empty value('') as well as 0?
Is there any impact of NULL value in query performance?
How it affects the index created on that column?


Thanks...
NULL is different from empty string '' and 0. Empty string as well as 0, are existing values that can be used in equations and other operations.


'' = '' is true

0 = 0 is true

NULL = NULL is not true

Quoted from Wikipedia:


  Null is a special marker used in Structured Query Language (SQL) to
  indicate that a data value does not exist in the database.



I'm not sure what kind of impact on performance would be expected with NULLs instead of ''s or 0s, but AFAIK no, there is no big difference in performance between the two. Somebody may correct me if I'm wrong. An exception to this rule is noted below.
First of all, with NULL you can have a UNIQUE INDEX on a column, and still have many NULL values. This is by definition of the SQL NULL. Read more at mysql reference


Also, if you use InnoDB (I'm only saying this because your question is tagged with mysql-5.1), then no value is stored at all, which may result in big reduction in storage and possibly faster searching (since more rows fit into one page)

On the contrary, with MyISAM, nullable column indexes may have increased size causing slightly increased storage space required.

In the end, it is neither bad, nor good practice to use NULL columns. Combined with bad design they can be a headache. But with good design they are useful and helpful. NULL columns deserve to have their position in well designed databases, they're just not appropriate for every column.