SQL Case

The CASE statement is used to execute SQL code when certain conditions are met, much like IF/ELSE statements in other languages. CASE statements can be divided into two methods: general CASE statements or CASE expressions, and both can lead to the same results.

---

Basic Syntax

General CASE statement

SELECT a,
CASE WHEN a=1 THEN 'one'
	 WHEN a=2 THEN 'two'
	 ELSE 'other'
END as b
FROM test;

Explanation

This query selects column a from the test table. As the query runs, if the row value in a is 1, then the query will return the string 'one'. Note that this query will return two columns, a and b.

This method also allows greater flexibility since the expression itself can be anything between WHEN and THEN.

CASE expression

The CASE expression syntax first evaluates an expression then compares the result with each value in the WHEN clauses sequentially.

SELECT a,
CASE a
	WHEN 1 THEN 'one'
	WHEN 2 THEN 'two'
	ELSE 'other'
END
FROM test

The main difference here is the use of an expression: the query here checks if the result of the expression a is equal to 1, else to 2, etc. This is often used to check for quality (when a == 1, then ‘one’ , etc); for more complex queries, it is often better to use the general CASE.

---

Example 1

SELECT customer_id,
CASE
	WHEN (customer_id <= 100) THEN 'Premium'
	WHEN (customer_id BETWEEN 100 AND 200) THEN 'Plus'
	ELSE 'Normal'
END as customer_class
FROM customer

Explanation: Returns a new column next to customer_id, where:

• When customer_id is less than or equal to 100, value is “Premium”
• ELSE when the id is between 100 and 200 value is “Plus”
• ELSE “Normal”

In Python a similar logic exists with if/elif/else:

def fancy_customers(customer_id: List):
	for id in customer_id:
		if id <= 100:
			print("Premium")
		elif id < 200 and id > 100:
			print("Plus")
		else:
			print("Normal")

---

Example 2

SELECT customer_id,
CASE customer_id % 2
	WHEN 0 THEN 'EVEN'
	ELSE 'ODD'
END AS even_odd
FROM customer

---

Example 3

Calling sum on the results of a results column.

SELECT
SUM(CASE rental_rate
	WHEN 0.99 THEN 1
	ELSE 0
END) AS bargains,
SUM(CASE rental_rate
	WHEN 2.99 THEN 1
	ELSE 0
END) AS regular,
FROM film

bargains	regular
341	323

Related Notes

• SQL Insert – How to add new rows to a table.
• SQL Delete – Removing rows from a table.
• SQL Transactions and Errors – Grouping statements to maintain atomicity.
• SQL select distinct – Retrieving data from tables.