How to Use the SQL CASE Conditional Expression
Every complete computer language has some kind of conditional statement or command, including SQL. In fact, most have several kinds. Probably the most common conditional statement or command is the IF…THEN…ELSE…ENDIF structure. If the condition following the IF keyword evaluates to True, the block of commands following the THEN keyword executes.
If the condition doesn’t evaluate to True, the block of commands after the ELSE keyword executes. The ENDIF keyword signals the end of the structure. This structure is great for any decision that goes one of two ways. The structure doesn’t work as well for decisions that can have more than two possible outcomes.
Most complete languages have a CASE statement that handles situations in which you may want to perform more than two tasks based on more than two possible values of a condition.
SQL has a CASE statement and a CASE expression. A CASE expression is only part of a statement — not a statement in its own right. In SQL, you can place a CASE expression almost anywhere a value is legal. At run time, a CASE expression evaluates to a value. SQL’s CASE statement doesn’t evaluate to a value; rather, it executes a block of statements.
The CASE expression searches a table, one row at a time, taking on the value of a specified result whenever one of a list of conditions is True. If the first condition is not satisfied for a row, the second condition is tested — and if it is True, the result specified for it is given to the expression, and so on until all conditions are processed.
If no match is found, the expression takes on a NULL value. Processing then moves to the next row.
You can use the CASE expression in the following two ways:
Use the expression with search conditions. CASE searches for rows in a table where specified conditions are True. If CASE finds a search condition to be True for a table row, the statement containing the CASE expression makes a specified change to that row.
Use the expression to compare a table field to a specified value. The outcome of the statement containing the CASE expression depends on which of several specified values in the table field is equal to each table row.