COALESCE

COALESCE ( expression [, expression [...] ] )

Returns the first non-NULL expression encountered in the list.

Description

COALESCE returns the first expression in the list that is not NULL. If all expressions in the list evaluate to NULL, then COALESCE returns NULL.

COALESCE is a shorthand for a CASE expression, and it conforms to the SQL standard. The list of arguments in COALESCE must be of the same data type, or of a data type that can be implicitly converted to a common data type.

Syntax

COALESCE ( expression1 [, expression2, ... ] )

Arguments

• expression1 [, expression2, ... ]

A list of expressions of any type.

Return Type

Returns the data type of the highest precedence from the arguments.

Examples

Example 1: Basic Usage

This example shows how COALESCE returns the first non-NULL value.

SELECT COALESCE(NULL, 1, 'abc'); -- Returns 1
SELECT COALESCE('abc', NULL, 1); -- Returns 'abc'
SELECT COALESCE(NULL, NULL, NULL); -- Returns NULL

Example 2: Handling NULL Values in a Table

Imagine a Products table with columns ProductName and Description. If Description is NULL, you might want to display a default message.

-- Sample Table Structure
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Description VARCHAR(255)
);

INSERT INTO Products (ProductID, ProductName, Description) VALUES
(1, 'Laptop', 'A high-performance laptop.'),
(2, 'Keyboard', NULL),
(3, 'Mouse', 'Ergonomic wireless mouse.');

-- Query using COALESCE
SELECT
    ProductName,
    COALESCE(Description, 'No description available.') AS ProductDescription
FROM
    Products;

The result of the above query would be:

ProductName | ProductDescription
------------|---------------------------------
Laptop      | A high-performance laptop.
Keyboard    | No description available.
Mouse       | Ergonomic wireless mouse.

See Also