CASE Clause

Many programming languages have conditional statements, stating if condition is true then do something, else do something else. This kind of structure allows execution of statements based on some condition. Although SQL is not a procedural programming language, it does allow conditional control over the data returned from a query. The WHERE clause uses comparisons to control row selection. The CASE statement allows comparisons in column output. Figure  shows a query using CASE to create a new output column containing adult or minor as appropriate, based on the age field.  

        test=> SELECT firstname,
        test->        age,
        test->        CASE
        test->            WHEN age >= 21 THEN 'adult'
        test->            ELSE 'minor'
        test->        END
        test-> FROM friend
        test-> ORDER BY firstname;
            firstname    | age | case  
        -----------------+-----+-------
         Dean            |  24 | adult
         Dick            |  19 | minor
         Ned             |  27 | adult
         Sandy           |  25 | adult
         Sandy           |  33 | adult
         Victor          |  22 | adult
        (6 rows)  

Of course, the values adult and minor do not appear in the table friend. The CASE clause allows the creation of those conditional strings.

Figure  shows a more complex example.  

        test=> SELECT  firstname, 
        test->         state, 
        test->         CASE
        test->                 WHEN state = 'PA' THEN 'close'
        test->                 WHEN state = 'NJ' OR state = 'MD' THEN 'far'
        test->                 ELSE 'very far'
        test->         END AS distance
        test-> FROM friend
        test-> ORDER BY firstname;
            firstname    | state | distance 
        -----------------+-------+----------
         Dean            | MA    | very far
         Dick            | NJ    | far
         Ned             | MD    | far
         Sandy           | NJ    | far
         Sandy           | MA    | very far
         Victor          | PA    | close
        (6 rows)  

It shows a query with multiple WHEN clauses. The AS clause is used to label the column with the word distance.  Although only SELECT examples are shown, CASE can be used in UPDATE and other complicated situations. CASE allows the creation of conditional values, which can be used for output or for further processing in the same query.