SQL COALESCE, ISNULL, NULLIF in SQL Server, Oracle and PostgreSQL

By: Andrea Gnemmi | Updated: 2022-03-21 | Comments (2) | Related: > SQL Server vs Oracle vs PostgreSQL Comparison

Problem

Sometimes a need arises to evaluate if a variable, expression or a column has no value associated so that it is NULL. The COALESCE() and ISNULL() functions can be used for this and we will look at examples from SQL Server, Oracle and PostgreSQL.

Solution

In this tutorial, we will review the different syntax and alternatives of COALESCE() and ISNULL() as well as their alternatives in SQL Server, Oracle and PostgreSQL.

This SQL tutorial will use the Chinook sample database available in multiple RDBMS formats. Chinook is a simulation of a digital media store, with sample data. Download the version you need and all the code to insert data.

Example SQL Server Function Syntax

In SQL databases we have the COALESCE() expression and the ISNULL() function in order to evaluate a NULL expression in stored procedures, SELECT with WHERE clause logic, SQL DELETE and scripts. This code has some differences that we will explore in the following examples.

SQL Server COALESCE Function

First of all, let's review an example SQL query with COALESCE(). Suppose that we need to return data from the Customer table and if the Company column is NULL, we need to return a specific value.

SELECT coalesce(company,'No Company') as Company
FROM Customer

This is a pretty typical example of a SQL COALESCE function!

Remember that COALESCE() returns the first expression that is not evaluated as NULL and it can have more than 2 arguments. Another very important thing to bear in mind is the result data type that we will have from evaluating with COALESCE(), since it is an expression it will take the data type of value with the highest precedence, as stated in the documentation.

Let's demonstrate another example adding the fax column. If a value exists for company, it will show that, if not and a value exists for fax it will show that and if not it will show "No Company" in the following example.

SELECT coalesce(company,fax,'No Company') as Company
FROM Customer

So far all OK, it puts the fax number if company is null or "No Company" if both columns are null, but what happens if we try to add a different result data type such as an integer with varchar data?

SELECT coalesce(company,fax,'No Company',1) as Company
FROM Customer

In the result set you can see we received an error as it is taking the data type as the last entry which is an integer and it is not able to convert the nvarchar column Company to integer.

SQL Server ISNULL Function

Let's see how ISNULL() behaves in the same examples, the main difference is that with this function we can have only two arguments.

SELECT isnull(company,'No Company') as Company
FROM Customer

So far it's the same, now let's see modifying the second argument and using an integer like in the COALESCE() example.

SELECT isnull(company,1) as Company
FROM Customer

This time it works, that's because ISNULL(), being a function, gets the result data type from the first argument, in our case Company columns which is nvarchar and so the integer value of 1 can be converted into this data type.

Example Oracle Function Syntax

In Oracle we have two functions that do the same job: COALESCE() and NVL(), the latter being an old proprietary function of PL/SQL that behaves like ISNULL() in SQL Server.

Oracle COALESCE Function

Let's try the same examples.

SELECT coalesce(company,'No Company') as Company
FROM CHINOOK.Customer;

Nothing different from the same expression in SQL Server, let's see how the COALESCE() function behaves in Oracle regarding the result data type (yes in Oracle it is a function and not an expression like in SQL Server).

Let's try the same examples we did in SQL Server.

SELECT coalesce(company,fax,'No Company') as Company
FROM chinook.Customer;

Same behavior as in SQL Server, now let's add a number and see.

SELECT coalesce(company,fax,'No Company',1) as Company
FROM chinook.Customer;

Almost the same error, but here with a difference. Since the function is assuming the result will be a CHAR data type, adding a NUMBER data type returns an error, so in this case the result data type is not the number but CHAR, so it is the first argument and not the last, which is the opposite from SQL Server.

Oracle NVL()

Now let's try the NVL() function. In many ways this is similar to ISNULL() in SQL Server, it has only two arguments and returns the first non-NULL.

Same example as before.

SELECT NVL(company,'No Company') as Company
FROM chinook.Customer;

Again, exactly the same as in SQL Server, now the example using a number as the second argument.

SELECT NVL(company,1) as Company
FROM chinook.Customer;

And again, the same behavior as in SQL Server.

Oracle NVL2()

In Oracle we also have NVL2() that accepts three parameters, let's see an example.

SELECT NVL2(company,fax,'No Company') as Company
FROM chinook.Customer;

And we try again using a different data type.

SELECT NVL2(company,fax,1) as Company
FROM chinook.Customer;

And again, with NVL2() no error is returned, but doing the same exact example with COALESCE().

SELECT coalesce(company,fax,1) as Company
FROM chinook.Customer;

Returns the same error as before.

Example PostgreSQL Function Syntax

In order to perform the same conditional function in PostgreSQL we have only the standard SQL function COALESCE().

SELECT coalesce("Company",'No Company') as company
FROM "Customer"

Exactly the same as in SQL Server and Oracle.

Let's try the following first with VARCHAR value.

SELECT coalesce("Company","Fax",'No Company') as company
FROM "Customer"

