# ISNULL in SQL: Syntax, Examples and NULL Handling

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:

```sql
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](/blog/data-analysis/using-case-in-sql).

## 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:

```sql
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:

```sql
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`:

```sql
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

1. [ISNULL (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-ver17)

## Further Reading

- [Codd EF (1970). A relational model of data for large shared data banks. Communications of the ACM](https://doi.org/10.1145/362384.362685)
- [PostgreSQL Documentation: SELECT](https://www.postgresql.org/docs/current/sql-select.html)
- [SQLite: SQL As Understood By SQLite](https://www.sqlite.org/lang.html)
- [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510)
- [PostgreSQL Tutorial: The SQL Language](https://www.postgresql.org/docs/current/tutorial-sql.html)

## Related Articles

- [SQL NOT IN Operator: Syntax and Examples](/blog/data-analysis/sql-not-in-operator)
- [SQL RIGHT Function: Syntax, Examples and Use Cases](/blog/data-analysis/sql-right-function-syntax-examples)
- [Using CASE in SQL: Syntax and Examples](/blog/data-analysis/using-case-in-sql)
- [SQL SELECT Statement: Syntax, Clauses and Examples](/blog/data-analysis/sql-select-statement-syntax-examples)
- [SQL DECODE Function: Syntax and Examples](/blog/data-analysis/sql-decode-function-syntax-examples)