Sunday, 5 May 2013

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.

Post a Comment