NULLIF function in MySQL

Introduction

The NULLIF function helps you compare two values and returns NULL if they are the same. Otherwise, it returns the first value. This is useful to avoid dividing by zero or to handle special cases in data.

When you want to avoid errors from dividing by zero in calculations.

When you want to replace a specific value with NULL to mark it as missing or special.

When comparing two columns and you want to return NULL if they match.

When cleaning data by turning certain values into NULL for easier filtering.

Syntax

MySQL

NULLIF(expression1, expression2)

Returns NULL if expression1 equals expression2.

Otherwise, returns expression1.

Examples

Returns NULL because both values are equal.

MySQL

SELECT NULLIF(5, 5);

Returns 5 because the values are different.

MySQL

SELECT NULLIF(5, 3);

Compares two columns and returns NULL if they are equal for each row.

MySQL

SELECT NULLIF(column1, column2) FROM table_name;

Sample Program

This query shows how NULLIF replaces 0 with NULL but keeps other values.

MySQL

SELECT
  value,
  NULLIF(value, 0) AS value_or_null
FROM
  (SELECT 10 AS value UNION ALL SELECT 0 UNION ALL SELECT 5) AS sample;

Important Notes

NULLIF is handy to prevent division by zero errors by turning zero into NULL.

If either expression is NULL, NULLIF returns NULL.

Summary

NULLIF compares two values and returns NULL if they are equal.

It helps handle special cases like zero values or matching columns.

Use it to avoid errors and clean data easily.