{"id":1061,"date":"2016-05-26T21:09:49","date_gmt":"2016-05-26T14:09:49","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1061"},"modified":"2024-05-08T22:44:47","modified_gmt":"2024-05-08T15:44:47","slug":"sqlite-coalesce","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-functions\/sqlite-coalesce\/","title":{"rendered":"SQLite coalesce() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQLite coalesce()<\/code> function to handle null values.<\/p>\n\n\n\n

Introduction to the SQLite coalesce() function<\/h2>\n\n\n\n

The coalesce()<\/code> function accepts two or more arguments and returns the first non-null argument. <\/p>\n\n\n\n

Here’s the syntax of the coalesce()<\/code> function:<\/p>\n\n\n

coalesce(parameter1,parameter2, \u2026);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If all the arguments are NULL, the coalesce<\/code> function returns NULL.<\/p>\n\n\n\n
Please note that the coalesce()<\/code> function is equivalent to the NVL<\/a> function in Oracle or the IFNULL<\/a> function in MySQL.<\/p>\n\n\n\n
SQLite coalesce() function examples<\/h2>\n\n\n\n
Let’s take some examples of using the coalesce()<\/code> function.<\/p>\n\n\n\n
1) Basic SQLite coalesce() function examples<\/h3>\n\n\n\n
The following query uses the coalesce()<\/code> function to return the first non-null value, which is 10:<\/p>\n\n\n
SELECT COALESCE(10<\/span>,20<\/span>); -- return<\/span> 10<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement also uses the coalesce()<\/code> function to return the first non-null argument, which is 20:<\/p>\n\n\n
SELECT COALESCE(NULL<\/span>,20<\/span>,10<\/span>); -- returns 20<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using coalesce() function to substitute NULL<\/h3>\n\n\n\n
We’ll use the following customers<\/code> table in the sample database<\/a>:<\/p>\n\n\n\n
\"customers\"<\/figure>\n\n\n\n
The following query returns the first name, last name, and the company of the customers:<\/p>\n\n\n
SELECT<\/span> firstname,\n       lastname,\n       company\n  FROM<\/span> customers\n ORDER<\/span> BY<\/span> firstname;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQLite<\/figure>\n\n\n\n
In the customers<\/code> table, the company information of the customers may be NULL.<\/p>\n\n\n\n
But you can use the coalesce()<\/code> function to replace NULL with another value such as individual<\/code>. For example:<\/p>\n\n\n
SELECT<\/span> firstname,\n       lastname,\n       coalesce<\/span>(company, 'Individual'<\/span>) entity\n  FROM<\/span> customers\n ORDER<\/span> BY<\/span> firstname;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQLite<\/figure>\n\n\n\n
3) Using coalesce() function in expressions<\/h3>\n\n\n\n
First, create a new table<\/a> called memberships<\/code> that stores the program name, net price, and discount.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> memberships (\n    membership_id INT<\/span>     PRIMARY KEY<\/span>,\n    program_name  TEXT<\/span>    NOT<\/span> NULL<\/span>,\n    net_price     NUMERIC<\/span> NOT<\/span> NULL<\/span>,\n    discount      NUMERIC<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> memberships(program_name, net_price, discount)\nVALUES<\/span>('1 Month'<\/span>, 100<\/span>, null<\/span>),\n      ('3 Months'<\/span>, 300<\/span>, 10<\/span>),\n      ('6 Months'<\/span>, 600<\/span>, 30<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> program_name,\n       (net_price - discount) AS<\/span> amount\n  FROM<\/span> memberships;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQLite<\/figure>\n\n\n\n
The amount of the 1-month membership is NULL because the discount value is NULL. To avoid this error, you use the coalesce()<\/code> function as follows:<\/p>\n\n\n
SELECT<\/span> program_name,\n       (net_price - coalesce<\/span>(discount, 0<\/span>) ) AS<\/span> amount\n  FROM<\/span> memberships;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQLite<\/figure>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the coalesce()<\/code> function to return the first non-null argument.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Third, query data<\/a> from the memberships<\/code> table; calculate the amount that members have to pay by subtracting the discount from the net price.<\/p>\n\n\n
Second, insert some sample data<\/a> into the memberships table; use the NULL value for the membership that does not have the discount.<\/p>\n\n\n