The SQL CASE Statement: A Comprehensive Guide

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