Coalesce

Conditional function

Description

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:

Click here to navigate to the Postgre docs page for this syntax.

The function Coalesce() is supported on snowflake

Grammar Syntax

COALESCE(value [, ...])

Sample Source Patterns

Postgre

Code

SELECT column1, column2, column3, coalesce(column1, column2, column3)
FROM (values
  (1,    2,    3   ),
  (null, 2,    3   ),
  (null, null, 3   ),
  (null, null, null),
  (1,    null, 3   ),
  (1,    null, null),
  (1,    2,    null)
) v

Result

COLUMN1	COLUMN2	COLUMN3	COALESCE(COLUMN1, COLUMN2, COLUMN3)
1	2	3	1
NULL	2	3	2
NULL	NULL	3	3
NULL	NULL	NULL	NULL
1	NULL	3	1
1	NULL	NULL	1
1	2	NULL	1

Snowflake

Code

SELECT column1, column2, column3, coalesce(column1, column2, column3)
FROM (values
  (1,    2,    3   ),
  (null, 2,    3   ),
  (null, null, 3   ),
  (null, null, null),
  (1,    null, 3   ),
  (1,    null, null),
  (1,    2,    null)
) v

Result

COLUMN1	COLUMN2	COLUMN3	COALESCE(COLUMN1, COLUMN2, COLUMN3)
1	2	3	1
NULL	2	3	2
NULL	NULL	3	3
NULL	NULL	NULL	NULL
1	NULL	3	1
1	NULL	NULL	1
1	2	NULL	1