{"id":679,"date":"2015-12-22T12:07:12","date_gmt":"2015-12-22T05:07:12","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=679"},"modified":"2022-04-03T15:03:08","modified_gmt":"2022-04-03T08:03:08","slug":"sqlite-case","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-case\/","title":{"rendered":"SQLite CASE"},"content":{"rendered":"\r\n

Summary: <\/strong>in this tutorial, you will learn about the SQLite CASE<\/code> expression to add the conditional logic to a query.<\/p>\r\n\r\n\r\n\r\n

The SQLite CASE<\/code> expression evaluates a list of conditions and returns an expression based on the result of the evaluation.<\/p>\r\n\r\n\r\n\r\n

The CASE<\/code> expression is similar to the IF-THEN-ELSE<\/code> statement in other programming languages.<\/p>\r\n\r\n\r\n\r\n

You can use the CASE<\/code> expression in any clause or statement that accepts a valid expression. For example, you can use the CASE<\/code> expression in clauses such as WHERE<\/a><\/code>, ORDER BY<\/code><\/a>, HAVING<\/a><\/code>, SELECT<\/a><\/code> and statements such as SELECT<\/a><\/code>, UPDATE<\/a><\/code>, and DELETE<\/a><\/code>.<\/p>\r\n\r\n\r\n\r\n

SQLite provides two forms of the CASE<\/code> expression: simple CASE<\/code> and searched CASE<\/code>.<\/p>\r\n\r\n\r\n\r\n

SQLite simple CASE expression<\/h2>\r\n\r\n\r\n\r\n

The simple CASE<\/code> expression compares an expression to a list of expressions to return the result. The following illustrates the syntax of the simple CASE<\/code> expression.<\/p>\r\n\r\n\r\n

CASE case_expression\r\n     WHEN when_expression_1 THEN result_1\r\n     WHEN when_expression_2 THEN result_2\r\n     ...\r\n     [ ELSE result_else ] \r\nEND<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The simple CASE<\/code> expression compares the case_expression<\/code> to the expression appears in the first WHEN<\/code> clause, when_expression_1<\/code>, for equality.<\/p>\r\n\r\n\r\n\r\n
If the case_expression<\/code> equals when_expression_1<\/code>, the simple CASE<\/code> returns the expression in the corresponding THEN<\/code> clause, which is the result_1<\/code>.<\/p>\r\n\r\n\r\n\r\n
Otherwise, the simple CASE<\/code> expression compares the case_expression<\/code> with the expression in the next WHEN<\/code> clause.<\/p>\r\n\r\n\r\n\r\n
In case no case_expression<\/code> matches the when_expression<\/code>, the CASE<\/code> expression returns the result_else<\/code> in the ELSE<\/code> clause. If you omit the ELSE<\/code> clause, the CASE<\/code> expression returns NULL.<\/p>\r\n\r\n\r\n\r\n
The simple CASE<\/code> expression uses short-circuit evaluation. In other words, it returns the result and stop evaluating other conditions as soon as it finds a match.<\/p>\r\n\r\n\r\n\r\n
Simple CASE example<\/h3>\r\n\r\n\r\n\r\n
Let’s take a look at the customers<\/code> table in the sample database<\/a>.<\/p>\r\n\r\n\r\n\r\n
\"customers\"<\/figure>\r\n\r\n\r\n\r\n
Suppose, you have to make a report of the customer groups with the logic that if a customer locates in the USA, this customer belongs to the domestic group, otherwise the customer belongs to the foreign group.<\/p>\r\n\r\n\r\n\r\n
To make this report, you use the simple CASE<\/code> expression in the SELECT<\/code> statement as follows:<\/p>\r\n\r\n\r\n
SELECT<\/span> customerid,\r\n       firstname,\r\n       lastname,\r\n       CASE<\/span> country \r\n           WHEN<\/span> 'USA'<\/span> \r\n               THEN<\/span> 'Domestic'<\/span> \r\n           ELSE<\/span> 'Foreign'<\/span> \r\n       END<\/span> CustomerGroup\r\nFROM<\/span> \r\n    customers\r\nORDER<\/span> BY<\/span> \r\n    LastName,\r\n    FirstName;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
SQLite searched CASE expression<\/h2>\r\n\r\n\r\n\r\n
The searched CASE<\/code> expression evaluates a list of expressions to decide the result. Note that the simple CASE<\/code> expression only compares for equality, while the searched CASE<\/code> expression can use any forms of comparison.<\/p>\r\n\r\n\r\n\r\n
The following illustrates the syntax of the searched CASE<\/code> expression.<\/p>\r\n\r\n\r\n
CASE\r\n     WHEN bool_expression_1 THEN result_1\r\n     WHEN bool_expression_2 THEN result_2\r\n     [ ELSE result_else ] \r\nEND<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The searched CASE<\/code> expression evaluates the Boolean expressions in the sequence specified and return the corresponding result if the expression evaluates to true.<\/p>\r\n\r\n\r\n\r\n
In case no expression evaluates to true, the searched CASE<\/code> expression returns the expression in the ELSE<\/code> clause if specified. If you omit the ELSE<\/code> clause, the searched CASE<\/code> expression returns NULL<\/code>.<\/p>\r\n\r\n\r\n\r\n
Similar to the simple CASE<\/code> expression, the searched CASE<\/code> expression stops the evaluation when a condition is met.<\/p>\r\n\r\n\r\n\r\n
Searched CASE example<\/h3>\r\n\r\n\r\n\r\n
We will use the tracks<\/code> table for the demonstration.<\/p>\r\n\r\n\r\n\r\n
\"\"<\/figure>\r\n\r\n\r\n\r\n
Suppose you want to classify the tracks based on its length such as less a minute, the track is short; between 1 and 5 minutes, the track is medium; greater than 5 minutes, the track is long.<\/p>\r\n\r\n\r\n\r\n
To achieve this, you use the searched CASE<\/code> expression as follows:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n\ttrackid,\r\n\tname<\/span>,\r\n\tCASE<\/span>\r\n\t\tWHEN<\/span> milliseconds < 60000<\/span> THEN<\/span>\r\n\t\t\t'short'<\/span>\r\n\t\tWHEN<\/span> milliseconds > 60000<\/span> AND<\/span> milliseconds < 300000<\/span> THEN<\/span> 'medium'<\/span>\r\n\t\tELSE<\/span>\r\n\t\t\t'long'<\/span>\r\n\t\tEND<\/span> category<\/span>\r\nFROM<\/span>\r\n\ttracks;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
In this tutorial, you have learned about SQLite CASE<\/code> expression to form conditional logic inside a SQL query.<\/p>\r\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
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n