SQL Standard

SQL Case Statement

Conditional statements are very prevalent when working with databases. For example, we can use the CASE keyword in SQL to provide a set of evaluated conditions. We can then execute the code block if a given statement is true.

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

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:

use employees;
select emp_no, first_name, gender,
    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:


We can also use the CASE statement with an ORDER BY clause as shown in the following:

select emp_no, first_name, gender from employees
order by case gender when 'F' then gender end desc,
case when 'M' then gender

Resulting Table:

There you have it. A simple and intuitive method of ordering the rows in a result set using the case statements.


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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list