SQL COALESCE() Function

Main Article :- Sql difference between COALESCE() and ISNULL() Functions

Use the ISNULL and Coalesce functions to replace null values with user-defined values.

Differences Between IsNull() and Coalesce() Functions

1.The COALESCE() feature is primarily based on the ANSI SQL popular whereas ISNULL feature is a Transact-SQL feature

2. An expression involving ISNULL with non-null parameters is viewed to be NOT NULL, whilst expressions involving COALESCE with non-null parameters is viewed to be NULL.

3. The ISNULL() characteristic includes solely two parameters. The COALESCE() characteristic consists of more than one parameters. If we use greater than two parameters with the ISNULL feature then we have to use nested ISNULL functions.

Example: ISNULL() function

SELECT ISNULL(NULL, NULL, 'Hello')  

COALESCE() function

SELECT COALESCE(NULL, NULL, 'hello')  

4. The ISNULL() characteristic appears at the primary fee and the second one parameter fee is robotically restrained to that duration however COALESCE() does now no longer have this restriction.

Example:

declare @test varchar(3)  
select isnull(@test, 'ABCD') AS ISNULLResult  
select coalesce(@test, 'ABCD') AS coalesceResult  

The check variable has duration 3. So the ISNULL feature returns tes and the COALESCE() feature does not; relying at the duration, it returns check.

5. The ISNULL() feature includes numerous sorts of parameters. The COALESCE() feature doesn`t restriction the variety of arguments, however they should all be of the identical statistics type.

Example: ISNULL() function

DECLARE @a VARCHAR(5)='Hello',  
@b INT =5  
SELECT ISNULL(@a, @b) AS ISNULLResult  

The COALESCE() function:

DECLARE @a VARCHAR(5)='Hello',  
@b INT =5  
SELECT COALESCE(@a, @b) AS COALESCEResult

6. Use them in a SELECT INTO question then each will produce a NON-NULL fee withinside the end result desk if the characteristic has NON NULL constraint however if it doesn`t then COALESCE will create an characteristic that lets in NULL and ISNULL will create which does not permit NULLs.

Now let's recognize every factor in little extra detail.

COALESCE promotes its argument to the better statistics kind.

As I actually have stated earlier than that, COALESCE efficiently promotes its arguments to the very best statistics kind withinside the expression list, even as ISNULL simply seems on the statistics sort of the primary argument and makes the entirety of that kind. Let's see an SQL question to recognize this factor.

SELECT 19 / ISNULL(CONVERT(INT,NULL), 3.00);

Output:

SELECT 19 / COALESCE(CONVERT(INT,NULL), 3.00)

Output:

The first SQL query uses ISNULL and the first data type is INT, but because it is NULL, it converts 3.00 to INT and performs an integer operation, but COALESCE correctly converts 19 to FLOAT and floats. Perform the operation. For more information on SQL Server floating point operations and data types, see Microsoft SQL for Beginners.

7. COALESCE allows multiple values, but ISNULL allows only one value.

You can provide COALESCE with multiple values ​​to use if the target is NULL. For example, the following query provided COALESCE with four options:

DECLARE @x VARCHAR(10)
DECLARE @y VARCHAR(10)
DECLARE @z VARCHAR(10)
DECLARE @a VARCHAR(10)

SELECT @a = 'SQL'

--This will return SQL
SELECT COALESCE(@x,@y,@z,@a)

With ISNULL, you can only provide two values e.g.

SELECT ISNULL(@x,@y); --NULL
SELECT ISNULL(@x,@a); --SQL

This flexibility permits you to update complicated case statements with easy coalesce features referred to as on SQL Server saved processes and features.

See those unfastened SQL and database guides to study greater approximately it.

8. Length of Result :

