NULL is anything which is unknown or unavailable in any RDBMS system and anyone who works in RDBMS environment must take extra precautions while dealing with data that may have some NULL values. There are few basics rules which one must follow in order to handle NULL values effectively.
- Mandatory columns may get NULL values from SOURCE or due to JOINS between tables. In such cases to avoid "NULL VALUE IN NOT NULL FIELD" error, use COALESCE function. eg: COALESCE(MANDATORY_COLUMN, 'SOME DEFAULT VALUE');
- While Joining several tables we may be having NULL values for some columns participating in JOIN CONDITION. In such cases, it is advisable to add one more condition along with join conditions to fetch only those records with some value for that column. eg: COLUMN_NAME IS NOT NULL (here COLUMN_NAME is used for joining with other table and may have NULL values.)
- Sometimes while we are checking some conditions and we don't want to remove records with NULL values instead we want to treat records with value as NULL as same , in that case above method will not work. In such cases, use COALESCE function in comparing the records from both tables. eg: COALESCE(COLUMN_1,'-1') = COALESCE(COLUMN_2,'-1') , with this method we can compare the values even if the column is having NULL values. However, it may lead to unexpected result set. So we should be little careful while using this method.
- Performing any kind of mathematical or logical Operations on NULL value in a column is not allowed. To check for NULL values, extra check should be implement by using condition: COLUMN_NAME IS NULL or COLUMN_NAME IS NOT NULL. NULL along with any operation may lead to unexpected result.
Have you faced any situation where due to NULL values you were getting wrong result ?