The NULLIF SQL Function in MySQL and MariaDB - Returns NULL if expressions are equal |
|
| | NULLIF | Syntax: | NULLIF(Expression 1, Expression 2) | | Return value: | Depending on the data types used | | Function type: | NULL function | |
| | The NULLIF() function returns NULL if "Expression 1" is equal to "Expression 2".
If "Expression 1" is not equal to "Expression 2", NULLIF() returns "Expression 1".
Where, if "Expression 1" is true, 1 is returned, if false, 0 is returned.
If "Expression 1" is NULL, the function will always return NULL. | SQL Examples for the NULLIF function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
| SELECT nullif('ok', 'ok');
SELECT nullif('ok', 'ok2');
SELECT nullif(1, 1);
SELECT nullif(3, 4);
SELECT nullif(true, true);
SELECT nullif(true, false);
SELECT nullif(false, true);
SELECT nullif(1, null);
SELECT nullif(null, 1);
SELECT nullif(null, null);
|
|
| nullif('ok', 'ok') |
|---|
| varchar(2) BINARY |
|---|
| NULL |
|
|
| nullif('ok', 'ok2') |
|---|
| varchar(2) BINARY |
|---|
| ok |
|
| |
| |
|
| nullif(true, true) |
|---|
| int(1) |
|---|
| NULL |
|
|
| nullif(true, false) |
|---|
| int(1) |
|---|
| 1 |
|
|
| nullif(false, true) |
|---|
| int(1) |
|---|
| 0 |
|
| |
| |
| |
|
| | The examples were created with the MyWAY SQL manager: | How to use the NULLIF() function in MySQL and MariaDB databases | The MySQL and MariaDB NULLIF() function is used to compare two expressions and return NULL if they are equal or the first expression, if they are not equal, which is useful when replacing a certain value with NULL for output or calculations in SQL queries. The function simplifies the logic by allowing comparison and replacement to be performed in a single step, providing flexibility in manipulating and transforming data in SQL queries.
NULLIF() compares the expressions based on their data types and performs a binary comparison. Therefore, the data types of the expressions to be compared should be compatible. The function can be used in applications, such as replacing certain values with NULL, to be able to handle certain conditions or special cases in queries, such as default values or placeholders in result sets as NULL to treat. Furthermore, the function is used to avoid errors in calculations or to simplify conditional expressions. | | Further MySQL und MariaDB SQL NULL functions | |
| | | More information about the NULLIF SQL function: and and |
|
|
|
|
