SQL - CASE Statement
Like the IF statement, The SQL CASE statement checks conditions and returns a value. It starts with checking the first condition and keep on checking conditions until it finds the condition true (like an IF-THEN-ELSE statement). Once the condition is found to be true, it stops checking the further conditions and returns the value. If no condition is found to be true, then it returns the value in the ELSE clause. If the ELSE clause is not defined, it returns NULL.
Syntax
The syntax for using CASE statement is given below:
CASE WHEN Condition1 THEN Value1 WHEN Condition2 THEN Value2 WHEN Condition3 THEN Value3 ... ELSE Value END
Example:
Consider a database table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
To fetch Name and Salary data of the employees present in the Employee table, the SQL code is:
SELECT Name, City, Salary, CASE WHEN Salary < 2800 THEN 'Salary is less than 2800.' WHEN Salary > 2800 THEN 'Salary is greater than 2800.' WHEN Salary = 2800 THEN 'Salary is 2800.' ELSE 'Salary Data missing.' END AS SalaryinCategory FROM Employee;
This will produce the result as shown below:
Name | City | Salary | SalaryinCategory |
---|---|---|---|
John | London | 3000 | Salary is greater than 2800. |
Marry | New York | 2750 | Salary is less than 2800. |
Jo | Paris | 2800 | Salary is 2800. |
Kim | Amsterdam | 3100 | Salary is greater than 2800. |
Ramesh | New Delhi | 3000 | Salary is greater than 2800. |
Huang | Beijing | 2800 | Salary is 2800. |