A case statement is similar to a nested if…else block which allows you to test a list of conditions and return only one value from the list of possible outcomes.
You will often find the use of CASE statements in stored procedures or SQL functions to run a set of conditions.
This tutorial teaches us how to work with CASE statements in Standard SQL.
SQL Case Statement
We can express the syntax of a CASE statement in SQL as shown in the following:
WHEN condition THEN result
WHEN condition THEN result
ELSE result
END
Let us discuss how to work with the CASE statement using practical examples. First, consider an example employee’s table as shown in the following:
In this cse, we have a table that contains the employee information. Let us see how we can use the SQL case statement to implement the conditional statements.
Take into consideration the following example statement:
select emp_no, first_name, gender,
case
when gender = 'M' then 'Male'
when gender = 'F' then 'Female'
else 'N/A'
end as gender_desc
from employees;
The previous example introduces a case statement to provide a description of the gender.
Resulting Output:
Example:
We can also use the CASE statement with an ORDER BY clause as shown in the following:
order by case gender when 'F' then gender end desc,
case when 'M' then gender
end;
Resulting Table:
There you have it. A simple and intuitive method of ordering the rows in a result set using the case statements.
Conclusion
In this tutorial, we learned how to use the case statement in Standard SQL to add the conditional checks. Conditionals can help you add flexibility in your SQL statements.
