SQLite 3 reference · Conditional and Type Functions

SQLite NULLIF() Function

Return NULL if two values are equal, otherwise the first value. Often used to avoid division by zero.

SQLite 3

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

nullif(X, Y)

Parameter	Description
`X`	The value.
`Y`	The value that should become `NULL`.

NULL if X = Y, otherwise X.

Equal values become NULLTry it yourself

SELECT nullif(5, 5) AS same, nullif(5, 3) AS different;

Output

same | different
NULL | 5

Avoid dividing by zeroTry it yourself

SELECT name, stock, round(100.0 / nullif(stock, 0), 2) AS pct_per_unit
FROM products
WHERE product_id IN (2, 3);

Output

name | stock | pct_per_unit
Clean Code | 15 | 6.67
The Pragmatic Programmer | 0 | NULL

COALESCE() · Return the first argument that isn't NULL. The standard way to replace NULL with a default value.
IFNULL() · Return the first argument if it isn't NULL, otherwise the second. A two-argument coalesce().
SQL NULL Values · tutorial