SQL Reference ยท Scalar Functions
SQL IFNULL(), NVL() and ISNULL()
Reference for the vendor versions of COALESCE: IFNULL in SQLite and MySQL, NVL in Oracle, ISNULL in SQL Server, and why COALESCE is the portable choice.
Every database needs a way to say "use this value if that one is NULL", and before COALESCE was standardized each vendor invented its own two-argument function: IFNULL (MySQL, SQLite), NVL (Oracle), and ISNULL (SQL Server). They all do the same basic job: return the first argument unless it is NULL, in which case return the second.
New code should prefer COALESCE, which is standard and works everywhere. This page helps you read existing code and translate it. Beware that ISNULL also exists in MySQL with a different meaning.
SELECT IFNULL(email, 'unknown') FROM employees; -- SQLite, MySQL
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
IFNULL(expr, replacement) -- SQLite, MySQL NVL(expr, replacement) -- Oracle ISNULL(expr, replacement) -- SQL Server COALESCE(expr, replacement) -- standard, all databases
| Part | Description |
|---|---|
expr |
The value to test. |
replacement |
Returned when expr is NULL. |
Returns: expr if it is not NULL, otherwise replacement.
Dialect differences
| Database | Function | Notes |
|---|---|---|
| SQLite | IFNULL(a, b) |
Same as COALESCE(a, b) with two arguments. |
| MySQL | IFNULL(a, b) |
Result type is the common type of both arguments. ISNULL(x) takes one argument and returns 1 or 0. |
| PostgreSQL | none | Use COALESCE. (ISNULL is a non-standard postfix test, not a function.) |
| SQL Server | ISNULL(a, b) |
The result takes the type and length of the first argument, so ISNULL(varchar_5_column, 'long text') is truncated to 5 characters. COALESCE does not truncate. |
| Oracle | NVL(a, b), NVL2(a, if_not_null, if_null) |
Evaluates both arguments; COALESCE short-circuits. The empty string is NULL. |
-- Oracle SELECT NVL(commission, 0) FROM employees; SELECT NVL2(commission, 'paid', 'none') FROM employees; -- SQL Server SELECT ISNULL(middle_name, '') FROM people; -- MySQL: one-argument test, not a default SELECT ISNULL(email) FROM employees; -- 1 or 0
Usage notes
- Prefer
COALESCE. It is standard, accepts any number of arguments and short-circuits. - Truncation in SQL Server.
ISNULLcoerces the replacement to the first argument's type; this is the most common bug when porting fromCOALESCE. - Only
NULLis replaced. Empty strings and zeros pass through unchanged. - Oracle's
NVL2has three arguments and distinguishes "has a value" from "has none"; the portable form isCASE WHEN x IS NOT NULL THEN a ELSE b END. - Performance. Wrapping an indexed column in any of these in a
WHEREclause usually disables the index. - Name clash. A bare
ISNULLin MySQL tests forNULL; in SQL Server it replaces. Check which database you are in.
Related
- COALESCE(), NULLIF(), IS NULL, CASE
- Lessons: COALESCE and NULLIF, SQL dialects