SQLite | Query Data | Return Conditional Results with CASE

Tags:

• SQLite
• Database

A CASE expression returns different values based on conditions or column values. This article explains both forms.

Conditional Branching with CASE

There are two main forms. The first evaluates conditions and returns the result for the first true condition.

SELECT
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result3
  END
FROM table_name;

If condition 1 is true, result 1 is returned; otherwise condition 2 is checked. If none is true, the ELSE result is returned.

–

Create the following table for an example.

create table exam (name text, result integer);

sqlite> create table exam (name text, result integer);
sqlite> 

Insert data into the table.

insert into exam values ('devkuma', 85);
insert into exam values ('kimkc', 54);
insert into exam values ('araikuma', 78);
insert into exam values ('happykuma', 98);
insert into exam values ('mykuma', 48);
insert into exam values ('raccoon', 68);

sqlite> insert into exam values ('devkuma', 85);
sqlite> insert into exam values ('kimkc', 54);
sqlite> insert into exam values ('araikuma', 78);
sqlite> insert into exam values ('happykuma', 98);
sqlite> insert into exam values ('mykuma', 48);
sqlite> insert into exam values ('raccoon', 68);
sqlite> 

Use CASE to return one of three values based on result, and alias the result column as judgment.

select
  name, result, 
  case
    when result > 80 then 'Pass'
    when result > 60 then 'ReTest'
    else 'Fail'
  end as judgment
from exam;

sqlite> select
   ...>   name, result, 
   ...>   case
   ...>     when result > 80 then 'Pass'
   ...>     when result > 60 then 'ReTest'
   ...>     else 'Fail'
   ...>   end as judgment
   ...> from exam;
name        result      judgment  
----------  ----------  ----------
devkuma     85          Pass      
kimkc       54          Fail      
araikuma    78          ReTest    
happykuma   98          Pass      
mykuma      48          Fail      
raccoon     68          ReTest    
sqlite> 

Values are returned according to the conditions.

Comparing Values with CASE

The second form compares an expression with multiple values in sequence.

SELECT
  CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE result3
  END
FROM table_name;

It returns result 1 for value 1, result 2 for value 2, and the ELSE result when nothing matches.

If condition 1 is true, result 1 is returned; otherwise condition 2 is checked. If none is true, the ELSE result is returned.

–

Create the following table for an example.

create table company (name text, address text);

sqlite> create table company (name text, address text);
sqlite> 

Insert data into the table.

insert into company values ('A_Electric', 'Seoul');
insert into company values ('B_Motor', 'California');
insert into company values ('C_Mobile', 'Suwon');
insert into company values ('D_Shipping', 'Beijing');

sqlite> insert into company values ('A_Electric', 'Seoul');
sqlite> insert into company values ('B_Motor', 'California');
sqlite> insert into company values ('C_Mobile', 'Suwon');
sqlite> insert into company values ('D_Shipping', 'Beijing');
sqlite> 

Use CASE to return different results for address, and alias the result column as Sales.

select
  name, address, 
  case address
    when 'California' then 'America'
    when 'Seoul' then 'Korea'
    when 'Beijing' then 'China'
    else 'Unknown'
  end as country
from company;

sqlite> select
   ...>   name, address, 
   ...>   case address
   ...>     when 'California' then 'America'
   ...>     when 'Seoul' then 'Korea'
   ...>     when 'Beijing' then 'China'
   ...>     else 'Unknown'
   ...>   end as country
   ...> from company;
name        address     country   
----------  ----------  ----------
A_Electric  Seoul       Korea     
B_Motor     California  America   
C_Mobile    Suwon       Unknown   
D_Shipping  Beijing     China     
sqlite> 

Values are returned according to the conditions.