SQLite NULLIF() Function
The SQLite NULLIF() function compares two expressions and returns NULL if they are equal. Otherwise, it returns the first expression.
The NULLIF() function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If neither argument to NULLIF() defines a collating function then the BINARY collating function is used.
Syntax
NULLIF(expr1, expr2)
Parameters
expr1, expr2 |
Required. Specify two expressions to be compared. |
Return Value
Returns NULL if expr1 and expr2 are equal. Otherwise, returns expr1.
Example 1:
The example below shows the usage of NULLIF() function.
SELECT NULLIF('John', 'John'); Result: NULL SELECT NULLIF('John', 'John_2005'); Result: 'John' SELECT NULLIF(123, 456); Result: 123 SELECT NULLIF(123, 123); Result: NULL SELECT NULLIF(NULL, NULL); Result: NULL
Example 2:
Consider a database table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
In the query below, the NULLIF() function is used to check the City column records against 'Paris'. It means if the employee city is Paris, it returns NULL. Otherwise, it returns the column value.
SELECT *, NULLIF(City, 'Paris') AS Result FROM Employee;
This will produce the result as shown below:
EmpID | Name | City | Age | Salary | Result |
---|---|---|---|---|---|
1 | John | London | 25 | 3000 | London |
2 | Marry | New York | 24 | 2750 | New York |
3 | Jo | Paris | 27 | 2800 | NULL |
4 | Kim | Amsterdam | 30 | 3100 | Amsterdam |
5 | Ramesh | New Delhi | 28 | 3000 | New Delhi |
6 | Huang | Beijing | 28 | 2800 | Beijing |
❮ SQLite Functions