NULLIF

19min
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:
Pgsql
|
NULLIF(argument_1,argument_2);
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. 
Pgsql
|
SELECT NULLIF (4, 4);
SELECT NULLIF (4, 4);
﻿
The result will be NULL since the two values being compared are equal (4 = 4).
Pgsql
|
 if 
----
   
 if 
----
   
﻿
Case #2: Handing Different Values
In this example, we want to use the NULLIF function to manage different values. 
Pgsql
|
SELECT NULLIF (9, 0);
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).
Pgsql
|
 if 
----
  9
 if 
----
  9
﻿
Case #3: String Comparison
In this case, the NULLIF function compares the strings 'L' and 'O'. 
Pgsql
|
SELECT NULLIF ('L', '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.
Pgsql
|
 if 
----
 L
 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:
Pgsql
|
CREATE TABLE employees (
    name TEXT,
    salary INT
);

INSERT INTO employees (name, salary)
VALUES
    ('John', 50000),
    ('Jane', 0),
    ('Roy', 0),
    ('NEil', 0),
    ('Michael', 75000);
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:
Pgsql
|
SELECT * FROM employees;
SELECT * FROM employees;
﻿
Pgsql
|
  name   | salary 
---------+--------
 John    |  50000
 Jane    |      0
 Roy     |      0
 NEil    |      0
 Michael |  75000
  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:
Pgsql
|
SELECT name, NULLIF(salary, 0) AS adjusted_salary
FROM employees;
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. 
Pgsql
|
  name   | adjusted_salary 
---------+-----------------
 John    |           50000
 Jane    |                
 Roy     |                
 NEil    |                
 Michael |           75000
  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. 
Pgsql
|
CREATE TABLE fractions (
    numerator INT,
    denominator INT
);

INSERT INTO fractions (numerator, denominator)
VALUES
    (10, 2),
    (20, 0),
    (15, 3),
    (75, 0),
    (15, 3);
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:
Pgsql
|
SELECT * FROM fractions;
SELECT * FROM fractions;
﻿
Pgsql
|
 numerator | denominator 
-----------+-------------
        10 |           2
        20 |           0
        15 |           3
        75 |           0
        15 |           3
 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.
Pgsql
|
SELECT numerator, denominator, numerator / NULLIF(denominator, 0) AS "result" FROM fractions;
SELECT numerator, denominator, numerator / NULLIF(denominator, 0) AS "result" FROM fractions;
﻿
The result is shown in the result column. 
Pgsql
|
 numerator | denominator | result 
-----------+-------------+--------
        10 |           2 |      5
        20 |           0 |       
        15 |           3 |      5
        75 |           0 |       
        15 |           3 |      5
 numerator | denominator | result 
-----------+-------------+--------
        10 |           2 |      5
        20 |           0 |       
        15 |           3 |      5
        75 |           0 |       
        15 |           3 |      5
﻿
﻿
Updated 13 Sep 2023
Did this page help you?
COALESCE
pg_total_relation_size
Docs powered by
Archbee
TABLE OF CONTENTS
Overview
Syntax
Examples
Case #1: Handling Equal Values
Case #2: Handing Different Values
Case #3: String Comparison
Case #4: Handling Default Values
Case #5: Avoiding Division by Zero