SQL COALESCE Function: Handling NULL Effectively

Summary: this tutorial introduces you to the SQL COALESCE function and shows you how to apply this function in real scenarios.

Introduction to the SQL COALESCE function

The COALESCE function accepts a number of arguments and returns the first non-NULL argument. The following illustrates the syntax of the COALESCE function.

COALESCE(argument1, argument2,...);Code language: SQL (Structured Query Language) (sql)

The COALESCE function evaluates its arguments from left to right. It stops evaluating until it finds the first non-NULL argument. It means that all the remaining arguments are not evaluated at all.

The COALESCE function returns NULL if all arguments are NULL.

The following statement returns 1 because 1 is the first non-NULL argument.

SELECT COALESCE(1,2,3); -- return 1Code language: SQL (Structured Query Language) (sql)

The following statement returns Not NULL because it is the first string argument that does not evaluate to NULL.

SELECT COALESCE(NULL,'Not NULL','OK'); -- return Not NULLCode language: SQL (Structured Query Language) (sql)

If you use the following statement

SELECT 1/0; -- division by zeroCode language: SQL (Structured Query Language) (sql)

you will get the division by zero error.

However, the following statement returns 1 and does not issue any error:

SELECT COALESCE(1,1/0); -- return 1Code language: SQL (Structured Query Language) (sql)

This is because the COALESCE function is short-circuited. It stops evaluating the remaining arguments after it finds the first non-NULL arguments.

Almost all relational database systems support the COALESCE function e.g., MySQL, PostgreSQL, Oracle, Microsoft SQL Server, Sybase.

Note that the COALESCE function is the most generic function of the NVL function and can be used instead of the NVL function.

SQL COALESCE examples

Assuming that we have a products table with the following structure and data:

CREATE TABLE products (
	ID INT PRIMARY KEY,
	product_name VARCHAR(255) NOT NULL,
	product_summary VARCHAR(255),
	product_description VARCHAR(4000) NOT NULL,
	price NUMERIC (11, 2) NOT NULL,
	discount NUMERIC (11, 2),
	CHECK (net_price >= discount)
);Code language: SQL (Structured Query Language) (sql)

INSERT INTO products (
	ID,
	product_name,
	product_summary,
	product_description,
	price,
	discount
)
VALUES
	(
		1,
		'McLaren 675LT',
		'Inspired by the McLaren F1 GTR Longtail',
		'Performance is like strikin and the seven-speed dual-clutch gearbox is twice as fast now.',
		349500,
		1000
	),
	(
		2,
		'Rolls-Royce Wraith Coupe',
		NULL,
		'Inspired by the words of Sir Henry Royce, this Rolls-Royce Wraith Coupe is an imperceptible force',
		304000,
		NULL
	),
	(
		3,
		'2016 Lamborghini Aventador Convertible',
		NULL,
		'Based on V12, this superveloce has been developed as the Lamborghini with the sportiest DNA',
		271000,
		500
	);Code language: SQL (Structured Query Language) (sql)

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

Using SQL COALESCE for substituting NULL values

When working with the data in the database table, you often use the COALESCE function to substitute a default value for a NULL value.

Suppose you have to display the products on a web page with all information in the products table. Some products may not have the summary but the other do.

In this case, you can use the COALESCE function to return the product summary, and if the product summary is not provided, you get the first 50 characters from the product description.

SELECT
	ID,
	product_name,
	COALESCE (
		product_summary,
		LEFT (product_description, 50)
	) excerpt,
	price,
	discount
FROM
	products;Code language: SQL (Structured Query Language) (sql)

You can use the CONCAT function to add the (…) to the end of the excerpt to make it more meaningful to users that the text they are reading is just the excerpt and there is more content if they click the read more link.

SELECT
	ID,
	product_name,
	COALESCE (
		product_summary,
		CONCAT(
			LEFT (product_description, 50),
			'...'
		)
	) excerpt,
	price,
	discount
FROM
	products;Code language: SQL (Structured Query Language) (sql)

Using SQL COALESCE function in expression

Suppose you need to calculate the net price of all products and you came up with the following query:

SELECT 
    id, 
    product_name, 
    (price - discount) AS net_price
FROM
    products;Code language: SQL (Structured Query Language) (sql)

The net price is NULL for the Rolls-Royce Wraith Coupe. This is because the discount of this product is NULL, and when you use this NULL value in the calculation, it results in a NULL value.

To fix this, you can update all NULL values in the discount column to 0.

UPDATE products 
SET 
    discount = 0
WHERE
    discount IS NULL;Code language: SQL (Structured Query Language) (sql)

Or you can use the COALESCE function as follows:

SELECT 
    id, 
    product_name, 
    price,
    discount,
    (price - COALESCE(discount,0)) AS net_price
FROM
    products;Code language: SQL (Structured Query Language) (sql)

The net price is now calculated correctly.

SQL COALESCE and CASE expression

The COALESCE function is syntactic of the CASE expression. It means that the expression

COALESCE(argument1,argument2,argument3);Code language: SQL (Structured Query Language) (sql)

can be rewritten using the following CASE expression:

CASE
  WHEN (argument1 IS NOT NULL) THEN argument1
  WHEN (argument2 IS NOT NULL) THEN argument2
  ELSE argument3
ENDCode language: SQL (Structured Query Language) (sql)

For example, you can rewrite the query that calculates the  net price from the price and discount using the CASE expression as follows:

SELECT 
    id,
    product_name,
    price,
    discount,
    (price - 
      CASE
        WHEN discount IS NOT NULL THEN discount
        ELSE 0
    END) AS net_price
FROM
    products;Code language: SQL (Structured Query Language) (sql)

The query returns the same result as the one that uses the COALESCE function.

In this tutorial, you have learned how to use the SQL COALESCE function to handle NULL values in the database table.