Understanding COALESCE in SQL With Examples

In SQL, the COALESCE function is used to handle the Null valuesIt returns the first non-null value among a list of expressions. It’s beneficial when you want to replace the null value with a user-defined value or the first available value from a set of columns or expressions.

coalesce in sql
Coalesce Function in SQL

For example, if you have two columns in a table, and you want to select the first non-null value between them, you can use the COALESCE function in the SELECT statement like this:

SELECT COALESCE(column1, column2) as result FROM Table_Name;

In the above query, the Coalesce function will return the value of column1 if it is not NULL. Otherwise, it will return the value of column2. If both column1 and column2 are NULL, it will return NULL.

Example:

SELECT COALESCE(NULL, 'Amit', 'Singh');

Output:

Amit

Syntax of Coalesce in SQL:

The syntax for the COALESCE function is as follows:

COALESCE(expression1, expression2, ..., expressionN)

Here, expression1, expression2, …, and expressionN are the values or expressions you want to evaluate. The COALESCE function returns the first non-null value among the set of expressions.

Example 1: Coalesce() function in SQL

For example, let’s say you have two columns in a table, firstName and lastName, and you want to select a single column that contains the full name of each person, but sometimes the firstName column may be NULL.
You can use the COALESCE function to achieve this as follows:

SELECT COALESCE(firstName + ' ', '') + lastName AS fullName FROM myTable;

In this example, the COALESCE function evaluates the “firstName” column. If it is not NULL, it is concatenated with a space and the “lastName” column to create the full name.

If the “firstName” column is NULL, the empty string is used instead, preventing the space from appearing before the last name.

Simply, The COALESCE in SQL is a convenient way to handle NULL values. It allows you to provide a default value or an alternative expression when a value is missing.

Example 2: Coalesce() function in SQL

If all expressions are NULL, then it returns NULL.

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

Output:

RESULT
------------
100

Understanding coalesce in SQL:

The COALESCE function in SQL returns the first non-null value from a list of expressions.
It is commonly used to replace null values with a default value or to combine multiple columns into one column.

Example:

SELECT COALESCE(NULL, 'Shekh Ali', 123);

This query will return ‘Shekh Ali’ since it is the first non-null value among the arguments.

Features and Benefits of COALESCE in SQL

The COALESCE function in SQL has the following properties:

  • Returns the first non-null value: The COALESCE in SQL evaluates each expression in the order provided and returns the value of the first expression that is not null. If all expressions are null, the Coalesce function returns null.
  • Supports multiple expressions: The COALESCE function can take two or more expressions as arguments, separated by commas. This allows you to check multiple values for null and return the first non-null value.
  • Implicit conversion of data types: When evaluating expressions, the COALESCE function automatically performs implicit data type conversions as necessary to match the data type of the first non-null value.
  • Can be nested: The COALESCE function can be nested within other functions to create more complex expressions. For example, you can nest a COALESCE function inside an aggregate function like SUM to handle null values in the data being summed.
  • Works with NULLIF function: The NULLIF function can be used in conjunction with COALESCE to compare two expressions and return null if they are equal. It allows you to handle specific cases where you want to exclude certain values from the COALESCE evaluation.

To understand how the COALESCE function works, let’s take an example of a table that contains customer data. The table has columns for the customer’s first, last, and middle names. However, some records may have null values for the middle name column.

-- Creating a new table
 CREATE TABLE customers (
    id int NOT NULL PRIMARY KEY,
    first_name varchar(50) NOT NULL,
    last_name varchar(50) NOT NULL,
    middle_name varchar(50) NULL
 );

-- Inserting records into table.
INSERT INTO customers VALUES (1, 'Shekh', 'Ali', NULL),
                                                          (2, 'Roman', 'Chug', 'A'),
                                                          (3, 'Denial', 'Smith', 'B');

Customers Table:

idfirst_namelast_namemiddle_name
1ShekhAliNULL
2RomanChugA
3DenialSmithB

Example: SQL Queries Using COALESCE() Function

Let’s look at some example queries that use the COALESCE in SQL.

Example : Use COALESCE() to Replace NULL with a Label

SELECT id, first_name, last_name, COALESCE(middle_name, 'N/A') AS middle_name
FROM customers;

In this query, the COALESCE function replaces null values in the middle name column with ‘N/A.’

coalesce in sql example

Example 2: Use COALESCE() When Concatenating NULL and Strings

In this query, the COALESCE function is used to avoid concatenating a null value with a string, which would result in a null value.

SELECT id, first_name, last_name, COALESCE(middle_name + ' ', '') + last_name AS full_name
FROM customers;

In this query, the COALESCE function is used to avoid concatenating a null value with a string, which would result in a null value.

Example 3: Use COALESCE() with multiple Arguments

SELECT Id, COALESCE(middle_name, first_name, last_name) AS Name
FROM customers;

In this query, the COALESCE function is used to return the first non-null value among multiple arguments.

Output:

IdFull_Name
1Shekh
2A
3B

Example 4: Use COALESCE() to replace NULL with a calculated value

SELECT id, first_name, last_name, COALESCE(middle_name, CONCAT(SUBSTRING(first_name, 1, 1), SUBSTRING(last_name, 1, 1))) AS initials
FROM customers;
idfirst_namelast_nameinitials
1ShekhAliSA
2RomanChugA
3DenialSmithB

In this query, the COALESCE function replaces null values in the middle name column with the first initial of the first name and last name.

Differences between the COALESCE and CASE statements in SQL Server:

