COALESCE¶

Returns the first non-NULL value from a list of expressions.

Syntax¶

Parameters¶

COALESCE(expression1, expression2, ...): Evaluates expressions from left to right and returns the first non-NULL value. Requires at least two expressions.

Returns¶

Returns the first non-NULL value with the same type as the input expressions. If all values are NULL, returns NULL. All expressions must be of compatible types.

Supported Types¶

COALESCE supports all data types:

Primitive types: STRING, BOOLEAN, DOUBLE, FLOAT, INTEGER, BIGINT, BYTES
Complex types: ARRAY, STRUCT

Examples¶

Setup¶

For these examples, assume we have a contacts table:

CREATE TABLE contacts(
    id BIGINT,
    name STRING,
    email STRING,
    phone STRING,
    address STRING,
    PRIMARY KEY(id))

INSERT INTO contacts VALUES
    (1, 'Alice', 'alice@example.com', NULL, NULL),
    (2, 'Bob', NULL, '555-0123', NULL),
    (3, 'Charlie', NULL, NULL, '123 Main St'),
    (4, 'David', NULL, NULL, NULL)

COALESCE - Find First Available Contact Method¶

Get the first available contact method for each person:

SELECT name, COALESCE(email, phone, address, 'No contact info') AS contact_method
FROM contacts

`name`	`contact_method`
`"Alice"`	`"alice@example.com"`
`"Bob"`	`"555-0123"`
`"Charlie"`	`"123 Main St"`
`"David"`	`"No contact info"`

COALESCE with Default Values¶

Provide default values for NULL fields:

SELECT name,
       COALESCE(email, 'no-email@example.com') AS email,
       COALESCE(phone, 'Unknown') AS phone
FROM contacts

`name`	`email`	`phone`
`"Alice"`	`"alice@example.com"`	`"Unknown"`
`"Bob"`	`"no-email@example.com"`	`"555-0123"`
`"Charlie"`	`"no-email@example.com"`	`"Unknown"`
`"David"`	`"no-email@example.com"`	`"Unknown"`

COALESCE in UPDATE Statements¶

Use COALESCE to update only NULL values:

UPDATE contacts
SET email = COALESCE(email, 'default@example.com')
WHERE id = 2

This sets the email to 'default@example.com' only if it was NULL, otherwise keeps the existing value.

Important Notes¶

COALESCE returns the first non-NULL value from left to right
If all values are NULL, COALESCE returns NULL
All expressions must be of compatible types
The function requires at least two arguments
COALESCE supports all data types, including complex types like STRUCT and ARRAY
Commonly used for providing default values when NULL is encountered