In the case of COALESCE statistics sort of the result, price determines the sort of COALESCE expression however withinside the case of ISNULL, it`s the sort of the primary argument. For example, see the subsequent SQL Query

DECLARE
@a AS VARCHAR(4) = NULL,
@b AS VARCHAR(10) = '1234567890';

SELECT COALESCE(@a, @b) AS [COALESCE], ISNULL(@a, @b) AS [ISNULL];

Output:

You can see that in case of COALESCE() the result is of type and length VARCHAR(10) but in case of ISNULL() is the length of the first value i.e. length is 4 characters. Another thing to be aware of is the use of square brackets, e.g. [ISNULL], we do this when we use keywords or functions as literals, i.e. variable names or column names.

If you want to know more, I recommend you to read Microsoft SQL Server 2012 SQL Fundamentals, one of the best books for understanding SQL Server.

10. Behavior of COALESCE and ISNULL when used in SELECT INTO

Another difference between COALESCE and ISNULL arises when you use them in a SELECT INTO clause. If you don't know, you can create a table by copying data and schema from another table using the SELECT INTO clause.

If you are using something like:

COALESCE(column1, 0) as new_column

vs

ISNULL(column1, 0) as new_column.

Then, each expressions will produce a NOT NULL characteristic in end result desk if the supply is described as NOT NULL, however in case supply characteristic lets in NULLs then COALESCE will create an characteristic that lets in NULL and ISNULL will create which doesn`t permit NULLs.

The COALESCE function can be thought of as a specialized version of the CASE statement in SQL.

SYNTAX:

The syntax of the CASE statement showing the function of the COALESCE function is as follows

SELECT column_name1,column_name2, column_name3,
CASE
WHEN column_name1 IS NOT NULL THEN column_name1
WHEN column_name2 IS NOT NULL THEN column_name2 ELSE NULL
END as 'case_name'
FROM table_name;

The parameters mentioned in the above syntax are :

Column_name1, column_name2: The values of columns that have to have coalesced, i.e. if the results of any of these columns result in NULL values, then the first non-NULL value among them will be returned. But if both of them evaluates to NULL, then CASE will return NULL.

Example 1: The above Coalesce SQL statement can be rewritten using the CASE statement.

SELECT
  firstname+''+lastname fullname,
   relationship,
  CASE 
	WHEN homephone is NOT NULL Then homephone
	WHEN cellphone is NOT NULL Then cellphone
	WHEN workphone is NOT NULL Then workphone
   ELSE 'NA'
   END
   EmergencyContactNumber
FROM
  dbo.tb_EmergencyContact

The query returns the same result as the one that uses the COALESCE function.

Example 2: Suppose that we want to specify the salary level for each job category within my publishing company. Using the pubs database, we could write the following query:

SELECT job_desc, salary_level = 
CASE
  WHEN job_desc = 'New Hire - Job Not Specified' THEN '25K'
  WHEN job_desc = 'Chief Executive Officer' THEN '500K'
  WHEN job_desc = 'Business Operations Manager' THEN '350K'
  WHEN job_desc = 'Chief Financial Officier' THEN '400K'
  WHEN job_desc = 'Publisher' THEN '75K'
  WHEN job_desc = 'Managing Editor' THEN '65K'
  WHEN job_desc = 'Marketing Manger' THEN '55K + commissions'
  ELSE 'you get the idea'
END
FROM jobs;

Results:

You can concatenate rows into a single string using the COALESCE method. This COALESCE method can be used in SQL Server versions 2008 and later. All you have to do is declare a varchar variable and inside the merge, concatenate the variable with commas and columns, then assign COALESCE to the variable.

In this method you don't have to worry about trailing commas. You may need a stored procedure or function to do this and get the concatenated string value.

Example 1: The COALESCE method is below.

Select FirstName, LastName, CityName, PostCode from SQLDemo.dbo.PersonNameAndAddress

Declare @val Varchar(MAX); 
Select @val = COALESCE(@val + ', ' + FirstName + ' ' + Lastname + ' Address: ' + CityName 
+ ' ' + PostCode, FirstName + ' ' + Lastname + ' Address: ' + CityName + ' ' + PostCode)
        From SQLDemo.dbo.PersonNameAndAddress 
		Select @val  AS PersonNameAndAddress;

Example 2:

Select CountryName from Application.Countries 

Declare @val Varchar(MAX); 
Select @val = COALESCE(@val + ', ' + CountryName, CountryName) 
        From Application.Countries Select @val;

