NULLIF

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram>>-NULLIF--(--expression--,--expression--)---------------------><
 

The NULLIF function returns a null value if the arguments compare equal, otherwise it returns the value of the first argument.

expression
The arguments must be compatible and comparable data types. Character-string arguments are compatible with datetime values. If one operand is a distinct type, the other operand must be the same distinct type. The arguments cannot be DataLink values.

The attributes of the result are the attributes of the first argument. The result can be null. The result is null if the first argument is null or if both arguments are equal.

The result of using NULLIF(e1,e2) is the same as using the expression

  CASE WHEN e1=e2 THEN NULL ELSE e1 END

Note that when e1=e2 evaluates to unknown (because one or both arguments is NULL), CASE expressions consider this not true. Therefore, in this situation, NULLIF returns the value of the first operand, e1.

Example