ISNULL in SQL: Syntax, Examples and NULL Handling

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

ISNULL in SQL is a function that replaces NULL with a value you specify. In Microsoft SQL Server and T-SQL, ISNULL(check_expression, replacement_value) returns the replacement whenever the first argument is NULL [1]. Other dialects either lack the function or use it differently, so the same name can mean two different things depending on where you run your query.

Quick Answer

ISNULL(check_expression, replacement_value) takes exactly two arguments and returns the replacement when the first is NULL [1].
It is a T-SQL and SQL Server function. MySQL has IFNULL, Oracle has NVL, and standard SQL uses COALESCE.
ISNULL returns the same data type as check_expression, so the replacement must be implicitly convertible to that type [1].
Do not use ISNULL to test for NULL values. Use IS NULL with a space between the keywords [1].
COALESCE and CASE WHEN ... IS NULL produce the same replacement result and work across more database engines.

Syntax

The T-SQL signature is:

ISNULL ( check_expression , replacement_value )

Argument	Required?	Meaning
`check_expression`	Yes	The expression tested for NULL. It can be of any type [1].
`replacement_value`	Yes	The value returned when `check_expression` is NULL. It must be implicitly convertible to the type of `check_expression` [1].

The return type matches check_expression. If you pass a literal NULL as check_expression, ISNULL returns the data type of replacement_value. If you pass a literal NULL and no replacement, it returns an int [1].

How It Works

ISNULL evaluates the first argument once. If that value is NULL, the function returns the second argument. If the first argument holds any non-NULL value, the function returns it unchanged. There is no branching on the data itself, only on whether the value is missing.

This matters because NULL is not a value. It is the absence of a value, so comparisons like discount = NULL never evaluate to true. That is why the function exists as a separate tool from the IS NULL predicate. ISNULL substitutes a usable value so arithmetic, concatenation, and aggregation can proceed. IS NULL answers a yes-or-no question about whether a value is missing [1].

The dialect split is the part that trips people up. SQL Server and T-SQL ship ISNULL as a two-argument replacement function [1]. MySQL uses IFNULL for the same job. Oracle uses NVL. PostgreSQL does not have ISNULL as a replacement function at all. Standard SQL, and every major engine, supports COALESCE, which accepts two or more arguments and returns the first non-NULL one. If you want one query that runs everywhere, reach for COALESCE or an explicit CASE expression.

Worked Example

The orders table below has six rows. Three customers have a discount, and three have NULL in the discount column.

order_id	customer	discount
1	Alice	0.10
2	Bob	NULL
3	Carol	0.15
4	Dan	NULL
5	Eve	0.20
6	Frank	NULL

This query replaces each NULL discount with 0.0, using both COALESCE and an explicit CASE expression so you can compare the two approaches:

SELECT order_id, customer, discount, COALESCE(discount, 0.0) AS discount_or_zero, CASE WHEN discount IS NULL THEN 0.0 ELSE discount END AS discount_case FROM orders;

The result was checked with an equivalent SQLite query. SQLite has no ISNULL replacement function, so COALESCE and CASE stand in for it here.

order_id	customer	discount	discount_or_zero	discount_case
1	Alice	0.10	0.10	0.10
2	Bob	NULL	0.0	0.0
3	Carol	0.15	0.15	0.15
4	Dan	NULL	0.0	0.0
5	Eve	0.20	0.20	0.20
6	Frank	NULL	0.0	0.0

Both columns agree on every row. Rows 2, 4, and 6 had NULL discounts and now show 0.0. Rows 1, 3, and 5 kept their original values untouched. On SQL Server you would write ISNULL(discount, 0.0) and get the identical output.

More Examples

Replacing a NULL string. The Microsoft documentation shows ISNULL replacing a NULL Color value with the string None [1]. The same pattern works for any text column where a missing value should read as a label instead of blank.

Replacing a NULL number in an aggregate. The documentation also shows substituting 50 for NULL entries in a Weight column before averaging [1]. Without the substitution, the average ignores those rows entirely, which changes the denominator.

Replacing a NULL quantity with zero. In the AdventureWorks example, ISNULL(MaxQty, 0.00) shows 0.00 whenever the maximum quantity is NULL [1]. This keeps numeric columns consistent for downstream reporting.

