SQLite coalesce() function

Description

The coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL. The function must have at least 2 arguments.

Syntax:

coalesce(X,Y,...)

Pictorial Presentation

Example: SQLite coalesce() function

sqlite> SELECT coalesce(NULL, 2, 3);

Sample Output:

coalesce(NULL, 2, 3)
--------------------
2

sqlite> SELECT coalesce(NULL, NULL, NULL);

Sample Output:

coalesce(NULL, NULL, NULL)
--------------------------

The following SQLite statement returns date of establishment for the Jex Max Publication, BPP Publication, Ultra Press Inc., Night Publication and Novel Publisher Ltd. For New Harrold Publication,Mountain Publication Summer, Pieterson Grp. of Publishers, the query returns the country, since they don't have any date of establishment (NULL).

SELECT pub_id,coalesce(estd,country,pub_city)
FROM publisher;

Sample Output:

pub_id      coalesce(estd,country,pub_city)
----------  -------------------------------
P001        1969-12-25
P002        1985-10-01
P003        1975-09-05
P004        1948-07-10
P005        1975-01-01
P006        1990-12-10
P007        1950-07-15
P008        2000-01-01

Sample table: publisher

Difference between ifnull() and coalesce() function in SQLite

In SQLite, ifnull() takes two expressions and if the first expression is not NULL, it returns the first expression otherwise it returns the second expression whereas coalesce() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values. See the following examples:

sqlite> SELECT ifnull('Red', 'Green');

Sample Output:

ifnull('Red', 'Green')
----------------------
Red

sqlite> SELECT ifnull(NULL, 'Green');

Sample Output:

ifnull(NULL, 'Green')
---------------------
Green

sqlite> SELECT coalesce(NULL, 'Green');

Sample Output:

coalesce(NULL, 'Green')
-----------------------
Green

sqlite> SELECT coalesce(NULL, 'Red', 'Green');

Sample Output:

coalesce(NULL, 'Red', 'Green')
------------------------------
Red

sqlite> SELECT coalesce(NULL, NULL, NULL, 'Red');

Sample Output:

coalesce(NULL, NULL, NULL, 'Red')
---------------------------------
Red

Previous: char()
Next: glob()