{"id":1054,"date":"2016-05-26T20:36:37","date_gmt":"2016-05-26T13:36:37","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1054"},"modified":"2020-04-11T08:58:13","modified_gmt":"2020-04-11T01:58:13","slug":"sqlite-nullif","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-functions\/sqlite-nullif\/","title":{"rendered":"SQLite NULLIF"},"content":{"rendered":"\n

The SQLite NULLIF function accepts two arguments and returns a NULL value if they are equal.<\/p>\n\n\n\n

If the first argument is not equal to the second one, the NULLIF function returns the first argument. In case both arguments are NULL, the NULLIF function returns a NULL value.<\/p>\n\n\n\n

Syntax<\/h2>\n\n\n\n

The following illustrates the syntax of the NULLIF function:<\/p>\n\n\n

NULLIF(parameter_1,parameter_2);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Logically, the NULLIF function is equivalent to the CASE<\/a> expression:<\/p>\n\n\n
CASE WHEN parameter_1 = parameter_2 THEN \u00a0NULL ELSE expr1 END<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Arguments<\/h2>\n\n\n\n
The NULLIF function accepts exactly two arguments.<\/p>\n\n\n\n
Return Type<\/h2>\n\n\n\n
The NULLIF function returns a value with the type of the first argument or NULL.<\/p>\n\n\n\n
Examples<\/h2>\n\n\n\n
We often use the NULLIF function when the database contains “special” values such as zero or empty string that we want to handle them as NULL values. This is very useful when we use the aggregate functions such as AVG<\/a>, MAX<\/a>, MIN<\/a>, SUM<\/a>, and COUNT<\/a>.<\/p>\n\n\n\n
Let\u2019s take a look at the following example.<\/p>\n\n\n\n
First, create a new products table<\/a> that consists of three columns: name, price, and discount.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> products (\n    name<\/span>     TEXT<\/span>    NOT<\/span> NULL<\/span>,\n    price    NUMERIC<\/span> NOT<\/span> NULL<\/span>,\n    discount NUMERIC<\/span> DEFAULT<\/span> 0<\/span>,\n    CHECK<\/span> (price >= 0<\/span> AND<\/span> \n           discount >= 0<\/span> AND<\/span> \n           price > discount) \n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> products(name<\/span>,price,discount)\nVALUES<\/span>('Apple iPhone'<\/span>, 700<\/span>, 0<\/span>), \n      ('Samsung Galaxy'<\/span>,600<\/span>,10<\/span>), \n      ('Google Nexus'<\/span>,399<\/span>,20<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, to count the number of products that have discount, we use the NULLIF function as follows:<\/p>\n\n\n
SELECT<\/span> COUNT<\/span>(NULLIF<\/span>(discount,0<\/span>)) discount_products\nFROM<\/span> products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
discount_products\n-----------------<\/span>\n2<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> count<\/span>(*)\nFROM<\/span> products\nWHERE<\/span> discount > 0<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> COUNT<\/span>(CASE<\/span>\n    WHEN<\/span> discount = 0<\/span> THEN<\/span>\n    NULL<\/span>\n    ELSE<\/span> 1<\/span> END<\/span>)\nFROM<\/span> products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
See also<\/h2>\n\n\n\n
IFNULL, COALESCE<\/p>\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
Or you can use the CASE expression<\/a> instead:<\/p>\n\n\n
It is equivalent to the following query<\/a>:<\/p>\n\n\n
Second, insert some sample data into<\/a> the products<\/code> table.<\/p>\n\n\n