Branching: Making Choices in Queries - MySQL

Branching is obviously an area with a wealth of potential when it comes to replacing programming logic with SQL logic. So, in this section, we present some useful MySQL branching methods with this goal in mind.

No programming or scripting language would be very useful if it didn’t provide a way to execute different instructions depending on the outcome of a test condition, such as whether one value is greater than another. MySQL is similar in this regard. It has four flow-control operators that you can use to choose between values based on how one or more conditions are evaluated: IF(), IFNULL(), NULLIF(), and CASE .

True/False Branching with IF()

The IF() operator has the following syntax:

IF(test_expression, true_result, false_result)

The IF() operator takes three expressions as arguments. If test_expression evaluates as true (or 1), than it returns true_result; otherwise, false_result is returned. Here is a fairly simple example:

Let’s look at a slightly more complex scenario. Suppose as part of an e-commerce web site, we have a products table defined in part like this:

CREATE TABLE products (
  Product_id INT AUTO_INCREMENT PRIMARY KEY,
  product_price DECIMAL(6, 2),
  product_weight DECIMAL(5, 2)
);

As part of the checkout process, we need to determine shipping according to the following conditions: if the price of a product is greater than $100 or if the weight of the product is less than 500 grams (0.5 kg), then no charges for shipping are added; otherwise, we add a shipping charge of $4.50 per kilo for any weight in excess of 500 grams.

SELECT @p:=product_price AS p, @w:=product_weight AS w,
  @s:=IF(@p > 100.00 OR @w > 0.5, 0, @w * 4.50) AS s, FORMAT(@p + @s, 2) AS t
FROM products
WHERE product_id=productid;

When we test this with a bit of sample data, here’s the result:

We employed some user variables to get around the problems that arise when trying to use column aliases to define other columns in the same query.

Null-based Branching with IFNULL()

The IFNULL() operator has the following syntax:

IFNULL(expression1, expression2)

The way the IFNULL() function works may seem a bit counterintuitive: I f expression1 is not NULL, then it is returned; otherwise, expression2 is returned. You could express the same logic using IF() and NOT IS NULL:

IF(NOT IS NULL expression1, expression1, expression2 )

For example, consider an orders table for which a partial definition might be like this:

CREATE TABLE orders (
  orderid INT AUTO_INCREMENT PRIMARY KEY, 
  acctid INT NOT NULL,
  orderdate DATE NOT NULL
  shipdate DATE NULL
);

To produce a list of recent orders (say within the past 30 days) showing which ones have been and haven’t yet been shipped, we could use something like this:

SELECT orderid, acctid, IFNULL(shipdate, 'PENDING') AS Shipped
FROM orders
WHERE orderdate >= SUBDATE(CURRENT_DATE, INTERVAL 1 MONTH);

Expression Relation-based Branching with NULLIF()

The NULLIF() operator has the following syntax:

NULLIF(expression1, expression2)

This function tests the relation expression1 = expression2 and if the result is true (1), then the function returns NULL; otherwise, it returns expression2. This could also be written as the following:

IF(expression1 = expression2, NULL, expression2)

or, as you’ll see shortly, like this:

CASE WHEN expression1 = expression2 THEN NULL ELSE expression2 END

The NULLIF() function may not prove to be as efficient a method as some others in many circumstances. This is because if expression1 and expression2 are equal, then expression2 will actually be evaluated twice.

CASE-based Branching

The CASE structure can be used to test an expression against one or more other expressions in a series and to return one of any number of different values based on the outcome. It works in a manner similar to that of the if ... then ... else ... construct found in most programming and scripting languages.

There are two different versions of CASE .

Multiple Conditions with CASE

One version of CASE is as follows:

CASE WHEN condition THEN result
[ELSE else_result | WHEN condition2 THEN result2
[ELSE else_result2 | WHEN...] ]
END

In the simplest instance, the condition is tested, and if true, then the result following the THEN keyword is returned. If condition is not true, then NULL is returned. You can override the latter behavior by including an ELSE clause; in this case, if condition is false, then the else_result expression value is returned instead of NULL . For example, we could rewrite the example we used for IFNULL() like this:

SELECT orderid, acctid,
  CASE
    WHEN shipdate IS NULL
    THEN 'PENDING'
    ELSE shipdate
  END AS Shipped
FROM orders
WHERE orderdate >= SUBDATE(CURRENT_DATE, INTERVAL 1 MONTH);

This is a bit more verbose than the IFNULL() version, but it’s also easier to read, particularly with the line breaks and indentation we’ve added here.

We could make the example a bit more selective by introducing additional WHEN ... THEN ... clauses. Using something like the following, we can return "ORDER PENDING" if no shipping date has yet been set, "ORDER SHIPPED ON ... " plus the shipping date if the shipping date is in the past, "FUTURE" if the shipping date is in the future, and "SHIP TODAY" if the shipping date matches today’s date:

SELECT orderid, acctid, @s := shipdate,
  CASE
    WHEN @s IS NULL THEN 'ORDER PENDING' 
    WHEN @s < CURRENT_DATE THEN CONCAT('ORDER SHIPPED ON ', @s)
    WHEN @s > CURRENT_DATE THEN 'FUTURE'
    ELSE 'SHIP TODAY'
  END AS Shipped
FROM orders
WHERE orderdate >= SUBDATE(CURRENT_DATE, INTERVAL 1 MONTH);

You’ll notice that the result set includes an extra column for the shipping date; very shortly (in the “Our Demonstration Revisited” section), we’ll show you how to get rid of this if you don’t need it.

---

NOTE  A given CASE structure may contain any number of WHEN... THEN... clauses and a maximum of one ELSE clause.

---

Comparisons with CASE

The other form of CASE is as follows:

CASE expression WHEN value THEN result [ELSE else_result | WHEN value2 THEN result2
[ELSE else_result2 | WHEN...] ]
END

This compares an expression to a value, both of which can be any valid MySQL expressions, and returns result if the two are equal. Otherwise, either NULL or an else_result is returned, depending on whether or not an ELSE clause follows.

Here is an example:

SELECT
  CASE order_date
    WHEN last_pmt_date
    THEN 'YES'
    ELSE 'NO'
  END AS pmt_status
FROM orders;

The CASE block will evaluate to 'YES' if the order_date and last_pmt_date columns for a given record hold the same value, and 'NO' if they don’t.

---

NOTE  The syntax for CASE varies somewhat from what we’ve shown in this chapter when it’s used inside stored procedures in MySQL 5.0 and later. See Chapter 8 for more information.

---

What About Loops?

Given what we’ve just seen in the way of decision-making capabilities, you might expect that there would be some sort of mechanism for performing iterations as well. However, MySQL does not support loops in (normal) queries. In fact, up until MySQL 5.0, it didn’t support any sort of looping constructs at all. Beginning with that version, it’s possible to use loops inside stored procedures and user-defined functions. In Chapter 8, we’ll examine the constructs used for this purpose.