SQL Case Statement Explained [Practical Examples]

The SQL Case Statement

SQL Case statement is an SQL’s conditional statement like if...then...else of programming language. The CASE statement checks conditions and returns a value when the condition is met true it returns the value specified after then clause. So, once a condition is true, it will stop reading and return the result. If none of the conditions are true, it execute the ELSE part and returns the value in the ELSE clause.

 

The CASE Statement in SQL has two formats

1. The Simple SQL CASE Statement: It compares an expression with a conditional expression to determine the result value.
2. The searched SQL CASE Statement: It searched SQL CASE expression evaluates a set of Boolean expressions to determine the result value.

An ELSE clause argument is optional in both the above SQL CASE Statement format.

We can use SQL CASE Statement with Other SQL Statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

 

Simple SQL CASE Statement

The simple CASE expression worked by comparing the first conditional expression to the case expression in each WHEN clause and if these expressions values are equivalent than the result value of the THEN clause will be returned. It only worked on equality operation.

If none of the conditional expression is matched with case expression than the ELSE result value is returned or if ELSE clause is not mentioned than SQL will return NULL value.

 Simple SQL CASE Statement Syntax: 

CASE Case_expression  
WHEN Conditional_expression THEN result_value  
WHEN Conditional_expression THEN result_value [ ...n]  
[ ELSE else_result_value ]  
END

 

Searched SQL CASE Statement  

The searched SQL Case statement comparing Boolean expression for each WHEN clause in the order it has been specified. If the first Boolean expression is evaluated as TRUE than it will return result value of the same THEN Clause.

If none of the Boolean expression is evaluated as TRUE than it will return result value of ELSE clause or if ELSE clause is not mentioned in SQL CASE statement than it will return NULL value.

The searched SQL CASE Statement Syntax: 

CASE  
WHEN Boolean_expression THEN result_value  
WHEN Boolean_expression THEN result_value [ ...n]  
[ ELSE else_result_value ]  
END

Here,

• CASE expression : It is a valid SQL expression or Variable which is going to evaluated with the simple SQL CASE format.
• WHEN conditional_expression : It Is a valid SQL conditional expression which is compared with case_expression with the simple SQL Case format. The data types of case_expression and each when_expression must be the same or must be converted implicitly.
• THEN result_value : It Is any valid expression or value which is going to returned when case_expression matched condition of  conditional_expression ,  or matched with Boolean_expression evaluates to be TRUE.
• ELSE else_result_value : It is an any valid value or expression which is going to returned if none of the condition is evaluated as TRUE. If ELSE part in not specified in SQL CASE statement than Case will return NULL. The data types of else_result_value and any result_value must be the same or must be converted implicitly.
• WHEN Boolean_expression : It Is an any valid Boolean expression which used with the searched SQL Case statement which evaluated and if returns true result_value is returned.

The SQL CASE expression cannot be worked with Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

Note that SQL Server allows for only 10 levels of nesting in CASE expressions.

 

SQL Case Statement Examples

Let's consider two tables for examples of SQL Case statement:
Employee Table (tblemp)

Department Table (tbldept)

 

Example-1: SQL Case Statement with Select command

Use SQL Case statement with SELECT query to fetch to display message of “Salary greater than 20000’ with emp_id

SELECT Emp_id, Salary,  
CASE WHEN Salary > 20000 THEN 'The Salary is greater than 20000'  
WHEN Salary = 20000 THEN 'The Salary is 20000'  
ELSE 'The Salary is under 20000'  
END AS Salaryamount 
FROM tblemp;

• In above Case statement query with SELECT command will return emp_id and salary details with one text as salary amount which is a result value of conditional expression of Salary >20000, Salary=2000.
• After writing the query, click on the execute SQL button to execute query.

OUTPUT: 

 

Example-2: SQL Case Statement with Order by clause

We can apply Order by clause with SELECT Command in SQL Case statement will sort result value in ascending or in descending order.

Extract employee data with department id and department name where department name will be the resulting value based on case condition in order of dept_id, execute it using SQL Case statement.

SELECT Emp_id, Emp_name,  
CASE Dept_id  
WHEN '10001' THEN 'Account'  
WHEN '10002' THEN 'Marketing'  
ELSE 'Department not found'  
END AS Department 
FROM   tblemp 
ORDER BY Dept_id;

• Preceding SQL query used case statement with SELECT command which gives name to each dept_id and we have also put ORDER BY clause to display result in order of department id.

• After writing the query, click on the execute SQL button to execute query.

OUTPUT:

 

