The COALESCE system function offers a great way to check expressions for NULL.
The syntax for COALESCE is very simple. It looks like this:
COALESCE(<expr1>,<expr2>,<expr3>,<expr4>,....<exprN>)
As you can see, all we do is outline a comma-separated list of expressions to the function. SQL Server will evaluate each expression, from left to right, and see if it evaluates to NULL. The value of the first expression that doesn’t evaluate to NULL is returned.
If all the expressions evaluate to NULL, then NULL is simply returned.
Your expressions can be many things. They can be column names, string literals, calls to scalar functions, calculations, whatever!
A simple example:
What do you think will be returned by the following statement?:
SELECT COALESCE(NULL, NULL, 'Hello there', 'How are you?',NULL) AS Greetings
This code will return “Hello there” because that is the first expression to not evaluate to NULL.
Here it is in action:
One factoid you should know:
You should know that you must pass at least two expressions to the COALESCE system function.
Next Steps:
Leave a comment if you found this tutorial helpful!
The COALESCE system function is very similar to ISNULL. The ISNULL system function is also great for checking for NULL and can be used as an excellent error handling tool. Learn all about it here:
SQL Server ISNULL: Explained with Examples
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
