NULL Value

is a special value, representing a missing or unknown value.

Syntax

NULL

Remarks

The NULL value can be assigned to variables or table columns of any datatype.

Usually, when an operator or function receives a NULL as argument, the result is also NULL.

PRINT 10 + NULL;
PRINT SIN(NULL);

The result is:

<NULL>
<NULL>

Comparison

The result of comparison operators is NULL if any operand is NULL.

To check if a value is NULL, don’t use the equality operator =, but use IS NULL or IS NOT NULL:

DECLARE @a INT;    -- the initial value of the variable is NULL

PRINT @a = NULL;   -- NULL
PRINT @a IS NULL;  -- true

The result is:

<NULL>
true

Logical Operations

In SQL, logical operators AND, OR, NOT evaluate to True, False or NULL, with a 3-valued logic.

PRINT 1=1 AND 2=NULL;  -- NULL
PRINT 1=2 AND 2=NULL;  -- false

The result is:

<NULL>
false

String Concatenation

The concatenation operator + will return NULL if any string is NULL.

To avoid this, use the function CONCAT:

PRINT 'Hello' + NULL + 'World';
PRINT CONCAT('Hello', NULL, 'World';

The result is:

<NULL>
HelloWorld

Function ISNULL

The function ISNULL returns the value of the first argument if it is not NULL. Else, it returns the replacement value.

DECLARE @a INT; -- @a is NULL

PRINT ISNULL(@a, 123);
PRINT ISNULL(9000, 123);

The result is:

123
9000

Function NULLIF

The function NULLIF returns NULL if the two arguments are equal.

PRINT NULLIF(123, 456);
PRINT NULLIF(123, 123);
PRINT NULLIF(123, '456');   -- string '456' is converted to int
PRINT NULLIF(123, 'Hello'); -- string 'Hello' cannot be converted to int

The result is:

123
<NULL>
123
<Conversion error. VARCHAR "Hello" cannot be cast into INT, invalid string.>