Some SQL programmers and developers tend to think NULL as zero or blank. In fact, NULL is neither of these. NULL literally means that the value is unknown or indeterminate.
One side effect of the indeterminate nature of NULL value is it cannot be used in a calculation or a comparision.
Listed below are a few important rules to remember about the behaviour of NULL values.
- A NULL value cannot be inserted into a column defined as NOT NULL.
- NULL values are not equal to each other. It is a frequent mistake to compare two columns that contain NULL and expect the NULL values to match. (A NULL column can be identified in a WHERE clause or in a boolean expression using phrases such as ‘value is NULL’ and ‘value is NOT NULL’.)
- A column containing a NULL value is ignored in the calculation of aggregate values such as AVG, SUM or MAX.
- When columns that contain NULL values in a GROUP BY clause of a query are listed, the query output contains rows for those NULL values.
- JOINS between tables, in which one join condition contains values and the other contains NULL, are governed by the rules for “outer joins”.
