Home arrow MySQL arrow Page 4 - Optimizing Queries with Operators for Date, Time and Other Functions

Branching: Making Choices in Queries - MySQL

This article will give you a good grounding in operators, branching and functions in MySQL, so you can make the database, instead of your own code, do the bulk of the work. It is the third of three parts, and excerpted from chapter four of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress; ISBN: 1590593324).

  1. Optimizing Queries with Operators for Date, Time and Other Functions
  2. Date and Time Conversion Functions
  3. Other MySQL Functions
  4. Branching: Making Choices in Queries
  5. Our Demonstration Revisited
By: Apress Publishing
Rating: starstarstarstarstar / 6
April 13, 2006

print this article



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_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:

  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

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...] ]

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,
    WHEN shipdate IS NULL
    ELSE shipdate
END AS Shipped
FROM orders

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,
END AS Shipped
FROM orders

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.

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...] ]

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:

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.

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.

>>> More MySQL Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: