After delivering my recent webinar, Writing SQL Queries, I received an interesting question: “Why is it called COALESCE?” This is a great opportunity to dive into SQL standards, how COALESCE works, and why it was given this particular name.

COALESCE: An Introduction

COALESCE is part of the SQL standard (ISO/ANSI SQL) and is used to handle NULL values in SQL queries. It allows you to return the first non-null value from a list of arguments. This function is crucial in data queries where NULLs are common, such as when joining tables or performing calculations that might involve incomplete data.

Syntax:

In this syntax, expression1, expression2, and so on, represent the list of values. COALESCE returns the first non-null expression from the list. If all expressions are NULL, COALESCE will return NULL.

Example:

Let’s say you have a table of customers with a few missing phone numbers:

In this case, the query will return the customer’s phone number if it exists; if not, it will return their mobile number. If both are NULL, it will default to displaying “No Contact.”

Why is it Called COALESCE?

I didn’t know the answer during the session but some research using my favourite GEN AI tool came up with the following

The term coalesce comes from the Latin word coalescere, meaning “to unite” or “come together.” In everyday language, it is used to describe things that merge or combine into a unified whole. This idea is mirrored in how the COALESCE function operates in SQL: it takes multiple possible values and “unites” them, returning the first one that is not NULL. The term was chosen by the creators of the SQL standard because it clearly represents the function’s purpose—to merge a list of potential values and return the first valid one.

SQL standards, including the SQL-92 and later revisions, use terms like COALESCE to ensure consistency across different database platforms, and the term itself was chosen for its clarity and descriptive power. By calling the function COALESCE, it conveys the idea of combining values in a meaningful way, which helps developers understand its purpose without needing extensive explanation.

COALESCE and the ISO/ANSI SQL Standard

COALESCE is part of the ISO/ANSI SQL standards and is widely supported across most relational database systems, such as Microsoft SQL Server, PostgreSQL, and MySQL. This standardisation ensures that developers can write queries using COALESCE in a platform-agnostic way, making it easier to port SQL code between different database systems. The function follows the same logic and syntax, contributing to SQL’s role as a standardized query language. You will also find that some database engines have their own non-standard functions for dealing with Nulls. SQL Server also has ISNULL for example.

Conclusion

In SQL, the COALESCE function is not only practical but also elegantly named to describe its functionality. It helps unify values by returning the first non-null result in a series of expressions, simplifying handling NULL values. The decision to name it COALESCE reflects the merging or uniting nature of the function—a decision made by the creators of the SQL standard to provide clarity and consistency across different database platforms.

If you find yourself working with queries that need to handle NULLs effectively, COALESCE should be part of your SQL toolkit.

You can watch the recording of today’s webinar here. If you would like to expand your SQL Server knowledge, then please sign up and attend our next TSQL Training Class.