CASE Statement in SQL
Table of Content:
The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
Syntax:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;
Prerequisite Codes
If you don't have table please create below table and practice it.
My database name is SQLExamples
USE SQLExamples
Create this below Employee table
DROP TABLE Employee CREATE TABLE Employee( EmpId INT, EmpName VARCHAR(25), EmpAddress VARCHAR(100), EmpDept VARCHAR(25) )
Insert data inside table
INSERT INTO Employee VALUES (1, 'Rambo', 'Kolkata', 'IT'), (2, 'Rohit', 'Kolkata', 'IT'), (3, 'Rohon', 'Kolkata', 'ITIS'), (4, 'Ronok', 'Kolkata', 'ITIS'), (5, 'Rubin', 'Kolkata', 'ITIS'), (6, 'Sorif', 'Kolkata', 'ADMIN'), (7, 'Soriful', 'Kolkata', 'ADMIN'), (8, 'Sofik', 'Kolkata', 'ADMIN')
SELECT * FROM Employee
Output:
The above code will produce the following result-
EmpId |
EmpName |
EmpAddress |
EmpDept |
1 |
Rambo |
Kolkata |
IT |
2 |
Rohit |
Kolkata |
IT |
3 |
Rohon |
Kolkata |
ITIS |
4 |
Ronok |
Kolkata |
ITIS |
5 |
Rubin |
Kolkata |
ITIS |
6 |
Sorif |
Kolkata |
ADMIN |
7 |
Soriful |
Kolkata |
ADMIN |
8 |
Sofik |
Kolkata |
ADMIN |
Example:
Code:
SELECT EmpName, EmpAddress, CASE WHEN EmpDept = 'ADMIN' THEN '20000' WHEN EmpDept = 'IT' THEN '250000' ELSE '30000' END AS Salary FROM Employee
Output:
The above code will produce the following result-
EmpName |
EmpAddress |
Salary |
Rambo |
Kolkata |
250000 |
Rohit |
Kolkata |
250000 |
Rohon |
Kolkata |
30000 |
Ronok |
Kolkata |
30000 |
Rubin |
Kolkata |
30000 |
Sorif |
Kolkata |
20000 |
Soriful |
Kolkata |
20000 |
Sofik |
Kolkata |
20000 |