What is the syntax of using Case when in SQL?

Bob Dang

4y1.4kLearn iOS Programming

case when

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Anupam Maiti4y ago
Simple CASE expression:
SELECT
     [BusinessEntityID]
    ,[MaritalStatus]
    ,MaritalStatusDesc = CASE WHEN [MaritalStatus] = 'S' THEN 'Single' ELSE 'Married' END
FROM [AdventureWorks2017].[HumanResources].[Employee];
Nesting CASE expression:
SELECT
     [BusinessEntityID]
    ,[JobTitle]
    ,[HireDate]
    ,Seniority = CASE   WHEN DATEDIFF(YEAR,[HireDate],GETDATE()) > 10 THEN 'Longer than 10 years'
                        WHEN DATEDIFF(YEAR,[HireDate],GETDATE()) = 10 THEN 'Exactly 10 years'
                        WHEN DATEDIFF(YEAR,[HireDate],GETDATE()) < 10 THEN 'Shorter than 10 years'
                        ELSE 'N/A' END
FROM [HumanResources].[Employee];
NOTE : CASE can not only be used in SELECT statements, but at any other place where an expression can be written. This includes UPDATE, DELETE, SET, MERGE, a WHERE or HAVING clause and an ORDER BY clause.
Example use in Order By :
SELECT
     [BusinessEntityID]
    ,[JobTitle]
FROM [AdventureWorks2017].[HumanResources].[Employee]
ORDER BY CASE   WHEN [JobTitle] LIKE 'Chief%'       THEN 1
                WHEN [JobTitle] LIKE 'Vice%'        THEN 2
                WHEN [JobTitle] LIKE '%Senior%'     THEN 3
                WHEN [JobTitle] LIKE '%Manager%'    THEN 4
                ELSE 5 END
        , [JobTitle]
+2
Kapil Singh Kumawat4y ago
Hello,

Here is the sample example of using case in sql server

--Simple CASE expression: CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END

for more info please refer Microsoft Docs-
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15
+1
Sachin Singh4y ago
it is similar to if-else, it ends with end keyword but doesn't start with begin.
select case when age<5 then 'Todller'
when age>5 and age<15 then 'Kids'
when age>15 and age<18 then 'Teen'
when age>18 and age<60 then 'Adult'
else 'Senior Citizen'
end as AgeGroup
from tblPerson
+2
Muhammad Imran Ansari4y ago
The syntex for CASE-WHEN in SQL is as follow:
SELECT CASE
    WHEN condition THEN Result
    ELSE Result
END;
Here is an example of CASE statement in SQL. You have a table of employees and want to check the designation on the basis of salary, you can write your using CASE-WHEN as:
Select EmployeeName, Salary,
CASE
        WHEN Salary >=80000 AND Salary <=100000 THEN 'Director'
        WHEN Salary >=50000 AND Salary <80000 THEN 'Senior Consultant'
        Else 'Consultant'
        END AS Designation
from Employee
Else is used when no case will be matched.
+3
Md Sarfaraj4y ago
Hello Bob,
Check out the below links maybe it will help you.

https://www.w3schools.com/sql/sql_case.asp
https://www.freecodecamp.org/news/case-statement-in-sql-example-query/
+2
Ravinder Sharma4y ago
CASE statements are used to create different outputs (Usually in the SELECT statement).
It is SQL's way of handling if-then logic.
SELECT
          CASE WHEN [CONDITION 1] THEN 'OUTPUT 1'
          WHEN [CONDITION 2] THEN 'OUTPUT 2'
           ELSE 'OUTPUT 3'  END
FROM [TABLE_NAME]
In the above query, the output will be based on the conditions, if CONDITION 1 is true then the output will be OUTPUT 1, likewise, if CONDITION 2 is true then the output will be OUTPUT 2, if both conditions are false then the output will be OUTPUT 3.
+2
Uttam Kumar4y ago
Here is the syntax
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;
+2