SQL CASE
SQL CASE expression provides an option for decision-making within SQL expressions. Its basic structure is similar to the IF…THEN…ELSE statement found in many programming languages. When the DBMS encounters a CASE expression, it evaluates the first search condition, and if it is TRUE, then the value of the SQL CASE expression is the value of the first result expression. If the result of the first search condition is not TRUE, the DBMS proceeds to the second search condition and checks whether it is TRUE. If so, the value of the SQL CASE expression is the value of the second result expression, and so on.
Example:
The following query will display names and grades for employees according to their salary:
SELECT full_name,
CASE WHEN salary > 60000 THEN 'A'
WHEN salary > 30000 and salary<=6000 THEN 'B'
ELSE 'C'
END AS grade
FROM employee;
Output:
Example 2:
The following query will display value ‘Not Entered’ for NULL values in date of birth column for employees:
SELECT name,
CASE birth_date
WHEN NULL THEN 'Not Entered'
ELSE ‘Entered’
END AS date_of_birth
FROM employee;
Output:
Note the difference between first query and second query; in second query there is no relational operator used and column name is entered only once beside SQL CASE keyword. This is an example of simple case.