SQL CASE

In this tutorial, we will learn how to use  CASE statement using SQL.

SQL CASE Statement

• SQL CASE Statement has the functionality like IF-THEN-ELSE Statement.
  If the condition is true, will return the result and if no condition is true, it returns the value in the ELSE clause.
• If there is no ELSE part and no conditions are true, then it returns NULL.

CASE
    WHEN CONDITION1   THEN result1
    WHEN CONDITION2   THEN result2
    WHEN CONDITION(N) THEN resultN
    ELSE result
END;

Example:

Let us consider this table "Employee" for records.

Table Name: Employee

Example of CASE Statement

For adding "Mr." and "Mrs." as a prefix in front of employee name according to their gender using CASE Statement, and this the most asking interview question of SQL, a query will be:

SELECT Id,
(CASE Gender
WHEN 'male' THEN 'Mr.'+EmpName
WHEN 'female' THEN 'Mrs.'+EmpName
ELSE EmpName
END) AS EmpName,City,Country,Gender,Salary
FROM Employee;

OR You can write this statement like this also,

SELECT Id,
(CASE 
WHEN Gender='male' THEN 'Mr.'+EmpName
WHEN Gender='female' THEN 'Mrs.'+EmpName
ELSE EmpName
END) AS EmpName,City,Country,Gender,Salary
FROM Employee;