case

Description

Supports conditional SQL expressions; can be used anywhere a value expression can be used.

Syntax

case 
	when search_condition then expression 
	[when search_condition then expression]...
	[else expression]
end
case and values syntax:
case expression
	when expression then expression 
	[when expression then expression]...
	[else expression]
end

Parameters

case

begins the case expression.

when

precedes the search condition or the expression to be compared.

search_condition

is used to set conditions for the results that are selected. Search conditions for case expressions are similar to the search conditions in a where clause. Search conditions are detailed in the Transact-SQL User’s Guide.

then

precedes the expression that specifies a result value of case.

expression

is a column name, a constant, a function, a subquery, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators. For more information about expressions, see “Expressions”.

Examples

Example 1

Selects all the authors from the authors table and, for certain authors, specifies the city in which they live:

select au_lname, postalcode, 
    case
            when postalcode = "94705" 
                then "Berkeley Author"
            when postalcode = "94609" 
                then "Oakland Author"
            when postalcode = "94612" 
                then "Oakland Author"
            when postalcode = "97330" 
                then "Corvallis Author"
        end 
from authors

Example 2

Returns the first occurrence of a non-NULL value in either the lowqty or highqty column of the discounts table:

select stor_id, discount,
        coalesce (lowqty, highqty)
from discounts

Yuo can also use the following format to produce the same result, since coalesce is an abbreviated form of a case expression:

select stor_id, discount, 
        case
            when lowqty is not NULL then lowqty
            else highqty
        end
from discounts

Example 3

Selects the titles and type from the titles table. If the book type is UNDECIDED, nullif returns a NULL value:

select title,
        nullif(type, "UNDECIDED")
from titles

You can also use the following format to produce the same result, since nullif is an abbreviated form of a case expression:

select title, 
        case
            when type = "UNDECIDED" then NULL
            else type
        end
from titles

Example 4

Produces an error message, because at least one expression must be something other than the null keyword:

select price, coalesce (NULL, NULL, NULL)
from titles
All result expressions in a CASE expression must not be NULL.

Example 5

Produces an error message, because at least two expressions must follow coalesce:

select stor_id, discount, coalesce (highqty) from discounts
A single coalesce element is illegal in a COALESCE expression.

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

case permission defaults to all users. No permission is required to use it.

See also

Commands coalesce, nullif, if...else, select, where clause