NULLIF Function

If expr1 is equal to expr2, NULL is returned; otherwise, expr1 is returned.

Syntax

NULLIF(expr1, expr2)

Arguments

expr1 und expr2: Values of any data type. Both values should have the same data type.

Usage Notes

The data type of the return value is the same as the data type of expr1.

Examples

SELECT col1, col2, NULLIF(col1, col2)
FROM   (SELECT 1 AS col1, 1 AS col2
        UNION ALL 
        SELECT 1 AS col1, 2 AS col2);

+---------+---------+--------------------------+
| COL1    | COL2    | NULLIF(COL, COL)         |
+---------+---------+--------------------------+
| 1       | 1       | NULL                     |
| 1       | 2       | 1                        |
+---------+---------+--------------------------+

SELECT col1, col2, NULLIF(col1, col2)
FROM   (SELECT '1' AS col1, '1' AS col2
        UNION ALL 
        SELECT '1' AS col1, '2' AS col2);

+---------+---------+--------------------------+
| COL1    | COL2    | NULLIF(COL, COL)         |
+---------+---------+--------------------------+
| 1       | 1       | NULL                     |
| 1       | 2       | 1                        |
+---------+---------+--------------------------+

SELECT col1, col2, NULLIF(col1, col2)
FROM   (SELECT 'a' AS col1, 'a' AS col2
        UNION ALL 
        SELECT 'a' AS col1, 'A' AS col2
        UNION ALL 
        SELECT 'b' AS col1, 'a' AS col2);

+---------+---------+--------------------------+
| COL1    | COL2    | NULLIF(COL1, COL2)       |
+---------+---------+--------------------------+
| a       | a       | NULL                     |
| a       | A       | a                        |
| b       | a       | b                        |
+---------+---------+--------------------------+