In SQLite, the coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL.

It’s similar to the ifnull() function, except that coalesce() accepts more than two arguments (ifnull() only accepts two arguments).

Syntax

The syntax goes like this:

coalesce(X,Y,...)

What this means is, you can provide two or more arguments. The function will then return the first one that’s not NULL.

Example

Here’s an example to demonstrate.

SELECT coalesce(NULL,1);

Result:

1

Here I provided two arguments and coalesce() returned the first non-NULL value, which in this case is 1.

More Examples

Here are more variations to demonstrate how coalesce() handles each case.

.mode line
SELECT
  coalesce(NULL,NULL,1,2,3),
  coalesce(1,NULL,2,3),
  coalesce(NULL,3,2,1),
  coalesce(1,2,3,NULL),
  coalesce(NULL,NULL);

Result:

coalesce(NULL,NULL,1,2,3) = 1
     coalesce(1,NULL,2,3) = 1
     coalesce(NULL,3,2,1) = 3
     coalesce(1,2,3,NULL) = 1
      coalesce(NULL,NULL) = 

So it’s very straightforward. It simply returns the first argument that is not NULL, regardless of how many arguments are provided.

Also notice that if all arguments are NULL, then it returns NULL.

Database Example

Here’s an example of using the coalesce() function in a database query.

.mode column
SELECT 
  CustomerId, 
  coalesce(Fax, 'N/A') AS Fax
FROM Customer
LIMIT 5;

Result:

CustomerId  Fax          
----------  -------------
1           +55 (12) 3923
2           N/A          
3           N/A          
4           N/A          
5           +420 2 4172 5

Here’s what it looks like without using the coalesce() function:

.mode column
SELECT 
  CustomerId, 
  Fax
FROM Customer
LIMIT 5;

Result:

CustomerId  Fax               
----------  ------------------
1           +55 (12) 3923-5566
2                             
3                             
4                             
5           +420 2 4172 5555  

So the coalesce() function can be useful for returning a meaningful string instead of NULL.

With Three Arguments

Here’s an example of adding a third argument. This allows us to run through multiple options before arriving at NULL.

SELECT 
  CustomerId, 
  coalesce(Fax, Email, 'N/A') AS "Fax/Email"
FROM Customer
LIMIT 5;

Result:

CustomerId  Fax/Email             
----------  ----------------------
1           +55 (12) 3923-5566    
2           [email protected] 
3           [email protected]   
4           [email protected] 
5           +420 2 4172 5555