Purpose of COALESCE expression
In this article you will get to know the Purpose of COALESCE expression
- It is used for NULL Handling.
- In the COALESCE expression, a list of arguments is passed. It is used to check if the arguments are NULL.
- The output is NULL if all arguments evaluate to NULL.
- Else, it will check for NOT NULL values sequentially in the list and will return the first Non-Null value.
COALESCE(exp_1, exp_2, …. exp_n);
Alternative function to COALESCE ?
123456789COALESCE is a shorthand expression for the following full CASE expression:CASEWHEN expression_1 IS NOT NULLTHEN expression_1...WHEN expression_nIS NOT NULLTHEN expression_nELSE NULLEND
Pre-requisite to use the function ?
1Each COALESCE function must have at least two operands.
Is it ANSI Compliance ?
1Yes, it is ANSI SQL:2011 compliant.
Here is how it works – first it shall check if col_1 is null. If yes, then it will check col_2.
If col_2 is also null then it will check for col_3.
If col_3 is also null, then the output will be NULL.
If coalesce encounters any of the column having not null value then the column value will be returned by the query. (Left to right priority)
Additional information :
- Anexpression_n in the argument list may be evaluated twice: once as a search condition and again as areturn value for that search condition.
- Using a nondeterministic function, such as RANDOM, in an expression_n may have unexpected results, because if the first calculation of expression_n is not NULL, the second calculation of thatexpression_n , which is returned as the value of the COALESCE expression, might be NULL.
- You can use a scalar subquery in a COALESCE expression. However, if you use a non-scalar subquery (a subquery that returns more than one row), a runtime error is returned.
Default Title ?
- The default title for a COALESCE expression appears as:
Any data types that have restrictions in a COALESCE Expression ?
Yes, there are restrictions on CLOB, BLOB, UDT types when they are to be used in the argument list. These restrictions are :
- A BLOB should be casted to BYTE or VARBYTE.
- A CLOB should be casted to CHAR or VARCHAR.
- Multiple UDTs should be identical types because Teradata Database does not perform implicit type conversion on UDTs in a COALESCE expression.
COALESCE with non-comparison operators ?
When non-comparison operators like || , *, + are to be used in COALESCE function, the entire COALESCE function and its operands must be enclosed in parenthesis.
Example :SELECT (COALESCE(300,0)) * 7;