With obviously the same results as in the previous examples.

Let's try with an INTEGER result data type.

SELECT coalesce("Company","Fax",'No Company',1) as company
FROM "Customer"

And again, with a similar error result, this time saying that the varchar and integer data types cannot be matched. Please notice that an error is returned from COALESCE() in all three RDBMS even if the last argument will be never returned, as obviously 'No Company' is NOT NULL!

NULLIF Function Examples

There is another expression/function present in all three RDBMS that is used with NULL and that is NULLIF(). This last function has two arguments and returns NULL if both arguments are equal, otherwise it returns the first argument. Let's review some examples!

SQL Server

We need to report all customers that have made 2 consecutive purchases with the same total amount, unfortunately we noticed that not all invoice numbers are consecutive.

;with invoice1 as 
  (select invoiceid,customerid, total, ROW_NUMBER() over (partition by CUSTOMERid order by invoicedate ASC) as row_num
   from invoice)
,invoice2 as
  (select invoiceid,customerid, total, ROW_NUMBER() over (partition by CUSTOMERid order by invoicedate ASC) as row_num
   from invoice)
 
SELECT invoice1.invoiceid,
   invoice1.customerid,
   FirstName+' '+LastName as customer, 
   invoice1.total,
   nullif(invoice1.total,invoice2.total) as Equal_Total_Prev_Invoice
FROM invoice1
INNER JOIN customer on customer.customerid=invoice1.customerid
LEFT OUTER JOIN invoice2 on invoice1.CustomerId=invoice2.CustomerId and invoice1.row_num-1=invoice2.row_num
WHERE nullif(invoice1.total,invoice2.total) is null
ORDER BY invoice1.CustomerId

So here we have used two CTEs with windowing functions in order to have the exact previous invoice and compare it with the following one, then using SELECT NULLIF() to compare the totals and return NULL in case they are equal.

A nice explanation of windowing functions on the three RDBMS can be found here: SQL Window Functions in SQL Server, Oracle and PostgreSQL.

Oracle

Let's review the same example in Oracle with the following query:

with invoice1 as 
 (select invoiceid,customerid, total, ROW_NUMBER() over (partition by customerid order by invoicedate asc) as row_num
  from chinook.invoice)
,invoice2 as
 (select invoiceid,customerid, total, ROW_NUMBER() over (partition by customerid order by invoicedate asc) as row_num
  from chinook.invoice)
 
SELECT 
   invoice1.invoiceid,
   invoice1.customerid,
   FirstName||' '||LastName as customer, 
   invoice1.total,
   nullif(invoice1.total,invoice2.total) as Equal_Total_Prev_Invoice
FROM invoice1
INNER JOIN chinook.customer on customer.customerid=invoice1.customerid
LEFT OUTER JOIN invoice2 on invoice1.CustomerId=invoice2.CustomerId and invoice1.row_num-1=invoice2.row_num
WHERE nullif(invoice1.total,invoice2.total) is null
ORDER BY invoice1.CustomerId;

The behavior is exactly the same.

PostgreSQL

Finally let's see how it works on PostgreSQL.

with invoice1 as 
 (select "InvoiceId","CustomerId", "Total", ROW_NUMBER() over (partition by "CustomerId" order by "InvoiceDate" asc) as row_num
   from "Invoice")
,invoice2 as
 (select "InvoiceId","CustomerId", "Total", ROW_NUMBER() over (partition by "CustomerId" order by "InvoiceDate" asc) as row_num
  from "Invoice")
 
SELECT invoice1."InvoiceId",
   invoice1."CustomerId",
   "FirstName"||' '||"LastName" as customer, 
   invoice1."Total",
   nullif(invoice1."Total",invoice2."Total") as "Equal_Total_Prev_Invoice"
FROM invoice1
INNER JOIN "Customer" on "Customer"."CustomerId"=invoice1."CustomerId"
LEFT OUTER JOIN invoice2 on invoice1."CustomerId"=invoice2."CustomerId" and invoice1.row_num-1=invoice2.row_num
WHERE nullif(invoice1."Total",invoice2."Total") is null
ORDER BY invoice1."CustomerId";

Again, the same exact behavior.

Conclusion

In this SQL tutorial, we have reviewed the SQL (structured query language) functions COALESCE(), ISNULL(), NULLIF() and how these work in SQL Server, Oracle and PostgreSQL. There are also other ways for checking NULL values such as the IS NULL clause and there are other complex functions in Oracle.

Next Steps

As usual links to the official documentation:
- SQL Server: Microsoft COALESCE (Transact-SQL)
- Oracle: Oracle COALESCE
- PostgreSQL: PostgreSQL Conditional Expressions
Some links to other tips regarding COALESCE() and ISNULL() or NULL comparisons:

About the author

Andrea Gnemmi is a Database and Data Warehouse professional with almost 20 years of experience, having started his career in Database Administration with SQL Server 2000.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-03-21

COALESCE(), ISNULL() , NULLIF() and alternatives in SQL Server, Oracle and PostgreSQL