SQL Tip #9 – All I know about SQL NULL

NULL indicates missing/unknown/inapplicable/absent.

If the above statement makes you think that NULL is useless, then you are mistaken. This is one of the thing that no database developer can avoid it. So it very important to understand how NULL behaves differently in various situations.

Simply we can say, NULL is a placeholder for missing or unknown values at any given time.

At the time of the table creation if we know that values for certain columns won’t be known or will be missing during record insertion then we can make those columns to allow NULL. Later NULL could be replaced with the meaningful values.

Takeaway for NULL:

  • It is very important not to get confused NULL as an space or empty string.
  • NULL is not equal to NULL.
  • Any operation involving NULL will return NULL
  • Comparison operators (<, >, =, <>) cannot be used to find records with the NULL values. IS NULL or IS NOT NULL operators is the way to go.
  • NULL will be ignored for most of the aggregate functions. However, COUNT(*) will include NULL values.

 

Following examples below will return NULL:

SELECT 'Sumon' + NULL
SELECT 0 + NULL
SELECT 10 * NULL
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s