Example 1: Let's see how we can use COALESCE() to do the same thing. Remember that COALESCE() is a standard function and whenever you can use COALESCE() you should use it. In this example, you don't have to do anything, just replace ISNULL() with COALESCE() and you're done, as shown in the following SQL query:

SELECT first_name, last_name, 
COALESCE(first_name,'') + COALESCE(last_name,'') as full_name FROM #People

Example 2: Let me display you some other use of COALESCE() feature even as we're the use of it. You can use COALESCE() to coach the use of the fee of some other column if the goal column is NULL and if this is additionally null then use the 1/3 column and so on.

You can use this method to offer state-of-the-art default values for your reports. For example, on this scenario, let`s show the fee of last_name if first_name is NULL and the fee of first_name if last_name is NULL withinside the report. Following SQL question makes use of COALESCE to do that:

SELECT COALESCE(first_name,last_name, '') as first_name,
COALESCE(last_name, first_name,'') as last_name,
COALESCE(first_name,'') + COALESCE(last_name,'') as full_name
FROM #People;

Output:

Here is the screenshot of SQL queries from Microsoft SQL SERVER 2014 database to provide you complete view:

That's all approximately a way to update NULL with empty String or clean in SQL SERVER. You can use COALESCE() to update NULL with blanks. It's in particular vital to apply those capabilities even as concatenating String in SQL Server due to the fact one NULL can flip all facts into NULL.

Btw, you may additionally use CONCAT() rather of + operator to keep away from NULL, this feature returns the fee of nonnull argument if some other argument is NULL. Between ISNULL() and COALESCE(), use ISNULL() in case you understand for positive that your code will run on Microsoft SQL Server however COALESCE() is higher due to the fact it is preferred and you may use it to update NULL with empty String in any database like Oracle, MySQL, and PostgreSQL.

Example 1: How to get rid of those bad NULL values ​​at higher group levels. For this we will use the COALESCE() function.

SELECT COALESCE(FullName, N'All Contestants') AS FullName,
 COALESCE(Category, N'All Categories') AS Category,
 Week,
 AVG(Score) AS AvgScore
FROM ContestScore
GROUP BY ROLLUP(FullName, Category), Week;

Example 2: I discovered a nuance with MySQL's GROUP BY statement earlier today that I'll share in the hope that others can learn from it. It is quite common to use a join statement to handle null values ​​while keeping the resulting field name unchanged.

SELECT coalesce(a.user_id, b.other_user_id) as user_id, 
sum(s.num) as total_nums
FROM table_a a
LEFT JOIN table_b on a.some_id = b.some_other_id
LEFT JOIN stats s on a.stat_id = s.id
GROUP BY user_id;

The nuance is that we need the GROUP BY to use to the complete coalesce expression however as it`s written it simplest applies to the user_id column from table_a. This has capability to provide unusual consequences in extra complex queries. The simplest reality I even observed it became that it became inflicting a reproduction key constraint violation in every other table.

Example 3: The answer is pretty easy however annoying - you need to use the complete coalesce expression inside the GROUP BY statement:

SELECT coalesce(a.user_id, b.other_user_id) as user_id, 
sum(s.num) as total_nums
FROM table_a a
LEFT JOIN table_b on a.some_id = b.some_other_id
LEFT JOIN stats s on a.stat_id = s.id
GROUP BY coalesce(a.user_id, b.other_user_id);

The reason this solution is messy is that it’s very easy to update the SELECT but forget to update the GROUP BY. This won’t throw an error and MySQL will execute the query just fine - the results just may be unexpected.

Example 4: Renaming the Resulting column and using that within the GROUP BY:

SELECT coalesce(a.user_id, b.other_user_id) as final_user_id, 
sum(s.num) as total_nums
FROM table_a a
LEFT JOIN table_b on a.some_id = b.some_other_id
LEFT JOIN stats s on a.stat_id = s.id
GROUP BY final_user_id;

This makes the query a bit more complicated but it’s being explicit about what we want and avoids hidden errors.

The COALESCE function accepts any number of arguments and returns the first non-NULL argument.

Syntax:

The following illustrates the syntax of the COALESCE function.

