Introduction

When you move SQL code between database engines, NULL handling is one of the first places where syntax diverges. SQLite does not implement Oracle's NVL() or SQL Server's ISNULL() function, but it gives you the same capability through IFNULL() and COALESCE().

SQLite Functions That Replace NVL() and ISNULL()

The closest SQLite equivalent depends on how many fallback values you need.

IFNULL(x, y) takes exactly two arguments and returns y when x is NULL. If x is not NULL, it returns x.

COALESCE(x, y, z, ...) is more general. It checks each argument from left to right and returns the first value that is not NULL. This makes it the best replacement when an old query chains several possible values together.

In practice:

• Oracle NVL(col, 'fallback') maps naturally to SQLite IFNULL(col, 'fallback')
• SQL Server ISNULL(col, 0) usually maps to SQLite IFNULL(col, 0)
• Standard SQL COALESCE(col1, col2, col3, 'unknown') works in SQLite as written

If you want the most portable SQL, prefer COALESCE(). It is part of the SQL standard and is widely supported beyond SQLite.

Query Examples

Suppose you have a table where contact details may be missing.

1CREATE TABLE contacts (
2    id INTEGER PRIMARY KEY,
3    name TEXT NOT NULL,
4    phone TEXT,
5    email TEXT,
6    city TEXT
7);
8
9INSERT INTO contacts (name, phone, email, city) VALUES
10    ('Ava', NULL, '[email protected]', 'Toronto'),
11    ('Ben', '555-0101', NULL, NULL),
12    ('Chen', NULL, NULL, 'Vancouver');

If you only need one fallback value, IFNULL() is concise:

1SELECT
2    name,
3    IFNULL(phone, 'no phone on file') AS phone_label
4FROM contacts;

That returns the phone number when it exists and the text 'no phone on file' otherwise.

If you want several fallback choices, use COALESCE():

1SELECT
2    name,
3    COALESCE(phone, email, 'no direct contact') AS best_contact
4FROM contacts;

This query first tries phone, then email, and finally the string 'no direct contact'.

You can also use COALESCE() in calculations:

1CREATE TABLE invoices (
2    id INTEGER PRIMARY KEY,
3    subtotal REAL,
4    tax REAL,
5    shipping REAL
6);
7
8SELECT
9    id,
10    COALESCE(subtotal, 0) +
11    COALESCE(tax, 0) +
12    COALESCE(shipping, 0) AS total
13FROM invoices;

Without the COALESCE() calls, a single NULL value would make the whole sum evaluate to NULL.

IS NULL Is Not the Same Thing

A common point of confusion is the difference between the IS NULL operator and ISNULL() in other database systems. In SQLite, IS NULL is a condition used for testing whether a value is NULL. It does not replace missing values.

SELECT name
FROM contacts
WHERE phone IS NULL;

That query filters rows. It does not provide a default string or number. When porting code, keep the distinction clear:

• use IS NULL to test
• use IFNULL() or COALESCE() to substitute

Porting Tips from Other Databases

When rewriting existing SQL for SQLite, these translations are usually safe:

1-- SQL Server
2SELECT ISNULL(discount, 0) FROM orders;
3
4-- SQLite
5SELECT IFNULL(discount, 0) FROM orders;

1-- Oracle
2SELECT NVL(manager_name, 'unassigned') FROM employees;
3
4-- SQLite
5SELECT IFNULL(manager_name, 'unassigned') FROM employees;

-- Multi-step fallback in many engines
SELECT COALESCE(nickname, first_name, 'anonymous') FROM users;

If you already have two-argument calls, IFNULL() is readable and idiomatic in SQLite. If your query may grow later or must stay portable across engines, COALESCE() is usually the better long-term choice.

Common Pitfalls

The first pitfall is assuming SQLite supports every vendor-specific function name. It does not. A query using Oracle NVL() or SQL Server ISNULL() will usually need to be rewritten.

The second pitfall is confusing empty strings with NULL. In SQLite, '' is still a real string value, so COALESCE('', 'fallback') returns the empty string, not 'fallback'. If you want blank strings treated as missing, combine NULLIF() with COALESCE().

SELECT COALESCE(NULLIF(phone, ''), 'no phone on file')
FROM contacts;

The third pitfall is mixing incompatible types without noticing. SQLite is flexible about types, but your application code may not be. If one branch returns text and another returns a number, the result may be harder to consume cleanly.

Summary

• SQLite does not provide Oracle NVL() or SQL Server ISNULL() as function names
• 'IFNULL(x, y) is the direct SQLite replacement for two-argument null substitution'
• 'COALESCE(x, y, z, ...) returns the first non-NULL value and is more portable'
• 'IS NULL is only for testing whether a value is missing'
• Use NULLIF() together with COALESCE() when blank strings should count as missing data