nullif()

Overview

The NULLIF() function allows us to replace a given value with null if it matches a specific criterion.

Syntax

The following illustrates the syntax of the NULLIF function:

NULLIF(argument_1,argument_2);

From the syntax above, we learn that the NULLIF function takes two arguments:

• The first argument is the value we want to evaluate
• The second argument is the value we want to treat as null if the first argument matches it

The Output:
If the first argument matches the second argument, the NULLIF() function returns NULL. Otherwise, it returns the first argument as-is.

Examples

Case #1: Handling Equal Values

In this case, the NULLIF function is used to compare the values 4 and 4.

SELECT NULLIF (4, 4);

The result will be NULL since the two values being compared are equal (4 = 4).

 if 
----
   

Case #2: Handing Different Values

In this example, we want to use the NULLIF function to manage different values.

SELECT NULLIF (9, 0);

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

 if 
----
  9

Case #3: String Comparison

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

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

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

 if 
----
 L

Case #4: Handling Default Values

Suppose we have 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);

Display the records of the table:

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 if 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

Case #5: Avoiding Division by Zero

Suppose we have 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);

Display the table using the SELECT statement:

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