NULLIF

MySQL

NULLIF(expr1, expr2)

• expr1: This is the first expression to be compared in the NULLIF function. It can be a column name, a constant, or the result of another function.
• expr2: This is the second expression to be compared in the NULLIF function. Like expr1, it can also be a column name, a constant, or the result of another function.

Example

SELECT NULLIF(4,4), NULLIF(5,4);

Output

+-------------+-------------+
| NULLIF(4,4) | NULLIF(5,4) |
+-------------+-------------+
|  NULL       |     5       |
+-------------+-------------+

Explanation

In the example, the function NULLIF compares two expressions. When the expressions are equal, the function returns NULL. In the first case, 4 equals 4, so NULL is returned. In the second case, 5 does not equal 4, so 5 is returned.

PostgreSQL

NULLIF(expression1, expression2)

• expression1: This is the first expression to be compared. It can be any data type that is comparable.
• expression2: This is the second expression against which the first expression is compared. It should be of a comparable data type to the first expression.

Example

SELECT NULLIF(5, 5), NULLIF('PostgreSQL', 'MySQL');

Output

 NULL | PostgreSQL
------+-----------
      | PostgreSQL

Explanation

`NULLIF` is a PostgreSQL function that compares two expressions. If the expressions are equal, `NULLIF` returns `NULL`. Otherwise, it returns the first expression. In this example, the function returns `NULL` for `NULLIF(5, 5)` because `5` equals `5`, and `'PostgreSQL'` for `NULLIF('PostgreSQL', 'MySQL')`, as `'PostgreSQL'` does not equal `'MySQL

</TabItem>

<TabItem label="SQL Server">
#### NULLIF\(expression1, expression2\)
- expression1: The first expression or value that is going to be compared in the NULLIF function\. This could be a column name, a literal value, or a resulting value from an arithmetic expression\. The data type of expression1 should be compatible with expression2\.
- expression2: The second expression or value involved in the comparison\. This expression corresponds with the first one and serves as the check value\. If the value of expression1 equals expression2, the NULLIF function will return a NULL value\.

#### Example
```sql
SELECT NULLIF('SQL', 'SQL') AS Test1, NULLIF('SQL', 'Python') AS Test2;

Output

Test1      Test2
-------------------
NULL       SQL

Explanation

The NULLIF function in SQL Server compares two expressions. If they are equal, it returns NULL. If they aren’t equal, it returns the first expression. In the example, ‘SQL’ and ‘SQL’ are equal, so Test1 returns NULL. ‘SQL’ and ‘Python’ are not equal, so Test2 returns ‘SQL’.

Oracle

NULLIF(expr1, expr2)

• expr1: The first expression that NULLIF() will evaluate. It can be a constant, variable, or column of a table. This expression can be of any data type.
• expr2: The second expression that NULLIF() compares against the first one. Same as expr1, this can be a constant, variable, or column and can be of any data type. If expr1 equals expr2, NULLIF() returns a NULL value.

Example

SELECT NULLIF('Apple', 'Apple') FROM dual;

Output

(No result returned or NULL)

Explanation

The NULLIF function in Oracle compares two expressions. If they are equal, NULLIF returns a NULL value. In this specific example, the two strings ‘Apple’ and ‘Apple’ are compared. Since they are equal, the function returns NULL.

SQLite

NULLIF(expr1, expr2)

• expr1: The first expression that SQL compares in the NULLIF function. This can be any SQL valid expression - a constant, a column of a table, a function, or an algebraic operation between constants and column values.
• expr2: The second expression with which the first one is compared. Like expr1, it can also be any valid SQL expression. If the evaluated result of expr1 and expr2 are equal, NULLIF will return a NULL value. If not, it will return the result of expr1.

Example

SELECT NULLIF('A', 'A'), NULLIF('A', 'B');

Output

NULL | 'A'

Explanation

In this example, the NULLIF function compares two values. If the two values are the same, it returns NULL. If they are different, it returns the first value. Thus, NULLIF('A', 'A') returns NULL and NULLIF('A', 'B') returns A.