SQL CASE

by

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

Leave a Comment