website logo
⌘K
šŸ Homepage
šŸ‘‹Introduction
Key Concepts & Architecture
šŸš€Run Oxla in 2 minutes
šŸƒā€ā™‚ļøRun Oxla on S3
šŸ›«Multi-node Deployment
šŸ‘Øā€šŸ’»SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
Comment Support
🚨Error Handling
šŸ†šDifferences Between Oxla vs. PostgreSQL
šŸ“ˆPublic Metrics
Docs powered by
Archbee
SQL Reference
...
SQL Functions
Other Functions

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);


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);


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

Pgsql
|
 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);


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

Pgsql
|
 if 
----
  9


Case #3: String Comparison

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

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


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);


Display the records of the table:

Pgsql
|
SELECT * FROM employees;

Pgsql
|
  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;


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


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);


Display the table using the SELECT statement:

Pgsql
|
SELECT * FROM fractions;

Pgsql
|
 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;


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




Updated 13 Sep 2023
Did this page help you?
PREVIOUS
COALESCE
NEXT
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
Docs powered by
Archbee

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




©2023 Oxla