Here are some differences between the COALESCE and CASE statements in SQL Server:

  • COALESCE is a function, while CASE is a statement in SQL.
  • COALESCE can only take a single argument list, while CASE can handle multiple conditions.
  • COALESCE returns the first non-NULL value from the argument list, while CASE can return different values based on the conditions.
  • COALESCE is more concise and easier to read than CASE when dealing with a small number of conditions.
  • COALESCE can be more efficient than CASE when dealing with a large number of conditions, as it does not need to evaluate each condition in turn.
  • CASE can handle more complex logic than COALESCE, as it can include multiple conditions and have different actions for each condition.
  • COALESCE in SQL is more commonly used to replace NULL values with non-NULL values, while CASE is more widely used for conditional logic in general.

Overall, both COALESCE and CASE have their strengths and weaknesses, and the choice between them depends on the particular use case and the developer’s preferences.

Comparing COALESCE and CASE in SQL Server

Here are examples of using the COALESCE() and CASE statements to achieve similar results:
Let’s suppose we have a table named Sales that contains the following data:

IdProductPrice
1Product A15.99
2Product BNULL
3Product C20.99

Example of using COALESCE() function

In this example, we will use the COALESCE() function to replace any NULL values in the price column with a default value of 0.00:

SELECT id, product, COALESCE(price, 0.00) AS price
FROM sales;
IdProductPrice
1Product A15.99
2Product B0.00
3Product C20.99

As you can see, the COALESCE() function has replaced the NULL value in the second row of the price column with 0.00.

Example: Using CASE Statement

In this example, we will use the CASE statement to achieve the same result as the COALESCE() function:

SELECT id, product, 
  CASE 
    WHEN price IS NULL THEN 0.00 
    ELSE price 
  END AS price
FROM sales;

As you can see in the previous example, the CASE statement can also achieved the same result as the COALESCE() function by replacing the NULL value in the second row of the price column with 0.00.

However, the CASE statement is a bit more verbose and can be less efficient than the COALESCE() function when dealing with multiple columns.

Comparing COALESCE and ISNULL

Here are some key differences between the COALESCE and ISNULL statements in SQL Server:

  • Function support: COALESCE is a standard SQL function supported by most database systems, while ISNULL is a T-SQL-specific function and may not be available in other database systems.
  • Parameter count: COALESCE can accept any number of parameters, while ISNULL can only take two parameters.
  • Return value: COALESCE returns the first non-NULL value from its list of parameters, while ISNULL returns the second parameter if the first parameter is NULL.
  • Data type compatibility: COALESCE can accept parameters of different data types, while ISNULL requires that the two parameters have the same data type.
  • Parameter count limitation: COALESCE can be used with more than two parameters, while ISNULL can only be used with two parameters.
  • Versatility: COALESCE is more versatile and can be used in more complex scenarios, while ISNULL is more straightforward but may only be suitable for some situations.

The choice between COALESCE and ISNULL will depend on the specific use case and the developer’s preference. However, both functions have strengths and weaknesses, and it’s important to consider the differences when deciding which one to use.

Example 1: using COALESCE in SQL

DECLARE @var1 varchar(10)
DECLARE @var2 varchar(10)

SET @var1 = NULL
SET @var2 = 'hello'

SELECT COALESCE(@var1, @var2) AS Result
-- Output: hello

In this example, we use COALESCE to return the first non-NULL value between the variables @var1 and @var2. Since @var1 is NULL, the COALESCE function returns @var2, which has the value ‘hello.’

Example 2: using ISNULL

DECLARE @var1 varchar(10)
DECLARE @var2 varchar(10)

SET @var1 = NULL
SET @var2 = 'hello'

SELECT ISNULL(@var1, @var2) AS Result
-- Output: hello

In this example, we use ISNULL to return the second parameter if the first parameter is NULL. Since @var1 is NULL, the ISNULL function returns @var2, which has the value ‘hello.’ It is the same as the COALESCE example above.

In this simple example, COALESCE and ISNULL produce the same result. However, the behavior of the two functions can differ in more complex scenarios where there are multiple arguments with different data types or when using more than two arguments.

Reference-MSDN-COALESCE Function in SQL

FAQs

Here are some possible FAQs related to the topic COALESCE Function in SQL:

Q: What is COALESCE function in SQL?

COALESCE is a built-in SQL Server function that returns the first non-NULL value in a list of expressions. We can use it to replace NULL values with alternative values, concatenate strings, and perform other operations that handle NULL values.

Q: What are the advantages of using COALESCE over ISNULL in SQL Server?

While We can use both functions to replace NULL values, COALESCE can handle multiple expressions and return the first non-NULL value among them. That makes it more flexible and powerful than ISNULL, which can only hold two arguments.

Q: Can COALESCE be used with different data types in SQL Server?

Yes, COALESCE can handle different data types, but it will return the data type with the highest precedence among the expressions. That means if the expressions have different data types, We may not expect the result, and type conversion may be necessary.

Q: Can COALESCE return NULL?

Yes, COALESCE can return NULL if all of its arguments are NULL. This is because the Coalesce function returns the first non-NULL value from its list of parameters. Therefore, if all the parameters passed to COALESCE are NULL, then it will return NULL.

Q: Can COALESCE be used in join conditions?

Yes, COALESCE can be used in join conditions. It can be particularly useful when joining tables which might have NULL values in the joined columns.

You might want to read this too:

Let others know about this post by sharing it and leaving your thoughts in the comments section.

Shekh Ali
4 1 vote
Article Rating
Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments