SQL NULL Functions Explained with Examples

A column nobody filled in comes back empty, and putting that on a page or into a sum gives you nothing usable. Four functions hand back a value of your choosing in its place: IFNULL in MySQL, ISNULL in SQL Server, NVL in Oracle, and COALESCE on all four engines, because COALESCE is the one the SQL standard defines. That empty cell holds a NULL, and each of the four functions keeps it off the page.

What NULL means, and the four functions that replace it

NULL marks the absence of data. It is not zero, not an empty string, and not a placeholder somebody typed. Because it stands for a value nobody knows, any comparison against it evaluates to NULL as well[1], which is why a filter, a join and a sort all treat it as a case of its own. SQL NULL values works through that side of it; this page is about the four functions that put a usable value in a NULL's place.

Three of the four belong to one engine each.

Two names in that table are traps. MySQL also has an ISNULL(), but it takes one argument and returns 1 or 0[6], so it tests rather than replaces. PostgreSQL has neither IFNULL() nor NVL()[4], which leaves COALESCE() as the only one of the four it accepts.

Every example below runs against three students, one of whom has no age and one of whom has no grade:

CREATE TABLE Students (
    ID    INT PRIMARY KEY,
    Name  VARCHAR(20),
    Age   INT,
    Grade CHAR(1)
);

INSERT INTO Students VALUES
    (1, 'Alice', 20,   NULL),
    (2, 'Bob',   NULL, 'B'),
    (3, 'Carol', 22,   'C');

IFNULL() in MySQL

IFNULL() takes two arguments and returns the first one when it is not NULL, the second when it is[2]:

SELECT Name, IFNULL(Age, 'Unknown') AS Age FROM Students;

The column holds numbers and the replacement is a word, which MySQL settles by widening the result to the more general of the two types, in the order STRING, REAL, INTEGER[2]. Both values come back as text, so a report can print the column as it is. A column you plan to sort or add up wants a number in the second argument instead.

ISNULL() in SQL Server

ISNULL() is SQL Server's two-argument replacement, and it returns the same data type as the expression it checks[3], converting the replacement to that type. Age is an integer column, so a word in the second argument asks for a conversion SQL Server cannot make:

SELECT Name, ISNULL(Age, 'Not Specified') AS Age FROM Students;

That statement fails on the conversion instead of returning rows, which is why a text default over a numeric column belongs to COALESCE rather than to ISNULL. A replacement of the column's own type goes straight through:

SELECT Name, ISNULL(Age, 0) AS Age FROM Students;

COALESCE() on all four engines

SELECT Name, COALESCE(Grade, 'Unknown') AS Grade FROM Students;

Alice has no Grade, so the second argument answers for her row. COALESCE() takes a list rather than a pair, returns the first argument that is not null, and returns null only when every argument is null[4]. COALESCE comes from the SQL standard, which is why all four engines carry it, and its arguments have to be convertible to one common data type[4].

Between a query that runs everywhere and one that reads a few characters shorter, write COALESCE: the engine-specific spellings buy nothing that COALESCE does not already do, and each of them pins the query to one database.

NVL() in Oracle

NVL() is the two-argument replacement in Oracle, and Oracle converts one argument to the data type of the other, returning an error when it cannot[5]. Over the Age column with a text default and no wrapper, that rule sends Oracle looking for a number in the word Unknown. Oracle answers with the conversion error rather than with the rows. TO_CHAR() around the column makes both arguments text, and the wrapped form is the one Oracle's own reference writes:

SELECT Name, NVL(TO_CHAR(Age), 'Unknown') AS Age FROM Students;

IS NULL vs = NULL

IS NULL is the only test for a missing value. MySQL's manual is blunt about the alternative: comparison operators such as equals and not-equal cannot be used to test for NULL[1]. Written with an equals sign, the condition is not false, it is NULL, so the row is neither matched nor rejected:

SELECT Name FROM Students WHERE Grade = NULL;

No rows come back, not even Alice's, whose Grade is exactly what the query was looking for. Swap the operator for IS NULL and her row appears. To treat two NULLs as equal instead, the standard writes IS NOT DISTINCT FROM, which MySQL spells as the null-safe equality operator[6]. An SQL CASE expression branches on NULL inside the SELECT list in one pass, where a replacement function would need nesting.

NULLs in aggregates, COUNT() and AVG()

