SQL Standard

SQL Coalesce Null Values

In SQL, the COALESCE is an extremely powerful and useful function that allows us to return the first non-null value from the list of expressions. It plays a very important role when handling the NULL values in a database table.

In this tutorial, we will look at the coalesce function in detail and explore how we can use it in various SQL operations.

Function Syntax:

The following shows the syntax of the coalesce function:

COALESCE(expression1, expression2, ..., expressionN)

In this case, the function evaluates the provided expressions. This can be basic expressions, values, or table columns.

The function then returns the first non-null value when evaluating the expressions.

Example 1: Basic Usage

Let us start with the basics of a simple coalesce function usage. Suppose we have a table called “employees” with the first name, last name, and middle name columns.

Let us assume that in this table, there are some employees who do not have a middle name while others do.

If we want to create a full name of the employees by combining the three columns, it can lead to problems where the middle name is NULL.

To avoid such an issue, we can use the coalesce function as demonstrated in the following example query:



COALESCE(middle_name, '') AS middle_name,


CONCAT_WS(' ', first_name, COALESCE(middle_name, ''), last_name) AS full_name

FROM employees;

In this query, we use the coalesce function to handle the cases where the value of the “middle_name” column is NULL.

The function evaluates the value. If it is NULL, the function returns an empty string which ensures that the concatenation does not fail.

Example 2: Default Value

We can also use the coalesce function to assign a default value in case the column is NULL.

Take for example, we have a table called “products” with the “product_id”, “product_name”, and “price” columns.

Some products may not have a specified price, and we want to display a default price of 0.00 for such products.

We can accomplish this with the coalesce function as follows:




COALESCE(price, 0.00) AS price

FROM products;

This should set the price to 0.00 for any NULL value in the “price” column.


In this post, we covered the fundamentals of working with the coalesce function including how to handle the null values and how to work with default values.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list