SQLite CASE statement is a control flow statement that allows you to execute a block of SQL code based on a specified condition. SQLite CASE corresponds to IF-THEN-ELSE in other programming languages.
The SQLite case statement has two forms:
The first form of SQLite case statement evaluates the expression and compares it with value. If the expression is equal to value, then SQLite returns statement. Otherwise, SQLite goes to the next WHEN clause and repeats the process. If the expression is never equal to any value, SQLite returns NULL.
CASE expression WHEN value THEN statement
The second form of SQLite case statement evaluates condition. If condition is true, then SQLite returns statement. Otherwise, SQLite goes to the next WHEN clause and repeats the process. If condition is never true, SQLite returns NULL.
CASE WHEN condition THEN statement
Syntax
Here is the syntax of SQLite case statement:
CASE expression WHEN value1 THEN statement1 WHEN value2 THEN statement2 ... [ ELSE statementN ] END
Example
Table of students
| student_id | first_name | last_name | birthday | address_id |
|---|---|---|---|---|
| 1 | Paul | Smith | 2002-10-14 | 100 |
| 2 | Charlotte | Jones | 2003-05-18 | 101 |
| 3 | Theodore | Hernandez | 2003-08-12 | 102 |
| 4 | Susan | Taylor | 2004-07-24 | NULL |
Table of student_address
| address_id | city | country |
|---|---|---|
| 100 | San Antonio | US |
| 101 | San Jose | US |
| 102 | Philadelphia | US |
| 103 | Austin | US |
| 104 | Boston | US |
| 105 | Seattle | US |
SELECT s.student_id, s.first_name, s.last_name, CASE WHEN s.address_id is not null THEN (SELECT sa.city FROM student_address sa WHERE sa.address_id=s.address_id) ELSE 'No city found' END address_id FROM students s;
Output
| student_id | first_name | last_name | address_name |
|---|---|---|---|
| 1 | Paul | Smith | San Antonio |
| 2 | Charlotte | Jones | San Jose |
| 3 | Theodore | Hernandez | Philadelphia |
| 4 | Susan | Taylor | No city found |
In the SQLite CASE example above, the address_id column is compared if it is null or contains values. If it contains values, then a SELECT statement will be made that will return the city from the student_address table. If the value of address_id is null then the ELSE clause will display ‘No city found’.
You can use SQLite case statement in the following SQLite statements:
– SELECT
– INSERT
– UPDATE
– DELETE
If you use SQLite case statement in an SQLite expression, SQLite returns the value of statement that matches value or condition. If there is no match, SQLite returns NULL.