CASE in MySQl

The previous lesson of MySql Lessons Series was about MySql If Statement and in this lesson we will learn about MySql Case Statement.

Just like MySql IF Statement, the need for use of CASE in MySql arises when the WHERE condition is not enough to meet all the required conditions of a query. To meet those special conditions we will need to use either an IF Statement or a CASE in our query along with WHERE clause. The basic structure of a CASE in MySql query is given below:


SELECT ColumnNames 
CASE WHEN (Condition) THEN (OPERATION)
  WHEN (Condition) THEN (OPERATION)
  WHEN (Condition) THEN (OPERATION)
END 
FROM TableName WHERE 1

The structure of the above CASE Statement is pretty much self explanatory. MySql CASE Statement has two parts i.e

1. Conditonal Part
2. Operational Part

Each condition of the CASE Statement is checked and if true the operation against the condition is performed. For more explanation of the MySql Case Statement, consider the user table given in the image below:

The user table shown in the above image is the same table we used in the previous lesson for MySql IF Statement. Let us use the same query requirements as of MySql If Statement in the previous lesson. Select the name of user, name of product bought and price of the product bought with currency sign appended to the price of product. We will append a dollar sign to price if the user is from America and a pound sign if the user is from England. MySql Case Query is given below:


SELECT Name, Product_Name, 
CASE WHEN Country = 'England' THEN CONCAT('£',Price) 
     WHEN Country = 'America' THEN CONCAT('$',Price) 
END
Price FROM user

The output of the above query is given below which is similar to the output of MySql If Query in the previous lesson.