What is SQL Server COALESCE() function?

Filed Under: SQL Server
SQL Server COALESCE() Function

Hey, folks! In this article, we will be focusing on SQL Server COALESCE() function.


Understanding SQL Server COALESCE() function

SQL Server COALESCE() function helps to estimate and return the first NON-NULL value from the set of data or database.

In scenarios when we need to fetch or estimate the non-null values from the database, SQL Server COALESCE() function can be used to server the purpose.


Syntax of SQL Server COALESCE() function

Having understood the working of COALESCE() function, let us get started with the syntax and implementation of the same.

Select COALESCE(expressions)

The expressions includes the set of data values to test for the presence of first non-null values.

The COALESCE() function returns the first NON-NULL expression from the parameter list.

If all the expressions in the parameter list are NULL values, then the COALESCE() function returns NULL.


Examples of COALESCE() function

Now let us understand the implementation of COALESCE() function through various examples as mentioned below.

Example 1:

SELECT COALESCE(NULL, 'Python', NULL, 'JournalDev.com');

In this example, we passed set of data values that includes two NULL values and two String values. The COALESCE() function returns the first NON-NULL value i.e. ‘Python’.

Output:

Python

Example 2:

SELECT COALESCE(NULL, NULL, NULL, 100);

In this example, we have passed three NULL values and an integer value. The function returns the integer value as output.

Output:

100

COALESCE() v/s ISNULL()

Many a times, we tend to get confused between two important yet relating functions of SQL Server i.e. COALESCE() function and ISNULL() function.

SQL Server COALESCE() function tests for the presence of first NON-NULL value and returns the value.

On the other side, SQL Server ISNULL() function checks whether the entered expression is NULL or not. If the entered expression is NULL, it returns the value passed to the argument list as output. Else, it returns the expression itself.


Conclusion

By this, we have come to the end of this topic. Please feel free to comment below in case you come across any doubts.

For more such posts related to SQL Server, please do visit SQL Server JournalDev.


References

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages