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