Home
/
Blog
/
COALESCE, NULLIF, and ISNULL function in SQL

COALESCE, NULLIF, and ISNULL function in SQL

In this article, we will review certain SQL functions and how they can be used. You will also check some code examples below. So here’s a brief overview:

COALESCE Function in SQL
NULLIF Function in SQL
ISNULL Function in SQL
SQL Coalesce vs Isnull
SQL Nullif vs IsNull
Isnull TSQL
T SQL Isnull vs Coalesce
Nullif SQL Server Example
What Does Coalesce Mean in SQL?
How to Use Coalesce in SQL?

COALESCE Function in SQL

COALESCE returns the first non-null parameter among all parameters passed to it. If all parameters are null then it’ll return null. The COALESCE() function in SQL is beneficial to manage NULL values. The NULL values are substituted with the user-entered value throughout the declaration value assessment procedure. The Coalesce function assesses the value in a particular hierarchy and consistently gives the first not the null value from the specified expression index. It gives the data type of value with the most elevated data type priority.

If all values are non-null, the output is classified as non-nullable. A value that includes a subquery is assessed and the subquery is considered twice. This output is in adherence with the SQL benchmark. In either possibility, various consequences can be produced between the primary evaluation and impending evaluations.

Its syntax is as follows.

COALESCE(arg1, arg2 [,argn])

Let’s take an example to get things clear. Suppose we have a customer table which is having contact no’s like office-phone, mobile-no, home-phone, and email. We want to give our sales guys a list of customers with the best available contact no. What I mean by “best available contact no” is if the office phone is not present in our database then we’ll return the mobile phone. If a mobile phone is also not available then we’ll return a home phone. If the home phone is also not available then we’ll return the email id. Below is the SQL query for that.

Now let’s analyze the results. In the case of “Gigi N Matthew”, Office mobile no is present, so it is selected. In the case of “Michael Raheem” we didn’t have office contact no, but mobile no was present, so it selected mobile no.

Now if we look at the contact no of “Thierry B D’Hers”, it is returning a blank value. Although he’s having an email address. The reason is that he’s having a blank string as office contact no, which is not NULL.

So to deal with this scenario, we can use the NULLIF function. Let’s dive into it:

NULLIF Function in SQL

NULLIF returns a null value if both arguments are the same. This function in SQL is utilized to study if the two identified values are similar or not. This function yields NULL if the entered two values are identical. It is also used to produce the first value if the two delivered values are not equal. This function comes under cutting-edge SQL features. It obtains two parameters i.e, the first value, and the second value.

NULLIF is identical to an explored CASE expression in which the two values are identical and the consequential value is NULL. It is highly advised that this function should not be utilized with time-dependent functions like RAND(), within a NULLIF function. This could drive the function to be considered twofold and to yield various outcomes from the two requests.

Its syntax is as follows:

NULLIF(arg1,arg2)

Below is our modified example with the use of NULLIF.

Now, we have contact no of “Thierry B D’Hers”, but for customer “Janice M Galvin” we received NULL output. It’s because we don’t have any information available. So we might want to change out query so instead of returning NULL we can have informative text like ‘’ For this we can use ISNULL function.

ISNULL Function in SQL

ISNULL function has two arguments, the first argument is a variable or expression to evaluate, and the second argument is a value that we want to return if the first argument is NULL. It is used to return the value provided, in case the entered value is NULL. Also, in case the entered value is not NULL then it yields the entered value. All the above-mentioned three functions are extremely important in advanced SQL queries. These functions are extremely valuable in handling NULL values. All three functions can be used in sub-queries and can be used effectively in designing robust database applications.

Its syntax is as follows:

ISNULL(expression,value)

Following is our modified example with the use of the ISNULL function.

Now, we got desired output.

SQL Coalesce vs Isnull

In SQL, dealing with null values is common. Two functions that assist with this are COALESCE and ISNULL. They both return the first non-null expression among their arguments. But what distinguishes the two? Well, COALESCE is ANSI SQL standard, meaning it is recognized by all SQL implementations. This makes it a more portable choice. On the other hand, ISNULL is specific to Microsoft SQL Server. It can only take two arguments, unlike COALESCE which can handle a wider range of parameters. Furthermore, ISNULL returns a datatype that corresponds to the first argument, whereas COALESCE returns the datatype of value with the highest precedence.

SQL Nullif vs IsNull

While ISNULL replaces null values, NULLIF is slightly different; it returns a null value if the two given expressions are equal. Therefore, NULLIF can be used as a preventative measure to avoid division by zero errors or other potential pitfalls that may occur when certain undesirable values crop up. Comparatively, ISNULL is used to manage already existing null values by replacing them.

Isnull TSQL

T-SQL is Microsoft’s proprietary extension of SQL, and ISNULL is a function specific to T-SQL. The ISNULL function in T-SQL takes two arguments. If the first argument is null, the function will return the second argument; otherwise, it will return the first argument. It’s a convenient way to ensure that operations on nullable columns do not result in null values, which might disrupt further calculations or data displays.

T SQL Isnull vs Coalesce

As earlier discussed, the primary differences between ISNULL and COALESCE in T-SQL lie in their functionality and flexibility. ISNULL is more restrictive in its application, accepting only two parameters and returning a data type based on the first parameter. In contrast, COALESCE is more versatile, accepting multiple parameters and returning the data type of the highest precedence value.

Nullif SQL Server Example

Consider the following example in SQL Server:

SELECT NULLIF(100, 100), NULLIF(200, 100);

The first NULLIF function will return null because the two arguments are equal, while the second NULLIF will return 200 because the arguments are different.

What Does Coalesce Mean in SQL?

COALESCE is a function in SQL that returns the first non-null value in a list. If all values are null, COALESCE will return null. It is commonly used to replace null values with a default value or to combine two columns, choosing non-null values first.

How to Use Coalesce in SQL?

To use COALESCE in SQL, you need to pass it a list of values. For instance:

SELECT COALESCE(null, null, 'third', 'fourth');

This will return ‘third’ as it is the first non-null value in the list. By using COALESCE, you can control what default value should be used when encountering a null value, thus providing more readability and control over your SQL operations. One of the main advantages of COALESCE is that it allows you to control the default value to use when a null value is encountered, offering increased readability and control over your SQL operations.

Moreover, COALESCE can be used in numerous practical scenarios. For example, in database queries where you have to choose between different columns where one or more may contain a null value. In such a case, COALESCE will help you pick the first non-null value.