COALESCE(argument1, argument2,...);

The COALESCE function evaluates its arguments from left to right. It stops evaluating until it finds the first non-NULL argument. This means that any remaining arguments are not evaluated.

The COALESCE function returns NULL if all arguments are NULL.

Example 1: The following statement returns 1 because 1 is the first non-NULL argument.

SELECT COALESCE(1,2,3); -- return 1

Example 2: The following statement returns Not NULL because it is the first string argument that does not evaluate to NULL.

SELECT COALESCE(NULL,'Not NULL','OK'); -- return Not NULL

Example 3: If you use the following statement:

SELECT 1/0; -- division by zero

you will get the division by zero error.

Example 4: However, the following statement returns 1 and does not issue any error:

SELECT COALESCE(1,1/0); -- return 1

This is because the COALESCE function is missing. It stops evaluating the remaining arguments after finding the first non-NULL arguments.

Example 5: Let’s give simple examples without column names or subqueries.

SQL COALESCE - a function that returns the first defined value, i.e. the non-NULL value of its argument list. Usually, one or more arguments to the COALESCE function correspond to the column of the table for which the query is resolved. Often a subquery is also an argument to a function. This is done when it cannot be said with certainty that the subquery will return a certain value, e.g. 5, "string", '20181209', etc., but not a NULL value. This NULL value will then be replaced with the next value immediately after.

COALESCE (NULL, 7, 9) // Return 7
COALESCE (NULL, 'Not Found') // Return 'Not Found'
COALESCE ('2017-10-20', NULL, '2018-03-08') // Return '2018-03-08'

Example 6: In the following example, we will concatenate some values. Again, though, this is just an evaluation to let you know what happens when we have a NULL value. So go ahead and run TSQL. And we can see that we encounter NULL value while dealing with string concatenation. SQL Server just returns NULL whenever it encounters a NULL value. The result is not a combination of first name, null and last name.

SELECT firstName +' '+MiddleName+' '+ LastName FullName FROM Person.Person

Handle NULL values ​​using a function called SQL COALESCE. It allows to manage the behavior of the NULL value. So in this case, use the join SQL function to replace all the NULL values ​​of the middle name with a `` (Car (13) space). The SQL statement should still concatenate the three names, but no NULL values ​​appear in the output. We now see that the full name is displayed with a space in between, for NULL values. In this way, it is possible to effectively customize the values ​​of the columns.

SELECT firstName +' '+COALESCE(MiddleName,'') +' '+ LastName  FROM Person.Person

Example 1: Use the COALESCE function. (Works for all data types.) If you're not familiar with this function, read our guide to handling NULL values ​​with the COALESCE function. We can essentially sort the final NULL values ​​while sorting the non-NULL values ​​in ascending order, providing the highest possible alternative for NULL values:

SELECT *
FROM paintings
ORDER BY COALESCE(year, 2021);

Here, we use 2021 as the highest possible value for the year column. (We can be sure that no paintings in our table are from the future. We could use any number above 2020 to accomplish this.)

Example 2: Correspondingly, to sort NULLs first whilst ordering non-NULL values in descending order, we can use the following query:

SELECT *
FROM paintings
ORDER BY COALESCE(year, 2021) DESC;

Output Like MySQL, SQL Server does not support the NULLS FIRST / NULLS LAST options. However, the tricks with the minus operator and the COALESCE function work in SQL Server just like in MySQL. You can use these options to change the default behavior of SQL Server when sorting NULL values.

Suppose we want to know the salary of all employees in the company. But in the employee table, we can see that the salary of all employees was not mentioned. However, hourly wages and commissions for employees lacking the pay fields have been mentioned.

We can use that to compare employee salaries in the following example.

SELECT COALESCE(CONVERT(varchar(50),employeeid),
firstname,lastname) as 'Employee Identifier',
COALESCE(salary,hourly_rate*8*30 + COALESCE(commission,0))as 'Compensation'
FROM employees
ORDER BY 2 DESC;

Using the first COALESCE function we tried to identify an employee by merging the employee's id, first name and last name, then using the second COALESCE function we tried to choose salary or remuneration Is calculated.