The CASE statement is popularly known as CASE Expression, allows the user to execute a part of SQL statement conditionally. In SQL, CASE statement is used for storing the procedure or formula of a particular column. The CASE expression can be classified into two types namely:
- Simple CASE Statement
- Searched CASE Statement
Basic Syntax for Simple CASE Statement
CASE inp_exp1 /* It is just your column name */
WHEN condi_exp1 THEN reslt_exp1
[WHEN condi_exp2 THEN reslt_exp2]
[ELSE reslt_exp]
END
How CASE Expression Work in SQL?
In our CASE expression syntax, the WHEN conditional statement will be evaluated only if input_exp is similar to condi_exp. If the condi_exp is returned similar to the input_exp then the analogous result_exp will be executed. If one WHEN condition is executed then other conditions will not be executed. In other words, in CASE expression a condition which matches first will be executed.
What happens when conditions are not matched in CASE expression?
If conditions mentioned in WHEN condition are not matched with CASE expression then the expression mentioned in the ELSE condition will be displayed. If ELSE statement is not used then NULL value will be returned.
Simple CASE Expression
In simple CASE statement, a selector expression will be evaluated and will be matched against one or more WHEN condition. The basic syntax for simple CASE expression is given below.
CASE selector
WHEN cond1
THEN result1;
WHEN cond2
THEN result2;
ELSE
result3;
END CASE
Searched CASE Expression
Searched CASE expression does not have any selector expression and will attempt to match against more than one WHEN condition. The basic syntax for searched CASE expression is given below.
CASE
WHEN condition1 THEN
O/p 1; //Output 1
WHEN condition2 THEN
O/p 2; //Output 2
ELSE
O/p 3; //Output 3
END CASE;
Applications of CASE Expressions
CASE expressions can be applied in any place where scalar expressions are allowed. Scalar expressions like WHERE and HAVING clauses present in a SELECT statement are also allowed. CASE expressions are popularly used to find Stored procedures, View and Formula of particular column.
Example for CASE Expression
Consider the following code as an example, in the code given below when the player_id is matched with barce_id then the corresponding player name will be listed as output. If the player_id and barce_id are not matched then ELSE condition will be used and “not_a_barce_playerz” will be displayed as an output.
SELECT playerz_id,
CASE Barce_id
WHEN 1 THEN ‘Iniessta’
WHEN 2 THEN ‘Messsi’
ELSE ‘not_a_barce_playerz’
END
FROM teamm_roster;
The above code can be re-written without expression clause. The rewritten SQL code without expression clause in CASE statement is given below.
SELECT playerz_id,
CASE
WHEN Barce_id = 1 THEN ‘Iniessta’
WHEN Barce_id = 2 THEN ‘Messsi’
ELSE ‘not_a_barce_playerz’
END
FROM teamm_roster;
It is not mandatory to use ELSE condition in CASE expression because the ELSE condition will not be utilized when the conditional expression is matched with result expression. If expressions did not match then NULL will be returned as output. The SQL codes for our example without ELSE statement is given below.
SELECT playerz_id,
CASE Barce_id
WHEN 1 THEN ‘Iniessta’
WHEN 2 THEN ‘Messsi’
END
FROM contacts;
- Read: More Interview Questions
Use of CASE Expression in Table Column Formula
When table design mode is created, properties of each column with several property values like default column value and identity column value are set. The data manipulation program for the table can be customized by the user. The following SQL code can be used to create column formula for a table.
// This is an SQL CASE pucca statement
(case [Code] when (01) then ‘Place1_01′ when (02) then ‘Place2_02’ _
when(03) then ‘Place3_03’ when(04) then ‘Place4_04’ when(05) _
then ‘Place5_05’ when (06) then ‘Place6_06’ when (07) then ‘Place7_07’ _
when(08) then ‘Place8_08’ when(09) then ‘Place9_09’ else ‘Vacant’ end)
CASE Expressions are also used in viewing an object present in a table. To view the object in a table, scalar expressions like WHERE and HAVING clauses used along with SELECT statement is used in CASE expression.