SQLite coalesce

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

SQLite COALESEC() pictorial presentation

Example : SQLite coalesce() function

  1. sqlite> SELECT coalesce(NULL, 2, 3);  
coalesce(NULL, 2, 3)
--------------------
2
  1. sqlite> SELECT coalesce(NULLNULLNULL);  
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).

  1. SELECT pub_id,coalesce(estd,country,pub_city)  
  2. FROM publisher;  
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

pub_id pub_name pub_city country country_office no_of_branch estd
P001 Jex Max Publication New York USA New York 15 1969-12-25
P002 BPP Publication Mumbai India New Delhi 10 1985-10-01
P003 New Harrold Publication Adelaide Australia Sydney 6 1975-09-05
P004 Ultra Press Inc. London UK London 8 1948-07-10
P005 Mountain Publication Houstan USA Sun Diego 25 1975-01-01
P006 Summer Night Publication New York USA Atlanta 10 1990-12-10
P007 Pieterson Grp. of Publishers Cambridge UK London 6 1950-07-15
P008 Novel Publisher Ltd. New Delhi India Bangalore 10 2000-01-01

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 :

  1. sqlite> SELECT ifnull('Red''Green');  
ifnull('Red', 'Green')
----------------------
Red
  1. sqlite> SELECT ifnull(NULL'Green');  
ifnull(NULL, 'Green')
---------------------
Green
  1. sqlite> SELECT coalesce(NULL'Green');  
coalesce(NULL, 'Green')
-----------------------
Green
  1. sqlite> SELECT coalesce(NULL'Red''Green');  
coalesce(NULL, 'Red', 'Green')
------------------------------
Red
  1. sqlite> SELECT coalesce(NULLNULLNULL'Red');  
coalesce(NULL, NULL, NULL, 'Red')
---------------------------------
Red