article

SQL CASE statement

Email
Submitted on: 5/1/2020 9:55:25 AM
By: Santosh Kumar Sahu  
Level: Beginner
User Rating: By 1 Users
Compatibility: Oracle, Other
Views: 746
 
     Discuss 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:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

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:

NameCitySalarySalaryinCategory
JohnLondon3000Salary is greater than 2800.
MarryNew York2750Salary is less than 2800.
JoParis2800Salary is 2800.
KimAmsterdam3100Salary is greater than 2800.
RameshNew Delhi3000Salary is greater than 2800.
HuangBeijing2800Salary is 2800.


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Beginner category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.