{"id":2571,"date":"2020-07-08T15:44:22","date_gmt":"2020-07-08T08:44:22","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=2571"},"modified":"2020-07-08T15:45:06","modified_gmt":"2020-07-08T08:45:06","slug":"sqlite-iif","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-functions\/sqlite-iif\/","title":{"rendered":"SQLite IIF"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the SQLite IIF()<\/code> function that allows you to add the if-else logic to queries.<\/p>\n\n\n\n

Overview of SQLite IIF() function<\/h2>\n\n\n\n

SQLite introduced the IIF()<\/code> function since version 3.32.0. The following shows the syntax of the IIF()<\/code> function:<\/p>\n\n\n

IIF(expression, true_expression, false_expression);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the IIF()<\/code> function evaluates the expression<\/code> first. If the result is true<\/code>, the IIF()<\/code> function returns the value of the second expression (true_expression<\/code>). Otherwise, it returns the value of the third expression (false_expression<\/code>).<\/p>\n\n\n\n
The IIF()<\/code> function is equivalent to the following CASE<\/a><\/code> expression:<\/p>\n\n\n
CASE\n    WHEN expression\n        THEN true_expression\n    ELSE\n        false_expression\nEND<\/span>   <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In practice, you use the IIF()<\/code> function to add the if-else logic to queries to form more flexible queries.<\/p>\n\n\n\n
SQLite IIF() function examples<\/h2>\n\n\n\n
Let’s take some example of SQLite IIF()<\/code> function.<\/p>\n\n\n\n
1) Simple SQLite IIF() function example<\/h3>\n\n\n\n
The following query illustrates how to use the IIF()<\/code> function in a simple SELECT<\/code> statement:<\/p>\n\n\n
SELECT<\/span> IIF<\/span>(1<\/span> < 2<\/span>, 'Yes'<\/span>, 'No'<\/span> ) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
result\n----------\nyes\n<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using SQLite IIF() function to classify information<\/h3>\n\n\n\n
See the following tracks<\/code> table from the sample database:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following statement uses the IIF()<\/code> function to classify tracks in the tracks table by their lengths:<\/p>\n\n\n
SELECT<\/span> \n    name<\/span>,\n    milliseconds,\n    IIF<\/span>(milliseconds <= 300000<\/span>, 'Short'<\/span>,\n        IIF<\/span>(milliseconds > 300000<\/span> AND<\/span> milliseconds <= 600000<\/span>, 'Medium'<\/span>,\n            IIF<\/span>(milliseconds > 600000<\/span>, 'Long'<\/span>,'N\/A'<\/span>)\n        )\n    )\nFROM<\/span>\n    tracks;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use the SQLite IIF()<\/code> function to add if-else logic to the query.<\/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