Dealing with NULL values in SQL Server

In RDBMS missing data is represented by NULL. Generally it indicates that the data has not been entered into the Database or technically it indicates 'value is unknown'.Since NULL is unknown so any operation with NULL will return NULL. E.g: SELECT 1 + NULL The above query will return NULL as result. NULL is not equal to NULL also. E.g: CASE     WHEN NULL = NULL THEN 'Equal'     ELSE 'Not Equal' END It will return 'Not Equal'. '=' operator can't check for nulls, there is another special operator 'IS', which is used to test for equivalance of special values. CASE     WHEN NULL IS NULL THEN 'Equal'     ELSE 'Not Equal' END It will return 'Equal'. Functions to handle NULL values ISNULL - It is a TSQL(propriotory of Microsoft) function but is not defined by ANSI. - It accepts two parameters and if the first parameter is evaluated to be NULL then the second parameter is returned else first parameter is returned. - It is similar to CASE     WHEN parmeter1 IS NOT NULL THEN parameter1     ELSE parameter2 END COALESCE - It is also a TSQL function but unlike ISNULL it is a part of ANSI. - It accepts two or more than two parameters and returns the first non-NULL parameter / expresion. - It is similar to CASE     WHEN parmeter1 IS NOT NULL THEN parameter1     WHEN parmeter2 IS NOT NULL THEN parameter2     ....................................     ....................................     ELSE parameter-n END Example: :- SELECT ISNULL(NULL, 'Devi') will return Devi. :- SELECT ISNULL(NULL, NULL) will return NULL. :- SELECT COALESCE(NULL, 'Devi') will return Devi. :- SELECT COALESCE(NULL, 'MFS', NULL, 'Devi') will return MFS

Difference: - In general it is accepted that ISNULL is slightly faster than COALESCE. - COALESCE is ANSI compliant, so COALESCE will not require any rework if you are changing your RDBMS, e.g: SQL Server to Oracle. - COALESCE accepts more than two parameters / expressions whereas ISNULL accepts only two. So in order to compare more than two parameters using ISNULL we have to write nested expressions. E.g: SELECT ISNULL(ISNULL(NULL, 'MFS'), 'Devi')  -- Will return MFS and here we are compairing NULL, MFS, Devi. So in this case COALESCE will be helpful and easier. - ISNULL will change the return value's data type to the data type of first argument. But COALESCE will promotes its arguments to the highest data type among compatable argument. NULLIF - It accepts two parameters and if they are equal, it returns a NULL else it returns the first parameter. - It is similar to: CASE     WHEN parameter1 = parameter2 THEN NULL     ELSE parameter1 END If a Database is polluted with N/A, blank or other values where it should contain nulls, we can use NULLIF() to replace the inconsistent values with nulls and clean the database.