SQLite CASE() Function
Like the IF statement, The SQLite CASE() function checks conditions and returns a value. It starts with checking the first condition and keep on checking conditions until it finds the condition true (like an IF-THEN-ELSE statement). Once the condition is found to be true, it stops checking the further conditions and returns the value. If no condition is found to be true, then it returns the value in the ELSE clause. If the ELSE clause is not defined, it returns NULL.
Syntax
The syntax for using CASE statement in SQLite is given below:
CASE WHEN Condition1 THEN Value1 WHEN Condition2 THEN Value2 WHEN Condition3 THEN Value3 ... ELSE Value END
Return Value
The CASE statement returns any datatype such as a string, numeric, date, etc. If no condition is found to be true, then the CASE statement will return the value in the ELSE clause. If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.
Example:
Consider a database table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
-
To get the Name and Salary data of the employees categorized by salary (CASE statement is used to define category), the following statement can be used:
SELECT Name, City, Salary, CASE WHEN Salary < 2800 THEN 'Salary is less than 2800.' WHEN Salary > 2800 THEN 'Salary is greater than 2800.' WHEN Salary = 2800 THEN 'Salary is 2800.' ELSE 'Salary Data missing.' END AS SalaryinCategory FROM Employee;
This will produce the result as shown below:
Name City Salary SalaryinCategory John London 3000 Salary is greater than 2800. Marry New York 2750 Salary is less than 2800. Jo Paris 2800 Salary is 2800. Kim Amsterdam 3100 Salary is greater than 2800. Ramesh New Delhi 3000 Salary is greater than 2800. Huang Beijing 2800 Salary is 2800. -
Consider the example below where the CASE statement is used to order by result based on given condition.
SELECT * FROM Employee ORDER BY (CASE WHEN Age <= 27 THEN Age ELSE City END);
This result of the following code will be:
EmpID Name City Age Salary 2 Marry New York 24 2750 1 John London 25 3000 3 Jo Paris 27 2800 4 Kim Amsterdam 30 3100 6 Huang Beijing 28 2800 5 Ramesh New Delhi 28 3000
❮ SQLite Functions