COALESCE( ) vs ISNULL( ) in SQL
ISNULL() replaces NULL with the specified replacement value. Do not use ISNULL to find NULL values. Use IS NULL instead. The following example finds all products that have NULL in the Color column.
The following example finds the average of the List Price of all products in a sample table. It substitutes the value zero for all NULL entries in the List Price column of the Product table.
COALESCE()evaluates the arguments in order and returns the current value of the first expression that initially doesn’t evaluate to NULL. For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null.
The COALESCE expression is a syntactic shortcut for the CASE expression:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
…
ELSE expressionN
END
The ISNULL function and the COALESCE expression have a similar purpose but can behave differently. Let’s take a glance!
1.ISNULL takes only two parameters. By contrast COALESCE takes a variable number of parameters.
2. Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int though for COALESCE, you must provide a data type.
3. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one):
By contrast,COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equal, have different nullability values. These values make a difference if you're using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example:
Thanks for reading. I hope this article helps you tackle a problem. See you in the next article 🙂
Database of queries➡️https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms>
References 🕮
