Understanding COALESCE in SQL With Examples

Last updated on August 29th, 2023 at 04:43 pm

In SQL, the COALESCE function is used to handle the Null values. It 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.

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:

id	first_name	last_name	middle_name
1	Shekh	Ali	NULL
2	Roman	Chug	A
3	Denial	Smith	B

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.’

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:

Id	Full_Name
1	Shekh
2	A
3	B

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;

id	first_name	last_name	initials
1	Shekh	Ali	SA
2	Roman	Chug	A
3	Denial	Smith	B

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:

Id	Product	Price
1	Product A	15.99
2	Product B	NULL
3	Product C	20.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;

Id	Product	Price
1	Product A	15.99
2	Product B	0.00
3	Product C	20.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:

You might want to read this too:

• Having VS Where Clause in SQL
• SQL Server Indexing: Clustered vs Non Clustered Index Explained
• Mastering Database Normalization: Best Practices and Techniques
• CRM Databases: The Key to Enhanced Customer Engagement and Sales
• Mastering SQL Inner Join
• SQL Server CONVERT Function: How to Convert Data Types in SQL Server
• Delete vs Truncate vs Drop in SQL Server
• SQL Server TRY_CAST() Function – Understanding TRY_CAST() in SQL with [Examples]
• UNION vs UNION ALL in SQL SERVER
• View In SQL | Types Of Views In SQL Server
• SQL Server Try Catch: Error Handling in SQL Server With [Examples]
• Triggers in SQL Server
• Different ways to delete duplicate rows in SQL Server
• Types of Joins in SQL Server
• Function vs Stored procedure in SQL Server
• SQL pivot tables: Understanding pivot tables in SQL Server
• Group by Month in SQL: A Comprehensive Guide with Examples in SQL Server

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