Learning SQL, 3rd Edition

Chapter 11. Conditional Logic

In certain situations, you may want your SQL logic to branch in one direction or another depending on the values of certain columns or expressions. This chapter focuses on how to write statements that can behave differently depending on the data encountered during statement execution. The mechanism used for conditional logic in SQL statements is the case expression, which can be utilized in select, insert, update, and delete statements.

What Is Conditional Logic?

Conditional logic is simply the ability to take one of several paths during program execution. For example, when querying customer information, you might want to include the customer.active column, which stores 1 to indicate active and 0 to indicate inactive. If the query results are being used to generate a report, you may want to translate the value to improve readability. While every database includes built-in functions for these types of situations, there are no standards, so you would need to remember which functions are used by which database. Fortunately, every database’s SQL implementation includes the case expression, which is useful in many situations, including simple translations:

mysql> SELECT first_name, last_name,
    ->   CASE
    ->     WHEN active = 1 THEN 'ACTIVE'
    ->     ELSE 'INACTIVE'
    ->   END activity_type     -> FROM customer; +-------------+--------------+---------------+ | first_name  | last_name    | activity_type | +-------------+--------------+---------------+ ...