Code Of Dot Net

My Blog About Software Development

How are they similar and how are they different?

COALESCE and ISNULL will both return the first none NULL value. If you ask developers, they’ll probably say the biggest difference is that ISNULL only accepts two arguments while COALESCE can accept many.

Run the code sample below and you’ll notice that COALESCE will accept multiple arguments while ISNULL only accepts two.

	
	DECLARE @A VARCHAR(3) = 'USD'
	DECLARE @B VARCHAR(3) = 'CAD'
	DECLARE @C VARCHAR(3) = 'EUR'
	DECLARE @D VARCHAR(3) = NULL

	-----------------------------------
	-- ISNULL will fail with error message "The isnull function requires 2 argument(s)."
	-----------------------------------
	SELECT ISNULL(@D, @A, @B, @C) 
	
	-----------------------------------
	-- COALESCE will return USD
	-----------------------------------
	SELECT COALESCE(@D, @A, @B, @C) 

 

There are a lot of subtle differences between them. Consider the following example:

 


	DECLARE @A VARCHAR(3) = NULL
	DECLARE @B VARCHAR(3) = NULL
	DECLARE @C VARCHAR(3) = NULL
	DECLARE @D VARCHAR(3) = NULL

	--Example 1:
	SELECT COALESCE(@A, @B, @C, @D) 

	--Example 2:
	SELECT COALESCE(NULL, NULL,NULL, NULL) 

	--Example 3:
	SELECT ISNULL(NULL, NULL) 

 

Example 1: All variables @A, @B, @C, @D are NULL, resulting in the statement being evaluated to NULL.

 

Screenshot of example 1 results

Screenshot of example 1 results

 

Example 2: COALESCE(NULL, NULL,NULL, NULL) results in an error: “At least one of the arguments to COALESCE must be an expression that is not the NULL constant.” That means COALESCE requires at least one argument that is typed. Changing example 2 to SELECT COALESCE(NULL, NULL,NULL, @A) will result in no error because @A is typed. @A is a VARCHAR(3). If we change example 2 to: SELECT COALESCE(NULL, NULL, CAST(NULL AS VARCHAR(3)), NULL), we will not see any errors either due to the typed VARCHAR(3).

Screenshot of example 2

Screenshot of example 2 result

 

Example 3: ISNULL does not care about having two NULL constants. It evaluate the statement to NULL.

 

Screenshot of example 3 results

Screenshot of example 3 results

 

The following set of SQL will highlight more differences between ISNULL and COALESCE.

 


	DECLARE @A VARCHAR(1) = NULL
	DECLARE @B VARCHAR(12) = 'SQL is great'

	--Example 4:
	SELECT COALESCE(@A, @B) AS AB_COALESCE, COALESCE(@B, @A) AS BA_COALESCE

	--Example 5:
	SELECT ISNULL(@A, @B) AS AB_ISNULL, ISNULL(@B, @A) AS BA_ISNULL

	--Example 6:
	SELECT COALESCE(@B, 1);

	--Example 7:
	SELECT ISNULL(@B, 1);

 

Example 4: COALESCE(@A, @B) vs COALESCE(@B, @A) evaluates to the same value and type. It evaluates to a VARCHAR(12) with text ‘SQL is great’.

Screenshot of example 4 results

Example 5: ISNULL(@A, @B) does not result in the same value and type as ISNULL(@B, @A).

  • ISNULL(@A, @B): Evaluates to a VARCHAR(1) with text ‘S’
  • ISNULL(@B, @A): Evaluates to a VARCHAR(12) with value ‘SQL is great’
Screenshot of example 5 results

Screenshot of example 5 results

 

Example 4 vs Example 5 highlights a huge difference between ISNULL and COALESCE. This difference could potentially leave your data in a less precise state if ignored. ISNULL does not care about data type precedence (When an operator combines two expressions of different types, data precedence rules convert lower precedence data to higher precedence data). ISNULL takes the type of the first field, and truncates the value of the second field to match the first. COALESCE on the other hand cares about data type precedence and converts accordingly.

 

Example 6: COALESCE(@B, 1) results in the error: “Conversion failed when converting the VARCHAR value ‘SQL is great’ to data type int. An integer has a higher data precedence than a string; therefore SQL Server tries to convert the result of COALESCE ‘SQL is great’ to an INT but fails and throws an error.

If we switch the same statement to SELECT COALESCE(1, @B), the result will be 1. INT has a higher data precedence than VARCHAR, and the result of the COALESCE is 1, which is an INT. No conversions needed here.

Screenshot of example 6 results

Screenshot of example 6 results

 

Example 7: Again, ISNULL does not care about data type precedence rules. It returns a VARCHAR(12) with text ‘SQL is great’.

Screenshot of example 7 results

Screenshot of example 7 results

 

If you have any questions or comments, please leave them below.

November 18th, 2016

Posted In: SQL

Leave a Reply

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

mautic is open source marketing automation