NULLIF

Description

The NULLIF() function returns NULL if expr1 = expr2 is true, otherwise returns expr1.

The return value has the same type as the first argument.

Syntax

1. > NULLIF(expr1,expr2)

Examples

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

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

5. -- Use the NULLIF() function to set the salary of employees whose salary is a specific value to NULL. The NULLIF(salary, 1500) function will compare the value of the salary field with 1500. Returns NULL if the salary value equals 1500; otherwise, returns the salary value.
6. mysql> SELECT name, salary, NULLIF(salary, 1500) AS adjusted_salary FROM employees;
7. +-------------+---------+-----------------+
8. | name        | salary  | adjusted_salary |
9. +-------------+---------+-----------------+
10. | John Doe    | 1000.00 | 1000.00         |
11. | Alice Smith | 2000.00 | 2000.00         |
12. | Bob Johnson | 1500.00 |                 |
13. +-------------+---------+-----------------+
14. 3 rows in set (0.01 sec)