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