COALESCE

MySQL

COALESCE(value1, value2, …, valueN)

• value1: This parameter is the first expression in the list to be evaluated and returned by the COALESCE function. It can be a column name, a constant, or a function. If the value of this first expression is not NULL, it will be the output of the function.
• value2, …, valuen: These parameters are the next expressions to be evaluated by the COALESCE function. They can also be column names, constants, or functions. If the first value is NULL, the function will look at the next expressions successively until it finds a non-NULL value. If all expressions are NULL, the COALESCE function will return NULL.

Example

SELECT COALESCE(NULL, 'First Non-NULL value', NULL, 'Second Non-NULL value');

Output

'First Non-NULL value'

Explanation

The COALESCE function returns the first non-NULL value in the list. In the provided example, ‘First Non-NULL value’ is the first non-NULL value in the list, hence, it is returned.

PostgreSQL

COALESCE(value1, value2, …, valueN)

• value1: This is the first expression to be checked by the COALESCE function. If it is not null, then the function will produce the result of `value1`.
• value2: This is the second expression to be checked in case `value1` is null. If `value1` is null and `value2` isn’t, then `value2` is returned by the function.
• …: This denotes that the COALESCE function can take multiple arguments. They are checked for null values in the order specified.
• valuen: This is the last expression to be checked. If all preceding expressions are null, and `valueN` is not null, the function will produce `valueN`. If `valueN` is also null, then the COALESCE function will return NULL.

Example

SELECT COALESCE(NULL, 'PostgreSQL', NULL, 'SQL');

Output

'PostgreSQL'

Explanation

The COALESCE function in PostgreSQL returns the first non-NULL value in the list. In the example, ‘PostgreSQL’ is the first non-NULL value, so it is returned by the function.

SQL Server

COALESCE( expression [ ,…n ] )

• expression: This refers to any SQL expression from which the function COALESCE will return the first non-null value present. An expression can be a constant, variable, column, a non-complex statement that combines other values, or it can be a function.
• [, …n]: This parameter represents an optional series of additional SQL expressions that will be evaluated in sequence until a non-null value is encountered. This series of expressions can be of any number (n), provided they are separated by commas. This allows the COALESCE function to accept and evaluate multiple inputs, returning the first non-null expression found when read from left to right.

Example

SELECT COALESCE(NULL, 'SQL Server', NULL, 'Database')

Output

SQL Server

Explanation

The COALESCE function in SQL Server goes through its arguments from left to right and returns the first non-NULL argument. In the provided example, the function finds ‘SQL Server’ as the first non-NULL argument hence it returns ‘SQL Server’.

Oracle

COALESCE(expression1, expression2, …, expression_n)

• expression1: This is the first expression value examined. It is the primary value COALESCE will return if it is not NULL.
• expression2: This is the second expression value. If Expression1 is NULL, then Oracle Database will attempt to return this value.
• expression_n: You can input any number of expressions up to n. Oracle Database will evaluate them in order and return the first non-NULL value. If all expressions are NULL, the function will return NULL.

Example

SELECT COALESCE(NULL, 'Hello', 'World') AS Result FROM dual;

Output

Result
------
Hello

Explanation

The COALESCE function returns the first non-null expression among its arguments. In this example, it returns the first non-null argument ‘Hello’, because the first argument is NULL.

SQLite

COALESCE(value1, value2, …, valueN)

• value1: The first expression in the sequence to evaluate. COALESCE returns the result of this expression if it is non-null.
• value2: The second expression to be evaluated if the first returns a null value. If this expression is non-null, COALESCE returns its result.
• …, valuen: A series of additional expressions to be evaluated in sequence, if the preceding expressions return null values. If an expression is non-null, COALESCE returns its result. This sequence can contain any number of expressions, up to the maximum query length.

Example

CREATE TABLE Payroll(id INTEGER, Salary INTEGER);
INSERT INTO Payroll(id, Salary) VALUES(1, 5000);
INSERT INTO Payroll(id, Salary) VALUES(2, NULL);
SELECT id, COALESCE(Salary, 4000) AS Salary FROM Payroll;

Output

id | Salary
--------------
1  | 5000
2  | 4000

Explanation

The COALESCE function in SQLite returns the first non-NULL value amongst its arguments. In this example, it replaced NULL Salary with 4000 for the second record.