SQL COALESCE Function Explained
COALESCE is a built-in function that helps you deal with NULL values effectively in MS SQL Server, MySQL, PostgreSQL and some other databases.
In SQL world, NULL is a tricky concept. Whenever a NULL value is included in an expression, it automatically returns a NULL result no matter what.
Take this statement for example:
SELECT customer_id,
order_value + tax AS 'total amount'
FROM customers;
It’s returning the total amount of money a customer paid. Imagine it meets a record where it does not have a value for the tax column, and the result for the total amount is NULL.
That’s where COALESCE comes in handy.
1. Syntax
COALESCE(value_1, value_2, ...., value_n)
2. What is does?
The COALESCE(value,...) returns the first non-NULL value in the list, or NULL if there are no non-NULL values. You must specify at least two values in the list. In case all values are NULL, the statement will return NULL.
For example, run the statement below:
SELECT COALESCE(NULL, NULL, '1st_not_null', '2nd_not_null');
It returns ‘1st_not_null’ because it’s the first value that is not NULL.
3. When and how to use it?
In real life, COALESCE is used to simplify a long CASE expression where you have to evaluate every value of a list to find the first non-null value. The SQL statement with CASE will be somewhat like this for a list of n values:
SELECT col_name,
CASE
WHEN value_1 is not NULL then value_1
WHEN value_2 is not NULL then value_2
WHEN value_3 is not NULL then value_3
…
ELSE value_n
END
FROM table_name;
With COALESCE, you need a statement as simple as this:
SELECT col_name,
COALESCE(value_1, value_2, value_3, …, value_n)
FROM table_name;
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download for iOS
