Overview

The case expression is probably the most useful addition in SQL-92.

This is a quick overview of how to use the expression, but you will find other tricks spread throughout the book.

The reason it is so important is that:

1. It works with any data type.

2. It allows the programmer to avoid procedural code by replacing IF-THEN-ELSE control flow with CASE expression inside the query.

3. It makes SQL statements equivalent to primitive recursive functions. You can look up what that means in a book on the theory of computation, but it is a nice mathematical property that guarantees certain kinds of problems can be solved.

11.1 The CASE Expression

The CASE expression allows the programmer to pick a value based on a logical expression in his code. ANSI stole the idea and the syntax from the now-defunct Ada programming language. Here is the syntax for a :

 ::=     ::=    CASE  ...      []    END  ::=    CASE      ...      []    END  ::= WHEN  THEN   ::= WHEN  THEN   ::= ELSE   ::=   ::=   ::=   NULL  ::= 

The searched CASE expression is probably the most used version of the expression. First, the expression is given a data type by finding the highest...