Finding NULLs the right way. To list rows where a value is missing, use the predicate, not the function:

SELECT * FROM orders WHERE discount IS NULL;

Note the space between IS and NULL. This returns Bob, Dan, and Frank from the sample table [1].

A portable version. If your query needs to run on more than one engine, write it with COALESCE:

SELECT order_id, customer, COALESCE(discount, 0.0) AS discount_or_zero FROM orders;

This returns the same six rows and the same replaced values shown above.

Errors and How to Fix Them

Wrong argument count. ISNULL takes exactly two arguments [1]. Passing one argument or three raises an error. If you need to check several columns in order, use COALESCE, which accepts a list.

Type conversion failures. The replacement value must be implicitly convertible to the type of check_expression [1]. Passing a string where the checked column is numeric can fail or silently convert. Match the types.

Using ISNULL as a predicate. In SQL Server, writing WHERE ISNULL(discount) to find missing discounts is an error. That is the job of IS NULL [1]. The function needs a replacement value and returns a value, not a boolean.

Assuming ISNULL works the same everywhere. Running the two-argument ISNULL against MySQL, Oracle, or PostgreSQL fails. Oracle and PostgreSQL have no ISNULL function, and MySQL's ISNULL(expr) takes a single argument and returns 1 or 0 to test for NULL. Use IFNULL, NVL, or COALESCE as appropriate for the engine.

Forgetting the space in IS NULL. ISNULL and IS NULL are different tokens. One is a function, the other is a predicate [1]. Mixing them up produces either an error or the wrong result set.

Common Mistakes

Using ISNULL to search for NULLs. Use IS NULL instead, with the space [1]. The function replaces NULL, it does not detect it for filtering.
Expecting ISNULL to work in every database. It is a SQL Server and T-SQL function [1]. On other engines, use COALESCE or the local equivalent.
Passing a replacement of the wrong type. The replacement must be implicitly convertible to the checked expression's type [1]. Convert explicitly when in doubt.
Replacing NULLs before an aggregate without thinking. Substituting 0 changes an average because the row now counts. Decide whether you want the row included or excluded.
Confusing ISNULL with COALESCE argument behavior. ISNULL takes two arguments [1]. COALESCE takes a list and returns the first non-NULL value.
Replacing NULLs in a column you later filter on. If you replace NULL with 0 in a SELECT and then filter on that alias, you may hide rows you meant to keep.

Limitations

ISNULL only handles two arguments. If you need to fall back through several columns, you have to nest calls or switch to COALESCE. Nesting gets hard to read quickly, and each nested call adds another type-conversion check.

The function also hides missing data. Replacing NULL with 0 or an empty string makes a query run, but it erases the distinction between "the value is zero" and "we do not know the value." For analysis, that difference often matters. If you replace NULLs early in a pipeline, downstream steps lose the ability to count how many values were missing. Keep the original column available when the count of missing values is itself a finding.

Frequently Asked Questions

What does ISNULL do in SQL?

ISNULL replaces NULL with a value you specify. It takes a check expression and a replacement value, returning the replacement when the check expression is NULL and the original value otherwise [1]. It is most common in SQL Server and T-SQL.

Is ISNULL the same as IS NULL?

No. ISNULL is a two-argument function that substitutes a value for NULL [1]. IS NULL is a predicate that tests whether a value is NULL and returns true or false. The space between the keywords is the difference.

Does ISNULL work in MySQL or PostgreSQL?

No. ISNULL as a replacement function is specific to SQL Server and T-SQL [1]. MySQL uses IFNULL, Oracle uses NVL, and PostgreSQL relies on COALESCE. COALESCE works in all of them and is the safest portable choice.

What is the difference between ISNULL and COALESCE?

ISNULL accepts exactly two arguments and returns the second when the first is NULL [1]. COALESCE accepts two or more arguments and returns the first non-NULL value in the list. COALESCE is standard SQL and runs on more engines, while ISNULL is shorter and specific to T-SQL.

Can ISNULL change the data type of my result?

The return type matches check_expression [1]. If you pass a literal NULL as the check expression, the function returns the data type of the replacement value instead. When both arguments are literals and no replacement is given, it returns an int [1]. Watch for this when the checked column is numeric and the replacement is text.

References

ISNULL (Transact-SQL) - SQL Server | Microsoft Learn