Example-3: Searched SQL CASE Statement with SELECT Command

In the following example we are retrieving employee id and name and also used searched SQL Case statement to give employee designation based on searched expression value

SELECT Emp_id, Emp_name,  
CASE WHEN Salary = 20000 THEN 'Analyst'  
WHEN Salary = 30000 THEN 'Manager'  
WHEN Salary = 35000 THEN 'Director'  
WHEN Salary = 25000 THEN 'Accountant'  
WHEN Salary < 20000 THEN 'Office Staff'  
ELSE 'Designation not defined' 
END AS Designation 
FROM tblemp;

• In previous query we give designation to employee based on salary value for that we used searched SQL Case statement.
• After writing the query, click on the execute SQL button to execute query.

OUTPUT:

 

SQL Case Statement | Aggregate functions and Group by clause

Aggregators are generally used in conjunction with grouping functions in order to summarize the data. Aggregate functions in SQL are carryout calculations on group of values and return a single value. Most common aggregate function used in SQL are COUNT, MIN, MAX, SUM, ABG and CHECKSUM.

The SQL Case statement are very useful to categorizing data, filtering data using where clause and also, we can use it to aggregating data based on results of logical condition. To apply aggregate function with SQL Case statement we must have to conjunct it with the GROUP BY clause.

 

Let’s take examples of SQL Case Statement having aggregate function with group by:

For example, calculate total salary paid in every city using with SQL Case statement

SELECT  city,  
CASE  
WHEN city = 'Surat' THEN SUM(Salary)  
WHEN city = 'Vadodara' THEN SUM(Salary)  
WHEN city = 'Navsari' THEN SUM(Salary)  
WHEN city = 'hazira' THEN SUM(Salary)  
END AS 'Total' 
FROM tblemp 
GROUP BY city;

• In this query we have used SUM aggregate function on salary to find city wise total salary so applied group by city clause also.
• After writing the query, click on the execute SQL button to execute query.

OUTPUT:

 

Let’s take example of SQL Case statement in inner join and Aggregate function including GROUP BY clause also: 

SELECT tbldept.Dept_name,  
CASE tbldept.Dept_name  
WHEN 'Account' THEN COUNT (*)  
WHEN 'Marketing' THEN COUNT (*)  
WHEN 'Sales' THEN COUNT (*)  
WHEN 'finance' THEN COUNT (*) END AS 'Total Employee' 
FROM tblemp INNER JOIN 
 tbldept ON tblemp.Dept_id = tbldept.Dept_id 
GROUP BY tbldept.Dept_name;

• Above query fetching records from two tables by connect with inner join, to count no. of employees working in each department using case statement, we apply COUNT function and group by on department name.
• After writing the query, click on the execute SQL button to execute query.

OUTPUT:

 

SQL Case Statement with UPDATE Statement

SQL Case statement can also be used with SQL Update command to modify the column value of rows. Using CASE in conjunction with a SQL UPDATE statement enables developers to conditionally modify large amounts of data using a single SQL statement.

Syntax: 

UPDATE table_name  
set column_name= CASE column_name 
WHEN condition_expression THEN result_value 
WHEN condition_expression THEN result_value 
... 
ELSE result_value 
END

Consider the following example of SQL Case statement within Update command:

Modify Employee salary raise it based on the department in which employee is working, Account department employee gets increment of 2%, Marketing department gets increment of 3%, Sales department with 1% and finance department gives increment of 4%, update table employee records using SQL Update statement with Case expression.

UPDATE  tblemp 
SET  Salary =  
CASE Dept_id  
WHEN 10001 THEN Salary * 1.02  
WHEN 10002 THEN Salary * 1.03  
WHEN 20001 THEN Salary * 1.01  
WHEN 30001 THEN Salary * 1.04  
ELSE NULL  
END

• When we execute above query it shows message like ‘10 row affected by last query’.
• To check the result of preceding update statement, we have to use select query to get data.

OUTPUT: 

SELECT Emp_id, Emp_name, street, city, Emp_contact, Salary, Dept_id 
FROM tblemp

 

Summary

The CASE statement comes in handy when you want to implement IF-THEN logic in SQL Server. In this article, we saw what CASE statement is along with its syntax. We also saw different examples of CASE statement along with its usage with the ORDER BY clause, the GROUP BY clause and also SQL Case statement with aggregate function also.

 

References

https://chartio.com/resources/tutorials/grouping-with-a-case-statement/
https://towardsdatascience.com/aggregating-and-grouping-data-in-sql-with-group-by-and-partition-by-fd5eaccece75

 

Further Reading

https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15