PostgreSQL ISNULL: The Missing Function
Published: 2023-10-23 07:00:00

**Let’s figure out whether or not the ISNULL PostgreSQL function exists, why, and explore its alternatives to get the same functionality.**

---
Tools used in this tutorial
[DbVisualizer](https://www.dbvis.com/download/), top rated database management tool and SQL client.
The [PostgreSQL](https://www.postgresql.org/) database

---
Handling `NULL` values in the backend and frontend is cumbersome. Noone wants to fill their code with null checks – the database should perform such actions instead. Here is why most popular DBMSs provide features such as `ISNULL()` to handle them directly within SQL queries. However, not all of these functions and operators are standard SQL.

So, is `ISNULL` part of PostgreSQL? In this article, you will find it out and explore a PostgreSQL `ISNULL` example list.

### Is There a PostgreSQL ISNULL function?
Even though `ISNULL()` is a function present in some SQL dialects, including [T-SQL](https://learn.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-ver16) and [MySQL](https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_isnull), it is not part of the SQL language standard. This means that not all DBMSs support it. **In particular, PostgreSQL does not have a built-in** `ISNULL` **function**.

Since the function is not part of the standard SQL specification, its implementation and purpose change from DBMS to DBMS. Let’s now explore how it differs in T-SQL and MySQL.

In Microsoft SQL Server (T-SQL), the `ISNULL(a, b)` function returns `b` when `a` is `NULL`. In other words, it replaces a `NULL` with a specified replacement value. For example:

```
1 SELECT ISNULL(salary, 0) AS salary
2 FROM employee;
```
This function helps avoid logical inconsistencies and comes in handy when dealing with `NULL` handling.

In MySQL, `ISNULL(expr)` performs a logical test on the input parameter. It returns `1` if `expr` is `NULL`, or `0` otherwise. Thus, it takes a single argument and checks its value for `NULL`. For example:

```
1 SELECT name, surname
2 FROM employee
3 WHERE ISNULL(middle_name);
```

This function shares special behaviors with `IS NULL` and is generally used in conjunction with logical or conditional operators, such as `AND`, `OR`, or `IF`.

#### What Is the PostgreSQL Equivalent for ISNULL?

There are different PostgreSQL approaches that you can follow to achieve goals similar to those provided by `ISNULL`. These depend on the implementation of the function.

In PostgreSQL, the most common equivalent of the SQL Server function is `COALESCE`. The main difference between the two is that the first expects only two arguments, while the latter accepts n input parameters.

Thus, the syntax of COALESCE is: `COALESCE(a, b [, c ... ] )`. The function returns the first `non-NULL` value, from left to right.

Equivalent PostgreSQL ISNULL example:

```
1 SELECT COALESCE(salary, 0) AS salary
2 FROM employee;
```

Keep in mind that `COALESCE` is part of the standard SQL specification. That means that it is not only available in PostgreSQL but it is also present in all other SQL dialects, including T-SQL itself. Check out our [in-depth guide on `COALESCE`](https://www.dbvis.com/thetable/postgresql-coalesce-function-handling-null-value/) to learn more about it.

Another T-SQL's `ISNULL` equivalent in PostgreSQL is the [CASE operator](https://www.postgresql.org/docs/current/functions-conditional.html). Thanks to the `CASE` statement, you can implement IF/ELSE behavior. When used in combination with the `IS NULL` operator, this enables you to achieve the same functionality as `ISNULL(a, b)` in T-SQL:

```
1 CASE WHEN a IS NULL THEN b ELSE a END
```

PostgreSQL example:

```
1 SELECT
2 CASE WHEN salary IS NULL THEN 0 ELSE salary END AS salary
3 FROM employee;
```

When it comes to the MySQL meaning of `ISNULL`, the easiest way to get the same behavior is through `IS NULL`. The main difference is that the function in MySQL returns an integer, while `IS NULL` in PostgreSQL returns a boolean. At the same time, MySQL does not support the boolean data type, so the two expressions can be considered equivalent most of the time.

## Equivalent PostgreSQL ISNULL Example List
To better understand how to replace `ISNULL` in PostgreSQL, let’s take a look at some examples. To visually explore the results of example queries, we will adopt [DbVisualizer](https://www.dbvis.com/). This database client supports more than 50 languages and represents the perfect tool for connecting and comparing different database technologies.

#### Example 1: Replace NULLs with a default value
In T-SQL, `ISNULL` is mainly used to replace NULL values with placeholders. Dealing with `NULL` values in the backend or frontend can be time-consuming. Here is why addressing those values directly at the database layer makes everything easier and faster.

Consider the example below:

```
1 SELECT product_name, price, ISNULL(stock_level, 0) AS stock_level
2 FROM products;
```

This query returns the value of `stock_level` if it is not `NULL`, or `0` otherwise.

The PostgreSQL `ISNULL` example equivalent for that query is:

```
1 SELECT name, price, COALESCE(stock_level, 0) AS stock_level
2 FROM products;
```

Similarly, `COALESCE` also returns the non-NULL expression.

<br />
![Note the 0s in the "stock_level" column.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kwy9sx4s4cgrgsy4xfr0.png)
<figure><figcaption>Note the 0s in the "stock_level" column.</figcaption></figure>
<br />

Another way to achieve the same result is with a `CASE` statement:

```
1 SELECT name, price, CASE WHEN stock_level IS NULL THEN 0 ELSE
2 stock_level END AS stock_level
3 FROM products;
```
 
<br />
![Executing the equivalent query in DbVisualizer.
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jotln6u0o6ad0yyai1s4.png)
<figure><figcaption>Executing the equivalent query in DbVisualizer.</figcaption></figure>
<br />
Note that the result sets in the two images are the same.

### Example 2: Better read NULL values
`ISNULL()` in MySQL can be adopted to make it easier to check whether a column is `NULL` or not. Take a look at the example below:

```
1 SELECT name, ISNULL(stock_level) AS unknown_status
2 FROM products;
```
In this query, `unknown_status` will contain `1` if `stock_level` is `NULL`, and `0` otherwise. This `SELECT`-generated column makes it easier to read the results and keep track of the stock data status.

The PostgreSQL `ISNULL` example alternative is:

```
1 SELECT name, stock_level IS NULL AS unknown_status
2 FROM products;
```

<br />
![Note the "true" and "false" values in the results.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mwqd8n6u7vefzk8vzu97.png)
<figure><figcaption>Note the "true" and "false" values in the results.</figcaption></figure>
<br />

Similarly, the `IS NULL` operator will return a boolean representing the status of the stock level associated with each product.

Congrats! The absence of `ISNULL` in PostgreSQL is no longer a problem!

## Conclusion
In this guide, you learned that `ISNULL` is a non-standard feature that not all SQL dialects support. Although PostgreSQL does not have it, it is pretty simple to achieve equivalent functionality.

Using a tool that can connect to multiple databases simultaneously makes it way easier to write queries with the equivalent result. A full-featured database client like DbVisualizer makes all the difference here! In addition to connecting to dozens of DBMSs, this tool offers advanced query optimization functionality, and full support for all PostgreSQL features, including `COALESCE` and `CASE`. [Download DbVisualizer for free now!](https://www.dbvis.com/download/)

## FAQ
Let’s answer some questions related to the topic of the guide.

### What is the difference between ISNULL and COALESCE?
`ISNULL` is a function specific to the SQL dialect, while `COALESCE` is a standard SQL function. The former behaves differently depending on the specific implementation. The second always returns the first non-`NULL` value, regardless of the database system in use.

### Why doesn't PostgreSQL support ISNULL?
`ISNULL` in PostgreSQL does not exist. The reason is that the function is not part of the SQL standard, and PostgreSQL is not required to provide an implementation for it.

### Why do MySQL and SQL Server have the ISNULL function and PostgreSQL does not?
MySQL and SQL Server are not standards-compliant databases. They both have their own proprietary extensions and aspire to provide rich features to users. On the other hand, PostgreSQL is pretty much a standards-compliant database. The standard SQL language does not include the `ISNULL` function, so PostgreSQL skipped it.

### What is the difference between using IS NULL and = NULL in PostgreSQL?
`IS NULL` in PostgreSQL returns true when the value being checked is `NULL`. Instead, using = `NULL` will result in `NULL` because `NULL` is not equal to anything, not even itself. So, the first operator checks for nullity, while the second is often used by mistake.

### What is the difference between IS NULL and IS NOT NULL in PostgreSQL?
`IS NULL` is used in PostgreSQL to check if a value is `NULL`, while `IS NOT NULL` is used to check if a value is not `NULL`. These operators are commonly used in `WHERE` clauses or in conditions to filter or evaluate `NULL` values in the database.


## About the author
Antonello Zanini is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.