Cloud

NULLIF

The NULLIF function replaces a given value with NULL if it matches a specific criterion.

Syntax

NULLIF(argument_1,argument_2);

The NULLIF function takes two arguments:

  • The first argument is the value to evaluate.

  • The second argument is the value to treat as NULL if the first argument matches it.

If the first argument matches the second argument, the NULLIF function returns NULL. Otherwise, it returns the first argument as-is.

Examples

Handle equal values

In this case, the NULLIF function compares the values 4 and 4.

SELECT NULLIF (4, 4);

The result is NULL because the two values being compared are equal (4 = 4).

 if
----

Handle different values

This example uses the NULLIF function to manage different values.

SELECT NULLIF (9, 0);

The result is 9 because the second value in the NULLIF function is 0 (the two values are not equal).

 if

  9

String comparison

In this case, the NULLIF function compares the strings L and O.

SELECT NULLIF ('L', 'O');

The result is L because the two strings being compared (L and O) are not equal. Therefore, the function returns the first string.

 if

 L

Handle default values

Suppose there is an employees table with columns for name and salary. This query retrieves employee names and their adjusted salaries, where a salary of 0 is replaced with NULL:

CREATE TABLE employees (
    name TEXT,
    salary INT
);

INSERT INTO employees (name, salary)
VALUES
    ('John', 50000),
    ('Jane', 0),
    ('Roy', 0),
    ('Neil', 0),
    ('Michael', 75000);

View the records:

SELECT * FROM employees;
  name   | salary
---------+--------
 John    |  50000
 Jane    |      0
 Roy     |      0
 Neil    |      0
 Michael |  75000

This query retrieves employee names and their adjusted salaries, where a salary of 0 is replaced with NULL:

SELECT name, NULLIF(salary, 0) AS adjusted_salary
FROM employees;

The NULLIF function checks whether the salary value is 0. If it is, the function returns NULL. Otherwise, it returns the original salary value.

  name   | adjusted_salary
---------+-----------------
 John    |           50000
 Jane    |
 Roy     |
 Neil    |
 Michael |           75000

Avoid division by zero

Suppose there is a fractions table with columns, a numerator and a denominator.

CREATE TABLE fractions (
    numerator INT,
    denominator INT
);

INSERT INTO fractions (numerator, denominator)
VALUES
    (10, 2),
    (20, 0),
    (15, 3),
    (75, 0),
    (15, 3);

View the table:

SELECT * FROM fractions;
 numerator | denominator
-----------+-------------
        10 |           2
        20 |           0
        15 |           3
        75 |           0
        15 |           3

Here, the NULLIF function is applied to the denominator column. If the denominator is 0, the function returns NULL, avoiding division by zero.

SELECT numerator, denominator, numerator / NULLIF(denominator, 0) AS "result" FROM fractions;

The result is shown in the result column.

 numerator | denominator | result
-----------+-------------+--------
        10 |           2 |      5
        20 |           0 |
        15 |           3 |      5
        75 |           0 |
        15 |           3 |      5