How to Use the NULLIF() Function in MySQL

08/05/2021

Contents

In this article, you will learn how to use the NULLIF() function in MySQL.

Using the NULLIF() function in MySQL

The NULLIF() function in MySQL is used to compare two expressions and return null if they are equal.

Syntax

The syntax for using the NULLIF() function in MySQL is as follows:

SELECT NULLIF(expression1, expression2) FROM table_name;

Where “expression1” and “expression2” are the expressions to be compared.

Examples

Suppose we have a table named “employees” with columns “employee_id”, “first_name”, “last_name”, and “salary”. We can use the NULLIF() function to return null for employees with a salary of 0 as follows:

SELECT employee_id, first_name, last_name, NULLIF(salary, 0) AS salary FROM employees;

This query will return the employee ID, first name, last name, and salary (with 0 values replaced by null) of all employees in the “employees” table.

We can also use the NULLIF() function in combination with other functions and clauses to create more complex queries. For example, suppose we want to calculate the average salary of all employees in the “employees” table, excluding those with a salary of 0. We can use the following query:

SELECT AVG(NULLIF(salary, 0)) AS average_salary FROM employees;

This query will return the average salary of all employees in the “employees” table, excluding those with a salary of 0.

#MySQL