SQL CASE is used to provide conditional (if-else) logic in SQL statements.
SQL CASE Syntax
It has 2 types of SQL CASE syntax: simple case expression and searched case expression.
Simple CASE expression:
CASE Column1 WHEN Condition1 THEN Result1 WHEN Condition2 THEN Result2 ELSE Resutl3 END AS NewColumnName
For example:
SELECT CASE Gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END AS Gender
Searched CASE expression:
CASE WHEN Condition1 THEN Result1 WHEN Condition2 THEN Result2 ELSE Resutl3 END AS NewColumnName
For example:
SELECT CASE WHEN Gender='M' THEN 'Male' WHEN Gender='F' THEN 'Female' END AS Gender
More SQL CASE Examples
Table: Employees
EmployeeId | FirstName | LastName | Department | Salary |
---|---|---|---|---|
1 | Hudyaf | Yufda | F | 100 |
2 | Qiav | Vofa | F | 120 |
3 | Pofaq | Rqafda | F | 110 |
4 | Bldfa | Mifeaa | F | 80 |
5 | Opfae | Qzhtya | D | 200 |
6 | Xuggg | Reia | D | 100 |
7 | Kofyu | Hhg | O | 120 |
SELECT FirstName, LastName, Department = CASE Department WHEN 'F' THEN 'Financial' WHEN 'D' THEN 'Development' ELSE 'Other' END FROM Employees
The result will look like:
FirstName | LastName | Department |
---|---|---|
Hudyaf | Yufda | Financial |
Qiav | Vofa | Financial |
Pofaq | Rqafda | Financial |
Bldfa | Mifeaa | Financial |
Opfae | Qzhtya | Development |
Xuggg | Reia | Development |
Kofyu | Hhg | Other |
UPDATE Employees SET Salary = ( CASE Department WHEN 'D' THEN Salary + 10 WHEN 'F' THEN Salary + 11 ELSE Salary + 12 END )
The updated table Employees will look like:
EmployeeId | FirstName | LastName | Department | Salary |
---|---|---|---|---|
1 | Hudyaf | Yufda | F | 111 |
2 | Qiav | Vofa | F | 131 |
3 | Pofaq | Rqafda | F | 121 |
4 | Bldfa | Mifeaa | F | 91 |
5 | Opfae | Qzhtya | D | 210 |
6 | Xuggg | Reia | D | 110 |
7 | Kofyu | Hhg | O | 132 |