Playing with NULL

A NULL values is an unknown value, different from a zero or an empty value.

There are few points that must be remembered when you are dealing with NULL values:

1.       No two NULL values are equal.

2.       Comparison between two NULL values or between one NULL and any other values results in an UNKNOWN value.

3.       Logical (e.g. AND, OR etc.) and Comparison (e.g. =, >, < etc.) Operators can return a third result UNKNOWN during comparison between two expressions.

4.       Concatenating two string values, if one of strings is Null, then result will be a null value. (SET CONCAT_NULL_YIELDS_NULL is ON)

How to manipulate NULL values?

There are three cases where you can face NULL values occurrences,

First case – Conditional expressions – used in WHERE clause, with IF statements, or with CASE statements, all above are for comparison between columns or between a column and a value.

Second case – Data sets returned through a selection or SELECT statement. Here you apply comparisons and conversion by evaluating or equating the values.

Third case – DML Statements, mostly considered statement here is INSERT statement, for NULL allowable columns, where you are not providing values for them, NULL is inserted.

You can use mix of below given functions and clauses for both first and second cases, while for third case if the columns are null allowable you can specify DEFAULT constraint with some appropriate default value.

There are three functions and two clauses that you can use to deal with NULL values.

Functions:

a.       ISNULL (Check_Expression, Replacement_value) – Replaces NULL with the specified replacement value.

b.      NULLIF (Expression, Expression) – Returns a NULL value if two provided expressions are equal or first expression if they are not equal.

c.       COALESCE (Expression,…..n) – Returns first non-NULL expression among n expressions or NULL if all expressions contain null.

Clauses:

a.       Expression IS NULL – Determines whether the specified expression is null. Returns TRUE if expression contains a null value otherwise FALSE.

b.      Expression IS NOT NULL – same as above, but it negates the result of above clause. FALSE for the expression containing null values otherwise TRUE.

Example:

/*

@String1 has default value.

@String2 has null as default.

@String3 has null as default.

*/

DECLARE @String1 varchar(10) = 'String1'

            ,@String2 varchar(10)

            ,@String3 varchar(10)

           

SELECT ISNULL(@String2, @String1) AS "ISNULL"

SELECT NULLIF(@String2, @String3) AS "NULLIFF"

SELECT COALESCE(@String2, @String3, @String1) AS "COALESCE"

SELECT 1 AS "IS NULL" WHERE @String2 IS NULL

SELECT 1 AS "IS NOT NULL" WHERE @String1 IS NOT NULL