SQL Server CASE expression evaluates a list of conditions and returns one of the multiple specified results. The CASE expression has two formats: simple CASE expression and searched CASE expression.
The simple CASE expression compares the input expression (input) to an expression (ei) in each WHEN clause for equality. If the input expression equals an expression (ei) in the WHEN clause, the result (ri) in the corresponding THEN clause is returned.
If the input expression does not equal to any expression and the ELSE clause is available, the CASE expression will return the result in the ELSE clause (re).
In case the ELSE clause is omitted and the input expression does not equal to any expression in the WHEN clause, the CASE expression will return NULL.
select *,case
when salary<35000 then 'level 1'
when salary>=35000 and salary<50000 then 'level 2'
when salary>=5000 and salary <750000 then 'level 3'
when salary>=75000 and salary <100000 then 'level 4'
else 'level 5' end from employee
COALESCE:The SQL Server COALESCE expression accepts a number of arguments, evaluates them in sequence, and returns the first non-null argument.
select
coalesce(manager,state,dept_head),manager,state,dept_head
from employee
Different way to replace null value:
1. is null (column/variable, 'replacement value') return replacement value if column or variable is null
select manager
,isnull(manager,'trainee') is_null
from employee
Null if (column/variable, value1) return null if column or variable have value value1
select
dept,nullif(dept,'resale') null_if
from employee