Home » SQL CASE STATEMENT

SQL CASE STATEMENT

The CASE statement has the functionality of an IF-THEN-ELSE. It goes through conditions and returns a value when the first condition is met . So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.





If there is no ELSE part and no conditions are true, it returns NULL.

There are two different types of format that can be used in CASE statement.

The simple CASE statement expression: It evaluates one expression against multiple values, once the condition and expression are matched, it returns the expression that is provided in THEN clause.

The searched CASE expression: It evaluates a condition using a comparison operators ,if condition is satisfied, It returns an expression corresponding THEN in the output.

Syntax

--Simple case expression:

CASE input_expression 
WHEN when_expression THEN result_expression [ ...n ] 
[ ELSE else_result_expression ] 
END

--Searched CASE expression:

CASE WHEN Boolean_expression 
THEN result_expression [ ...n ] 
[ ELSE else_result_expression ] 
END

Input_expression
Is the expression that is evaluated when the simple CASE format is used.

WHEN when_expression
Is a simple expression to which input_expression is compared when the simple CASE format is used.  The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

THEN result_expression
Is the expression that is returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE.

ELSE else_result_expression
Is the expression that is returned if no comparison operation evaluates to TRUE. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

WHEN Boolean_expression
Is the Boolean expression that is evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

Lets Look at an example of CASE Statement in SQL Server.

CREATE TABLE ITEM_DETAILS (ID INT NOT NULL, 
ITEM VARCHAR(50), 
ITEM_QTY INT, 
ITEM_PURCHASED_DATE DATETIME)

INSERT INTO ITEM_DETAILS (ID, ITEM, ITEM_QTY, ITEM_PURCHASED_DATE)
VALUES (1, 'Scanner', 300, '2019-12-01 00:00:00.000'),
(2, 'Scanner', 250, '2019-12-10 00:00:00.000'),
(3, 'Scanner', 110, '2019-11-12 00:00:00.000'),
(4, 'Scanner', 110, '2019-09-19 00:00:00.000'),
(5, 'Scanner', 280, '2019-09-25 00:00:00.000'),
(6, 'Scanner', 180, '2019-08-15 00:00:00.000'),
(7, 'Scanner', 180, '2019-07-08 00:00:00.000'),
(8, 'Pen Drive', 360, '2019-12-15 00:00:00.000'),
(9, 'Pen Drive', 270, '2019-09-11 00:00:00.000'),
(10, 'Pen Drive', 210, '2019-08-22 00:00:00.000'),
(11, 'Pen Drive', 190, '2019-08-19 00:00:00.000'),
(12, 'Pen Drive', 480, '2019-07-15 00:00:00.000'),
(13, 'Pen Drive', 680, '2019-06-25 00:00:00.000'),
(14, 'Pen Drive', 120, '2019-06-18 00:00:00.000'),
(15, 'Hard Disk', 270, '2019-09-19 00:00:00.000'),
(16, 'Hard Disk', 210, '2019-08-12 00:00:00.000'),
(17, 'Hard Disk', 190, '2019-08-29 00:00:00.000'),
(18, 'Hard Disk', 480, '2019-07-25 00:00:00.000'),
(19, 'Hard Disk', 680, '2019-06-15 00:00:00.000'),
(20, 'Hard Disk', 120, '2019-06-28 00:00:00.000')

A simple CASE statement expression

It evaluates one expression against multiple values, once the condition and expression are matched, it returns the expression that is provided in THEN clause.

Suppose, you want to display a short code of an item in result set such as If item is ‘SCANNER’ then code will be ‘SCN’, If ‘PEN DRIVE’ then ‘PD’, and ‘HD’ for ‘HARD DISK’.

So it can be done using simple case statement that will evaluates Item value against multiple given values and if it is matched then it returns output provided in THEN.

Following simple case expression evaluates Item value against multiple given values and return the Short code that is given in THEN, If condition and expression are matched.

 SELECT ID, ITEM, CASE ITEM 
  WHEN 'SCANNER' THEN 'SCN'
  WHEN 'PEN DRIVE' THEN 'PD'
  WHEN 'HARD DISK' THEN 'HD'
  ELSE 'NOT MATCHED' END CODE, ITEM_PURCHASED_DATE
FROM ITEM_DETAILS

As you can see Code column that contain the short code for an item.

The searched CASE expression:

It evaluates a condition using comparison operators, if condition is satisfied, It returns an expression corresponding THEN in the output.

Suppose, you want to set a quantity status based on the range of a quantity such as If quantity is between >=1 and <= 100 then status will be ‘Low Supply’, If quantity is between >100 and <=500 then status will be ‘Medium Supply’ , and status will be ‘High Supply’ for all quantity >500.

Following Search case expression returns the status based on the comparsion of quanity and returns the status mentioned in THEN, if comparsion expression is satisfied.

 SELECT ID, ITEM, ITEM_QTY,CASE 
   WHEN ITEM_QTY >=1 AND ITEM_QTY <=100 THEN 'LOW SUPPLY'
   WHEN ITEM_QTY >100 AND ITEM_QTY <=500 THEN 'MEDIUM SUPPLY'
   WHEN ITEM_QTY >500 THEN 'HIGH SUPPLY'
   ELSE 'NOT SATISIFED' END STATUS, ITEM_PURCHASED_DATE
FROM ITEM_DETAILS

As you can see, it evaluates an expression and returns the status for each quantity.

Lets take a look on few example using CASE statement

CASE Statement with ORDER BY Clause

Sometime we have a requirement to sort the result set returned by select statement in a custom order , like to display any particular column value at top of result set, while remaining can be in standard sorting order.

Following statement displays the ITEM– ‘HARD DISK’ always at on the top of  in sorting result set , regardless whatever the sorting order is used in select statement .

 SELECT ID, ITEM, ITEM_QTY, ITEM_PURCHASED_DATE
   FROM ITEM_DETAILS
 ORDER BY 
   CASE ITEM WHEN 'HARD DISK' THEN 1 ELSE 2 END

 

 

As you can see, all Item name that have value ‘HARD DISK’ is at on the TOP in sorting order, and remaing item name values follows the standard sorting order.

If you want read it in detail, read here – Custom sorting using case statement in Order By Clause




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.