Aggregate functions ignore NULL values[7], with one exception: COUNT(*) returns a count of the rows retrieved whether or not they contain NULL[7], while COUNT(column) counts only the values in it that are not NULL. Reading one for the other is where a miscount starts:

SELECT COUNT(*) AS rows_total, COUNT(Age) AS ages_known, AVG(Age) AS avg_age
FROM Students;

Three rows, two of them with an age, and an average of 21 rather than 14, because AVG() added 20 and 22 and divided by 2. Replace the NULL first, with IFNULL(Age, 0) or COALESCE(Age, 0), and the same average comes out as 14, which is the number you want only if a missing age really means zero. Counting the gaps takes COUNT(*) minus COUNT(Age), or a WHERE Age IS NULL. An aggregate beside another column also needs a GROUP BY clause, and the SQL COUNT, AVG and SUM tutorial works through the aggregates themselves.

NULLs in string concatenation

Concatenation is where the four engines genuinely disagree, so this is the one place to check yours before the report ships.

Each engine documents its own row. MySQL's CONCAT() returns NULL if any argument is NULL[8]. SQL Server's CONCAT implicitly converts null values to empty strings[9]. PostgreSQL's concat ignores NULL arguments[10]. Oracle's concatenation operator returns the surviving operand, because null can result only from the concatenation of two null strings[11]. Running the MySQL version over the three students shows what that costs:

SELECT Name, CONCAT(Grade, ' (final)') AS GradeLabel FROM Students;

Alice's label is gone entirely, and the word "(final)" went with it. The pipe operator carries a trap of its own. Oracle's own page recommends wrapping a nullable expression in NVL() rather than relying on the surviving-operand behavior, in case a later release changes it[11]. In MySQL the same two characters mean logical OR, and they concatenate only when the PIPES_AS_CONCAT SQL mode is enabled[12]. Replace the value before you concatenate it and none of the four rows above can surprise you.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Test NULL replacements in DbSchema

NULL bugs come from an assumption that holds on one engine and fails on the next, and the table above is a short list of where that happens. Open the SQL Editor in DbSchema on the connection the query will ship on, paste the variants one under the other, and click Execute Query to read each result grid under its statement.

Testing needs rows that hold NULL to begin with. DbSchema's Relational Data Editor opens a table together with the tables related to it, and a cell edited there reaches the database when you click Commit, so the rows the test needs are a few clicks rather than an UPDATE statement. Rows you insert this way change the database, while the diagram and the editors DbSchema opens over it are kept in the model file.

Practice exercises

1. List the students whose Grade is missing.
2. Return every Grade, with "Not Assessed" in place of the missing one.
3. Compute the average Age over the students who have one, then over all three.
4. Build one column that reads "Alice, 20", with "Unknown" where the age is missing.

The four spellings are one query apart once you are connected. Download DbSchema at https://dbschema.com/download.html, open the SQL Editor on the database the query will run on, and try your replacement over the same rows before the report reaches anyone. The SQL Editor is in the free Community Edition, and the Relational Data Editor that sets up the NULL rows for the test is in Pro.

FAQs

Can NULL take part in arithmetic?

An expression with NULL in it returns NULL rather than an error, so Age + 1 is NULL for the student who has no age. Wrap the column rather than the whole expression, as in IFNULL(Age, 0) + 1, and every row comes back as a number.

How do I default a NULL to another value?

COALESCE(column, replacement) is the portable answer, and it reads its arguments from the left. It stops at the first one that is not NULL, so arguments to the right of that one are never evaluated[4].

Sources

1. MySQL 8.4 Reference Manual - Working with NULL Values
2. MySQL 8.4 Reference Manual - Flow Control Functions
3. ISNULL (Transact-SQL) - Microsoft Learn
4. PostgreSQL 17 - Conditional Expressions
5. Oracle Database 26 SQL Language Reference - NVL
6. MySQL 8.4 Reference Manual - Comparison Functions and Operators
7. MySQL 8.4 Reference Manual - Aggregate Function Descriptions
8. MySQL 8.4 Reference Manual - String Functions and Operators
9. CONCAT (Transact-SQL) - Microsoft Learn
10. PostgreSQL 17 - String Functions and Operators
11. Oracle Database 26 SQL Language Reference - Concatenation Operator
12. MySQL 8.4 Reference Manual - Logical Operators