SQL - SPLessons

SQL Case

Chapter 44

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQL Case

SQL Case

shape Description

SQL Case statement is like IF THEN ELSE statement. It can be used in a SQL query statement.

And case statements can be classified to 2 types:

Simple Case Expression

shape Description

In simple case expression, contrast an expression with static qualities.

Search Case Expression

shape Description

In search case expressions, where an expression can be contrasted to multiple conditions consistently. 

shape Conceptual
figure

shape Syntax

The syntax for case statement is as follows:

CASE expression
WHEN value1 then result1
WHEN value2 then result2
.
.
.
ELSE result
END

If value of expression is equal to value_1 then it will show result_set_1 and like that it will check for all the values mentioned. If it is not matched with no values then it will set the default value mentioned in ELSE part.

shape Examples

SELECT order_id,
CASE supplier_id
  WHEN 'S1001' THEN 'DELL'
  WHEN 'S1002' THEN 'LENOVO'
  ELSE 'NO_ITEM' END FROM orders;

Case statement can be used as following:

SELECT student_id CASE WHEN marks BETWEEN 70 AND 80 THEN 'General Incentive' WHEN marks > 80 Then 'Highest Incentive' ELSE 'No Incentive' END FROM student_details;

Summary

shape Key Points


  • SQL CASE Statement – Similar to IF THEN ELSE statement.