Skip to content

NULLIF

Grammar Description

The NULLIF() function is used to compare the values ​​of two expressions. If the values ​​of expr1 and expr2 are equal, the NULLIF() function returns NULL; otherwise, the value of expr1 is returned. This function is usually used to deal with avoiding division by zero or avoiding errors when using invalid values ​​in calculations.

Grammar Structure

> NULLIF(expr1,expr2)

Example

CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, salary DECIMAL(10, 2) );

INSERT INTO employees (name, salary) VALUES ('John Doe', 1000), ('Alice Smith', 2000), ('Bob Johnson', 1500);

-- Use the NULLIF() function to set the salary of an employee whose salary is a specific value to NULL. The NULLIF(salary, 1500) function compares the value of the salary field to 1500. If the value of salary is equal to 1500, NULL is returned, otherwise the value of salary is returned.
mysql> SELECT name, salary, NULLIF(salary, 1500) AS adjusted_salary FROM employees;
+------------------------------------------------------------------------------------------------------------------------------
| name | salary | adjusted_salary |
+------------------------------------------------------------------------------------------------------------------------------
| John Doe | 1000.00 | 1000.00 |
| Alice Smith | 2000.00 | 2000.00 |
| Bob Johnson | 1500.00 | |
+------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.01 sec)