SQLite CASE

Definition of SQLite CASE Statement

SQLite provides the case statement that means we can add case expression with conditional logic into the SQL statement. Basically, SQLite case expression executes the list of different conditions and it returns an expression that depends on the outcome of the execution. Working of SQLite case statements is the same as IF – THEN – ELSE statements like other programming languages. SQLite case statement we can use in any clause or SQL statement if the expression is valid, it included different clauses such as WHERE, ORDER BY, HAVING and SELECT as well as it also support the different SQL statement such as SELECT, UPDATE and DELETE. SQLite provides two different forms of the case statements.

Syntax:

case specified test expression
when [specified condition.1] then [specified expression. 1] when [specified condition.2] then [specified expression. 2] …………
when [specified condition. N] then [specified expression. N] else [specified expression] end

Explanation:

In the above syntax, we define more than one condition to get the required result. In the above syntax, we use when and then clauses and that work in an orderly manner. The execution above syntax is that it first executes condition 1 if this condition is true then it executes the expression 1 otherwise it will execute condition 2 and so on. If the condition is false then it will execute the else part of the syntax.

How CASE statement work in SQLite?

Now let’s see how the case statement works in SQLite as follows.

Normally there are two forms of the case statement in SQLite as follows.

SQLite simple case expression

The syntax of this we already mentioned in the above syntax. In this method, it compares each expression with the list of expressions to return the end output. This is a very simple method to execute the case statement as per the requirement of the user. Here each condition and each expression depends on each other that means if the first condition is true then and then only it executes expression, in this way it executes all conditions and expression and if a condition is false then control transfer to the else part as shown in the above syntax.

Search case expression

The search case expression assesses a rundown of expression to choose the outcome. Note that the straightforward case expression just analyzes for equity, while the looked-through case expression can utilize any type of examination.

Syntax:

case specified test expression
when [specified Boolean expression.1] then [specified expression. 1] when [specified Boolean expression.2] then [specified expression. 2] else [specified expression] end

Search case expression assesses the Boolean expression in the grouping indicated and returns the relating result if the expression assesses to valid.

In the event that no expression assesses to valid, the search case expression returns the expression in the ELSE condition whenever indicated. In the event that you overlook the ELSE statement, the looked-through case expression brings NULL back.

Like the straightforward case expression, search case expression stops the assessment when the specified condition is met and execution will be stopped.

Example

Now let’s see the different examples of SQLite case statements as follows.

First, create a new table by using the following create table statement as follows.

create table stud (stud_id integer primary key, stud_name text not null, stud_email text not null, stud_mark float not null);

Explanation

In the above example, we created a new table name as stud different attributes such as stud_id with integer data type and primary key, stud_name with text data type and not null, stud_email with text data type and not null, and stud_mark with float data type and not null constraint. End out of the above statement as shown in the below screenshot as follows.

.table

Now insert some records into the stud table by using insert into the statement as follows.

insert into stud (stud_id, stud_name, stud_email, stud_mark) values (1, “Jay”, “[email protected]”, 90), (2, “Johan”, “[email protected]”, 85), (3, “Jenny”,”[email protected]”, 70), (4, “Sameer”, “[email protected]”, 63), (5, “Pooja”, “[email protected]”, 50);
select * from stud;

Explanation

With the help of the above statement, we inserted some records into the stud table successfully. End out of above statement as shown in below screenshot as follows.

Now use a simple SQLite case statement as follows.

select stud_id, stud_name, stud_mark,
case
when stud_mark >= 85 then “A+”
when stud_mark >= 70 then “A”
when stud_mark >= 60 then “B”
when stud_mark >= 50 then “C”
else “You are Fail”
end as “grade”
from stud;

Explanation

In the above example first, it check stud_mark greater than or equal to 85 if this condition is true then it shows A+, if condition false then it check second condition that is stud_mark greater than 70 then it shows A. if condition is false then control passes to third condition that is stud_mark greater than or equal to 60 then it shows B. if condition is false then it check stud_mark greater than or equal 50 then it shows C and if all condition is not satisfied then it execute the else part of above SQL statement that is You are Fail. End out of above statement as shown in below screenshot as follows.

Now let’s see the example of the second method that SQLite search case statement as follows.

We have an already created table that is stud, so directly use SQLite search case statement as follows.

select stud_id, stud_name, stud_mark,
case
when stud_mark > 86 then “A+”
when stud_mark > 70 and stud_mark < 60 then “A”
else
“C”
end “grade”
from stud;

Explanation

In the above example, we use a Boolean search case statement as shown in the above statement, here first check stud_mark greater than 86 then it prints A+. if the condition is false then is check the second condition that is stud_mark greater than 70 and stud_mark less than 60 then it prints A and if both conditions is false then it executes else statement. End out of above statement as shown in below screenshot as follows.

Conclusion

We hope from this article you have understood about the SQLite case. From the above article, we have learned the basic syntax of case statements and we also see different examples of case statements. We also learned the rules of case statements. From this article, we learned how and when we use the SQLite case statement.

Recommended Articles

This is a guide to SQLite CASE. Here we discuss the Definition, How CASE statement work in SQLite? examples with code implementation. You may also have a look at the following articles to learn more –

1. SQL pivot
2. SQL Formatter
3. SQL DESCRIBE TABLE
4. SQL Delete View