{"id":8623,"date":"2020-05-18T08:53:07","date_gmt":"2020-05-17T22:53:07","guid":{"rendered":"https:\/\/database.guide\/?p=8623"},"modified":"2020-05-28T13:44:03","modified_gmt":"2020-05-28T03:44:03","slug":"how-sqlite-ifnull-works","status":"publish","type":"post","link":"https:\/\/database.guide\/how-sqlite-ifnull-works\/","title":{"rendered":"How SQLite Ifnull() Works"},"content":{"rendered":"\n

The SQLite<\/a> ifnull()<\/code> function allows you to replace NULL values with another value.<\/p>\n\n\n\n

It takes two arguments, and it returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. <\/p>\n\n\n\n

The ifnull()<\/code> function is equivalent to coalesce()<\/a><\/code> with two arguments.<\/p>\n\n\n\n\n\n\n\n

Simple Example<\/h2>\n\n\n\n

Here’s a simple example to demonstrate the concept.<\/p>\n\n\n\n

SELECT \n  ifnull(123, 0),\n  ifnull(NULL, 0);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ifnull(123, 0)  ifnull(NULL, 0)\n--------------  ---------------\n123             0              <\/pre>\n\n\n\n
The second column was NULL and so 0<\/strong> was returned instead.<\/p>\n\n\n\n
Database Example<\/h2>\n\n\n\n
This example uses ifnull()<\/code> in a database query. This demonstrates how ifnull()<\/code> can be useful when querying data that may contain NULL values.<\/p>\n\n\n\n
Take the following table as an example:<\/p>\n\n\n\n
ProductId   ProductName    Price     \n----------  -------------  ----------\n1           Widget Holder  139.5     \n2           Widget Stick   89.75     \n3           Foo Cap        11.99     \n4           Free Widget    0.0       \n5           Free Foobar    0.0       \n6           Free Beer                <\/pre>\n\n\n\n
Most products have been populated with prices, but the price of Free Beer<\/strong> is NULL.<\/p>\n\n\n\n
We can change this to a value of our choosing.<\/p>\n\n\n\n
SELECT \n  ProductName,\n  ifnull(Price, 0.0)\nFROM Products;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ProductName    ifnull(Price, 0.0)\n-------------  ------------------\nWidget Holder  139.5             \nWidget Stick   89.75             \nFoo Cap        11.99             \nFree Widget    0.0               \nFree Foobar    0.0               \nFree Beer      0.0               <\/pre>\n\n\n\n
Now it has the same price as the other free products.<\/p>\n\n\n\n
The replacement value doesn’t necessarily need to be a number. You can also replace it with a string.<\/p>\n\n\n\n
SELECT \n  ProductName,\n  ifnull(Price, 'FREE!')\nFROM Products;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ProductName    ifnull(Price, 'FREE!')\n-------------  ----------------------\nWidget Holder  139.5                 \nWidget Stick   89.75                 \nFoo Cap        11.99                 \nFree Widget    0.0                   \nFree Foobar    0.0                   \nFree Beer      FREE!                 <\/pre>\n\n\n\n
When Both Arguments are NULL<\/h2>\n\n\n\n
If both arguments are NULL, then NULL is returned.<\/p>\n\n\n\n
SELECT ifnull(NULL, NULL);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ifnull(NULL, NULL)\n------------------\n                  <\/pre>\n","protected":false},"excerpt":{"rendered":"
The SQLite ifnull() function allows you to replace NULL values with another value. It takes two arguments, and it returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. The ifnull() function is equivalent to coalesce() with two arguments.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[100],"tags":[83,93],"class_list":["post-8623","post","type-post","status-publish","format-standard","hentry","category-sqlite","tag-comparison-functions","tag-functions"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/8623","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=8623"}],"version-history":[{"count":6,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/8623\/revisions"}],"predecessor-version":[{"id":8826,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/8623\/revisions\/8826"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=8623"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=8623"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=8623"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}