SELECT: Set Operations

Structured Query Language SELECT: Case Expression

SELECT: Subquery

Sometimes it's necessary to translate stored values (or values to be stored) from one representation to another. Suppose there is a columnstatus with legal values from 0 to 9, but the end-users should receive strings that explain the meaning of the numeric values in short, eg.: 'ordered', 'delivered', 'back delivery', 'out of stock', ... . The recommended way to do this is a separate table where the numeric values maps to the explanatory strings. Notwithstanding this, application developers may favor a solution within an application server.

The CASE expression, which is shown on this page, is a technique for solving the described situation as part of a SELECT, INSERT or UPDATE command as well as solving additional problems. As part of the language, it's a powerful term that can be applied at plenty of places within SQL commands. On this page, we focus on its use together with the SELECT command. The strategy and syntax for CASE withinINSERT andUPDATE are equivalent and are presented over there. In comparison with the recommended technique of a separate table for the translation, the CASE expression is much more flexible (which is not an advantage in all cases).

-- Technical term: "simple case"-- Select id, contact_type in a translated version and contact_valueSELECTid,CASEcontact_typeWHEN'fixed line'THEN'Phone'WHEN'mobile'THEN'Phone'ELSE'Not a telephone number'ENDAS'contact_type',contact_valueFROMcontact;

The CASE expression is introduced with its keyword CASE and runs up to the END keyword. In this first example, it specifies a column name and a series of WHEN/THEN clauses with an optional ELSE clause. The WHEN/THEN clauses are compared and evaluated against the values of the named column, one after the other. If none of them hits, the ELSE clause applies. If there is no ELSE clause and none of the WHEN/THEN clauses hit, the NULL special marker will be applied.

The comparison between the values of the column and the fixed values within the WHEN/THEN clause is done solely by "=" (equals). This is a good starting point, but real applications need more than that. Therefore there is a variant of the CASE.

-- Technical term: "searched case"-- Select persons name, weight and a denomination of the weightSELECTfirstname,lastname,weight,CASEWHEN(weightISNULLORweight=0)THEN'weight is unknown'WHENweight<40THEN'lightweight'WHENweightBETWEEN40AND85THEN'medium'ELSE'heavyweight'ENDAS'weight'FROMperson;

The crucial point is the direct succession of the two keywords CASE and WHEN. There isno column name between them. In this variant, there must be a complete expression, which evaluates to one of the 3-value-logic termstrue,false orunknown, between each WHEN and THEN. Now it is possible to use all the comparisons and boolean operators as they are known by theWHERE clause. It is even possible to compare different columns or function calls with each other.

There are the two variantssimple case andsearched case.

-- "simple case" performs successive comparisons using the equal operator: <column_name> = <expression_x>CASE<column_name>WHEN<expression_1>THEN<result_1>WHEN<expression_2>THEN<result_2>...ELSE<default_result>-- optionalEND-- "searched case" is recognised by 'nothing' between CASE and first WHENCASEWHEN<condition_1>THEN<result_1>WHEN<condition_2>THEN<result_2>...ELSE<default_result>-- optionalEND

Thesimple case is limited to one column and the use of the equal operator, whereas thesearched case may evaluate arbitrary columns of the (intermediate) result with arbitrary operators, functions or predicates.

The use of CASE expressions is not limited to projections (the column list between SELECT and FROM). As the clause evaluates to a value, it can be applied as a substitution for values at several places within SQL commands. In the following, we offer some examples.

ORDER BY clause

Sort contact values in the order: all fixed lines, all mobile phones, all emails, all icq's. Within each group sort over the contact values.

SELECT*FROMcontactORDERBY-- a "simple case" construct as substitution for a column nameCASEcontact_typeWHEN'fixed line'THEN0WHEN'mobile'THEN1WHEN'email'THEN2WHEN'icq'THEN3ELSE4END,contact_value;

In the next example, persons are ordered by weight classes, within the classes by their name.

-- order by weight classesSELECTfirstname,lastname,weight,CASEWHEN(weightISNULLORweight=0)THEN'weight is unknown'WHENweight<40THEN'lightweight'WHENweightBETWEEN40AND85THEN'medium'ELSE'heavyweight'ENDFROMpersonORDERBY-- a "searched case" construct with IS NULL, BETWEEN and 'less than'.CASEWHEN(weightISNULLORweight=0)THEN0WHENweight<40THEN1WHENweightBETWEEN40AND85THEN2ELSE3END,lastname,firstname;

WHERE clause

Within the WHERE clauses, there may occur fixed values or column names. CASE expressions can be used as a substitution for them. In the example, persons receive a discount on their weight depending on their place of birth (consider it as a theoretical example). Thus Mr. Goldstein, with its 95 kg, counts only with 76 kg and is not part of the result set.

SELECT*FROMpersonWHERECASE-- Modify weight depending on place of birth.WHENplace_of_birth='Dallas'THENweight*0.8WHENplace_of_birth='Richland'THENweight*0.9ELSEweightEND>80ORweight<20;-- any other condition

SELECTfirstname,lastname,CASEfirstnameWHEN'Larry'THEN'male'WHEN'Tom'THEN'male'WHEN'James'THEN'male'WHEN'John'THEN'male'WHEN'Elias'THEN'male'WHEN'Yorgos'THEN'male'WHEN'Victor'THEN'male'WHEN'Lisa'THEN'female'ELSE'unknown gender'ENDFROMperson;

SELECTfirstname,lastname,CASEWHENfirstnamein('Larry','Tom','James','John','Elias','Yorgos','Victor')THEN'male'WHENfirstname='Lisa'THEN'female'ELSE'unknown gender'ENDFROMperson;

-- Hint: Some implementations use a different function name: length() or len().SELECTfirstname,lastname,CASEWHENCHARACTER_LENGTH(firstname)<4THEN'short name'WHENCHARACTER_LENGTH(firstname)<6THEN'medium length'ELSE'long name'ENDFROMperson;

-- Hint: Some implementations use a different function name: length() or len().SELECTSUM(CASEWHENCHARACTER_LENGTH(firstname)<4THEN1ELSE0END)asshort_names,SUM(CASEWHENCHARACTER_LENGTH(firstname)between4and5THEN1ELSE0END)asmedium,SUM(CASEWHENCHARACTER_LENGTH(firstname)>5THEN1ELSE0END)aslong_namesFROMperson;

SELECT: Set Operations

Structured Query Language SELECT: Case Expression

SELECT: Subquery

• Book:Structured Query Language