CASE Expressions in SELECT Statements

In database application programming, it is sometimes necessary to modify the representation of data. For instance, a person’s gender can be coded using the values 1, 2, and 3 (for female, male, and child, respectively). Such a programming technique can reduce the time for the implementation of a program. The CASE expression in the Transact-SQL language makes this type of encoding easy to implement.

The CASE expression has two different forms:

• Simple CASE expression
• Searched CASE expression

The syntax of the simple CASE expression is

CASE expression_1
	{WHEN expression_2 THEN result_1} ...
	[ELSE result_n]
ENDCode language: PHP (php)

A Transact-SQL statement with the simple CASE expression looks for the first expression in the list of all WHEN clauses that match expression_1 and evaluates the corresponding THEN clause. If there is no match, the ELSE clause is evaluated.

The syntax of the searched CASE expression is

CASE
	{WHEN condition_1 THEN result_1} ...
	[ELSE result_n]
ENDCode language: PHP (php)

A Transact-SQL statement with the searched CASE expression looks for the first expression that evaluates to TRUE. If none of the WHEN conditions evaluates to TRUE, the value of the ELSE expression is returned. Example 6.49 shows the use of the searched CASE expression.

USE sample;
SELECT project_name,
	CASE
		WHEN budget > 0 AND budget < 100000 THEN 1
		WHEN budget >= 100000 AND budget < 200000 THEN 2
		WHEN budget >= 200000 AND budget < 300000 THEN 3
		ELSE 4
	END budget_weight
FROM project;Code language: PHP (php)

The result is

In Example 6.49, budgets of all projects are weighted, and the calculated weights (together with the name of the corresponding project) are displayed. Example 6.50 shows another example with the CASE expression, where the WHEN clause contains inner queries as part of the expression.

USE sample;
SELECT project_name,
	CASE
		WHEN p1.budget < (SELECT AVG(p2.budget) FROM project p2)
			THEN 'below average'
		WHEN p1.budget = (SELECT AVG(p2.budget) FROM project p2)
			THEN 'on average'
		WHEN p1.budget > (SELECT AVG(p2.budget) FROM project p2)
			THEN 'above average'
		END budget_category
	FROM project p1;Code language: PHP (php)

The result is