Case Expression is used when we want output based different condition. You might think that for that purpose If-Else are there, but here's a difference. If-Else are flow control statement, while Case is not. If-Else spans across multiple SQL queries, when Case is used in single SQL query.
Let's take an example to make things clear. If we want to give message to students based on grade they received, we can write case statement as below.
It'll give output 'Well DONE'.
Now, let's dig in more. There are two type of Case statements.
In simple case one input is compared with different expressions, and based on match result is picked. Below is syntax.
If input is null then result will be null regardless of condition. If no expression is matched then expression in else is picked, and in case else is not specified then null is selected. Example displayed earlier is simple case expression.
Problem with simple case statement is that they can only perform equality check. If we want to use other predicates then we go for searched case. Below is syntax.Example:
In this example as you can see, we've used different types of predicates which are simply not possible in simple case expression.
1. We can have nested cases. For example below statement.
Sql Server only allows nesting up to 10 level.
2. If we are using aggregate statement they are evaluated first, and sometimes it can cause runtime error. So we are advised to use aggregate statements wisely. Look at below example taken from BOL.
3. We can use case statement in INSERT, UPDATE, DELETE statements too. And also in clauses like WHERE, IN, ORDER BY, HAVING. For more details visit BOL.