A lot of times, when learning something in college, you may think “What good is this to me in real life?”. COALESCE is one of those SQL functions which puzzled some of my colleagues. But here is a very prominent use case:
Assume you have a list of Article entities in your database, and each article has a Category field (classic VARCHAR, no relations or other complications involved). You want to write an API endpoint to get all the Categories of a list of Articles, based on a list of Article ids. But now, your database is iffy, and some of your Articles aren’t categorised, so they have a NULL instead of an actual Category string. You want all these articles to belong to the “Miscellaneous” category.
Bad idea: processing, processing, processing… Run an SQL query to select the Category strings, check if “Miscellaneous” is in the result, add it otherwise (you don’t want the “Miscellaneous” category appearing twice, in case some Articles are correctly labeled as such)
Neat idea: Use COALESCE in your query to convert any NULL values to “Miscellaneous”. You’ll have something like:
SELECT DISTINCT(COALESCE(articles.category, “Miscellaneous”)) as category’)) FROM articles;
And you’ve done it with no extra overhead in processing, which might have been difficult for others to read and understand.