How To Use the SQLite COALESCE Function

Summary: in this tutorial, you will learn how to use the SQLite COALESCE function to handle null values.

Introduction to the SQLite COALESCE function

The COALESCE function accepts two or more arguments and returns the first non-null argument. The following illustrates the syntax of the COALESCE function:

COALESCE(parameter1,parameter2, …);Code language: SQL (Structured Query Language) (sql)

If all the arguments are NULL, the COALESCE function returns NULL.

The COALESCE function is equivalent to the NVL function in Oracle or the IFNULL function in MySQL.

See the following basic examples:

SELECT COALESCE(10,20); -- return 10
SELECT COALESCE(NULL,20,10); -- returns 20Code language: SQL (Structured Query Language) (sql)

In practice, the COALESCE function is used to substitute a default value for a NULL value.

SQLite COALESCE examples

See the following customers table in the sample database.

The following query returns the first name, last name, and the company of the customers.

SELECT firstname,
       lastname,
       company
  FROM customers
 ORDER BY firstname;Code language: SQL (Structured Query Language) (sql)

In the customers table, the company information of the customer, who does not have company, is stored as NULL values.

You can use the COALESCE function to replace the NULL value by anther value e.g., individual as the following query:

SELECT firstname,
       lastname,
       coalesce(company, 'Individual') entity
  FROM customers
 ORDER BY firstname;Code language: SQL (Structured Query Language) (sql)

Let’s take a look at another example that we commonly see in the real scenarios.

First, creates a new table called memberships that stores the program name, net price, and discount.

CREATE TABLE IF NOT EXISTS memberships (
    membership_id INT     PRIMARY KEY,
    program_name  TEXT    NOT NULL,
    net_price     NUMERIC NOT NULL,
    discount      NUMERIC
);Code language: SQL (Structured Query Language) (sql)

Second, insert some sample data into the memberships table; use the NULL value for the membership that does not have the discount.

INSERT INTO memberships(program_name, net_price, discount)
VALUES('1 Month', 100, null),
      ('3 Months', 300, 10),
      ('6 Months', 600, 30);Code language: SQL (Structured Query Language) (sql)

Third, query data from the memberships table; calculate the amount that members have to pay by subtracting the discount from the net price.

SELECT program_name,
       (net_price - discount) AS amount
  FROM memberships;Code language: SQL (Structured Query Language) (sql)

The amount of the 1-month membership is NULL because the discount value is NULL. To avoid this error, you use the COALESCE function as follows:

SELECT program_name,
       (net_price - coalesce(discount, 0) ) AS amount
  FROM memberships;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQLite function to handle NULL values in the data.

Was this tutorial helpful ?