Nulls

IS [NOT] NULL

IS [NOT] NULL is used to check whether an expressions evaluates to a null (unknown) value.

Syntax:

<expression> IS NULL
<expression> IS NOT NULL

For example:

null IS NULL // true
1 IS NULL // false

Attention

IS NULL must be used to check for null value.s Equals (=) will always evaluate to null.

ISNULL

ISNULL provides an alternative to a null value.

Syntax:

ISNULL(<expression>, <alternative>)

For example:

ISNULL(NULL, 1) = 1
ISNULL(2, 1) = 2

NULLIF

NULLIF checks if two values are the same and return null or the first expression otherwise.

Syntax:

NULLIF(<expression1>, <expression2>)

For example:

NULLIF(4,4) = NULL
NULLIF(5,7) = 5

COALESCE

Returns the first non-nullable argument if any or null otherwise.

Syntax:

COALESCE(<expression1>, ...)

For example:

COALESCE(NULL, NULL, 3, 4) = 3
COALESCE(NULL, NULL, NULL) = NULL