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 (
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,
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:
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 (
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
Expression Relation-based Branching with NULLIF()
The NULLIF() operator has the following syntax:
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.
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
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,
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,
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.
The other form of CASE is as follows:
CASE expression WHEN value THEN result [ELSE else_result | WHEN value2 THEN result2
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:
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.
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.
blog comments powered by Disqus