Learning SQL

Chapter 11. Conditional Logic

In certain situations, you may want your SQL statements to take one course of action 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.

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 retrieve either the fname/lname columns from the individual table or the name column from the business table depending on what type of customer is encountered. Using outer joins, you could return both strings and let the caller figure out which one to use, as in:

    mysql> SELECT c.cust_id, c.fed_id, c.cust_type_cd,
        ->   CONCAT(i.fname, ' ', i.lname) indiv_name,
        ->   b.name business_name
        -> FROM customer c LEFT OUTER JOIN individual i
        ->   ON c.cust_id = i.cust_id
        ->   LEFT OUTER JOIN business b
        ->   ON c.cust_id = b.cust_id; +---------+-------------+--------------+-----------------+------------------------+ | cust_id | fed_id | cust_type_cd | indiv_name | business_name | +---------+-------------+--------------+-----------------+------------------------+ | 1 | 111-11-1111 | I | James Hadley | NULL | | 2 | 222-22-2222 | I | Susan Tingley | NULL | | 3 | 333-33-3333 | I | Frank Tucker | NULL | | 4 | 444-44-4444 | I | John Hayward | NULL | | 5 | 555-55-5555 | I | Charles Frasier | NULL | | 6 | 666-66-6666 ...