How to set up and query conditions with SQL CASE

SQL CASE lets you manipulate a database by querying certain conditions and displaying the results. The approach of the CASE condition is similar to the If-then-else statement.

What is SQL CASE?

The If-then-else statement is common in many database and programming languages. It executes an action if a certain condition is met. If the condition is no longer met, the program exits the loop or executes another action. The Structured Query Language is based on a similar principle and provides the SQL CASE statement. This statement evaluates a list of conditions and returns a specific value if a condition is met. If no conditions are met, it returns a value defined under ELSE. If there is no ELSE clause and the conditions are not met, it outputs NULL.

Syntax and function

The basic syntax of SQL CASE is as follows:

SELECT column1, column2, ..., 
CASE 
WHEN condition1 THEN result1 
END AS name 
FROM name_of_table;

Use the SQL command SELECT to specify which columns should be taken into account. Use CASE to check the condition. If this applies (i.e., it is true), the value of the stored result is output. END AS ends the loop. FROM specifies the table in which this loop is to be executed.

With the optional ELSE restriction, the syntax is:

SELECT column1, column2, ..., 
CASE 
WHEN condition1 THEN result1 
ELSE other_result 
END AS name 
FROM name_of_table;

Example for using the condition

To illustrate how SQL CASE works, we have created a simple table called ‘Customer list’. This table stores the customer number, customer name, location, order date, number of products ordered, and the amount paid in pounds.

Now we use the condition to work through the list. A specific text should be output for total amounts over 400 pounds, and another for amounts under 400 pounds. We use the ELSE clause for orders that are exactly 400 pounds. This demonstrates how multiple conditions can be checked in sequence. The appropriate syntax for our example is as follows:

SELECT Customer number, name, location, date, product sum
CASE
	WHEN Sum > 400 THEN 'The purchase value is over 400 Pounds'
	WHEN Sum < 400 THEN 'The purchase value is less than 400 Pounds'
	ELSE 'The purchase value is exactly 400 Pounds'
END AS Order details
FROM Customer list;

The output looks as follows:

Combination with other instructions

You can also query the conditions within other instructions. In the following, we arrange the entries by customer number. If this isn’t specified (i.e. NULL), the customer name should be taken into account instead:

SELECT Customer number, name, location, date, product, sum
FROM Customer list
ORDER BY
(CASE
		WHEN Customer number IS NULL THEN Name
		ELSE Customer number
END);

The output will be: