The NULLIF() Function — Syntax and Usage Examples

Let’s talk about another function for dealing with the mysterious NULL — NULLIF(). It’ll help you out when you need to swap certain values for NULL so you can handle your data the right way. Buckle up, this is gonna be a fun ride!

The NULLIF() function is a great tool in PostgreSQL that lets you compare two values and returns NULL if they’re the same, or the first value if they’re different. It’s kinda like saying: “If these values are the same, let’s just forget about them!”

Here’s the simple syntax for the function:

NULLIF(value1, value2)

If value1 and value2 are equal, the result is NULL. If they’re different, the result is value1.

Example:

SELECT NULLIF(10, 10); -- Result: NULL
SELECT NULLIF(10, 20); -- Result: 10
SELECT NULLIF('hello', 'hello'); -- Result: NULL
SELECT NULLIF('hello', 'world'); -- Result: 'hello'

Easy, right? Now let’s try using this function in some real-life tasks.

Example 1: Preventing Division by Zero

Dividing by zero is every programmer’s nightmare. In SQL, if you try to divide by zero, you’ll get an error. That’s where NULLIF() comes to the rescue.

Imagine you have a sales table with two columns: revenue and expenses. You want to calculate the profitability (revenue / expenses), but sometimes expenses are zero. If you just run the query without any checks, you’ll get a division by zero error.

The sales table:

Broken query:

SELECT revenue / expenses AS profitability
FROM sales;
-- Error: division by zero!

Solution with NULLIF():

SELECT revenue / NULLIF(expenses, 0) AS profitability
FROM sales;

Result:

Now, if expenses is 0, NULLIF(expenses, 0) returns NULL, and dividing by NULL doesn’t throw an error — it just gives you NULL.

Example 2: Replacing Identical Values with NULL

Imagine you’ve got a table with student data, with columns first_name and preferred_name. Sometimes students prefer their real name, sometimes they pick something else. If both names are the same, there’s no point in showing preferred_name.

The students table:

Query with NULLIF():

SELECT first_name,
       NULLIF(preferred_name, first_name) AS display_name
FROM students;

Result:

When preferred_name and first_name are the same, the result is NULL.

Example 3: Filtering Data

Sometimes you want to pick only those rows where the values in two columns are different. For example, you’ve got an orders table with original_price and discounted_price. You want to find orders where a discount was actually applied.

The orders table:

Query with NULLIF():

SELECT order_id, original_price, discounted_price
FROM orders
WHERE NULLIF(original_price, discounted_price) IS NOT NULL;

Result:

Only orders with a discount show up in the result.

Practical Use Cases for NULLIF()

Case 1: Picking an Optional Value

When working with data, sometimes you need to pick a value from several columns, skipping those that are equal to a certain value. For example, you’ve got a list of employees with their salaries: base_salary and bonus. If the bonus is 0, you want to set it to 999.

The employees table:

Query with NULLIF():

SELECT employee_id,
       base_salary + COALESCE(NULLIF(bonus, 0), 999) AS total_salary
FROM employees;

Result:

If bonus is 0, it turns into 999.

Case 2: Counting Rows

You can use NULLIF() to make complex filtering conditions easier. For example, you’ve got a users table and you want to get both the total number of users and the number of registered users (not ‘guest’) in one query.

SELECT
  COUNT(*) AS total_users,
  COUNT(NULLIF(status, 'guest')) AS registered_users
FROM users;

So, with just one function — NULLIF() — you can solve a bunch of problems: from avoiding errors to just making your code easier to read. Now that you know how it works, you’re already one step closer to mastering data work in PostgreSQL.