MySQL: Case Statement

The CASE function in MySQL provides certain conditions based on which, if it is true, then it returns the result. Think of it more like IF, ELSE, and THEN statement where a particular part is satisfied when the provided condition is true.

If no conditions satisfy, it automatically goes to the ELSE and returns whatever is provided inside. If no ELSE is provided and no conditions are satisfied, the table value will be null.

Syntax

CASE
WHEN condition THEN result
ELSE result
END

• Start with CASE within it provide N number of conditions based on WHEN and THEN.
• Use END at the final stage of CASE to end the statement.

Let us look at a more realistic case scenario where CASE can use this.

Let us calculate the tax amount of individuals with a specific range of salaries.

Here are the tax rates:

• Based on these tax rates, we will calculate the tax amount for each individual with a specific range of salaries that falls between these tax rates.

Here is our salary table:

• We have the names and salaries of each individual in LPA.

Ok, let us dive into the query.

select Name, Salary as "Salary(In LPA)", (case 
when (Salary >= 500001 and Salary <= 1000000) THEN round(((Salary - 500000) * (20/100) + (12500)))
when (Salary <= 250000) then 0
when (Salary >= 250001 and Salary <= 500000) then round(((Salary - 250000) * (5/100)))
when (Salary >= 1000001) then round(((Salary - 1000000) * (30/100) + (112500)))

 ELSE 
      0 
 END) as TaxAmount from salary;

• So CASE statement is used well in this example.
• According to the tax rates table, salary range between 500001 and 1000000 shall be subtracted with 500000 from LPA and get 20% off of it Adding 12500 with it.
• We have rounded off too to get the nearest integer.
• So we had provided the salary range inside the when and used then to return a result for all the others like the same.
• This is an effective way to use the CASE statement as it will be more readable and understandable.