Skip to main content

NULLIF

Accepts two arguments and returns a NULL value if they are equal, otherwise the first argument is returned.

The NULLIF function is equvalent to CASE WHEN expression1 = expression2 THEN NULL ELSE expression1 END

NULLIF is useful with aggregate functions such as AVG, MAX, MIN, SUM, and COUNT, where the result set contains special values that are not NULL, but that you want to treat as NULL for the purposes of aggregation.

See also:
[SELECT] [COALESCE] [IFNULL]

Syntax​

SELECT NULLIF(expression1, expression2) FROM <multipartIdentifier>;

Arguments​

expression
Two expressions of any data type which resolve to a NULL or non NULL value.

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 defines a collating function then BINARY collation is used.

Return Value(s)​

Returns NULL if the two expression arguments resolve to the same value, otherwise the first value is returned.

Examples​

Count records where a field is not equal to a given value​

SELECT COUNT(NULLIF(name, 'controller-0'))
FROM google.compute.instances 
WHERE project = 'stackql-demo' 
AND zone = 'australia-southeast1-a';
/*
equivalent to: 
SELECT COUNT(*) FROM google.compute.instances 
WHERE name != 'controller-0';
*/

For more information, see https://www.sqlite.org/lang_corefunc.html#nullif