MySQL in a Nutshell (2008)

Part II. SQL Statements and Functions

Chapter 14. Flow Control Functions

MySQL has a few built-in flow control functions that you can use in SQL statements for more precise and directed results. This chapter provides the syntax of function and gives examples of their use. For the examples in this chapter, a fictitious database for a stock broker is used.

The following functions are covered in this chapter:

CASE, IF(), IFNULL(), ISNULL(), NULLIF().

Functions in Alphabetical Order

The following are the MySQL flow control functions listed alphabetically.

Name

CASE

Synopsis

CASE value

WHEN [value] THEN result

. . .

[ELSE result]

END

CASE

WHEN [condition] THEN result

. . .

[ELSE result]

END

This function produces results that vary based on which condition is true. It is similar to the IF() function, except that multiple conditions and results may be strung together. In the first syntax shown, the value given after CASE is compared to each WHEN value. If a match is found, theresult given for the THEN is returned. The second syntax tests each condition independently, and they are not based on a single value. For both syntaxes, if no match is found and an ELSE clause is included, the result given for the ELSE clause is returned. If there is no match and no ELSEclause is given, NULL is returned.

If the chosen result is a string, it is returned as a string data type. If result is numeric, the result may be returned as a decimal, real, or integer value.

Here’s an example of the first syntax shown:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Client,

telephone_home AS Telephone,

CASE type

WHEN 'RET' THEN 'Retirement Account'

WHEN 'REG' THEN 'Regular Account'

WHEN 'CUS' THEN 'Minor Account'

END AS 'Account Type'

FROM clients;

This SQL statement retrieves a list of clients and their telephone numbers, along with a description of their account types. However, the account type is a three-letter abbreviation, so CASE() is used to substitute each type with a more descriptive name.

This example uses the syntax in which a common parameter is evaluated to determine the possible result. The following SQL statement utilizes the other syntax for the function:

SELECT CONCAT(name_last, SPACE(1), name_first) AS Prospect,

CASE

WHEN YEAR(NOW( )) - YEAR(birth_date) ≤ 17 THEN 'Minor'

WHEN YEAR(NOW( )) - YEAR(birth_date) > 17 < 26 THEN 'Too Young'

WHEN YEAR(NOW( )) - YEAR(birth_date) > 60 THEN 'Elderly'

ELSE home_telephone;

END

AS Telephone

FROM prospects;

In this example, the SQL statement analyzes a table containing a list of people that the broker might call to buy an investment. The table contains the birth dates and the telephone numbers of each prospect. The SQL statement provides the telephone numbers only for prospects aged 26 to 60 because anyone younger or older would not be suitable for this particular investment. However, a message for each prospect that is disqualified is given based on the clauses of the CASE() statement.

When using a CASE statement within a stored procedure, it cannot be given a NULL value for the ELSE clause. Also, a CASE statement ends with END CASE.

Name

IF()

Synopsis

IF(condition, result, result)

This function returns the result given in the second argument if the condition given in the first argument is met (i.e., the condition does not equal 0 or NULL). If the condition does equal 0 or NULL, the function returns the result given in the third argument. Note that the value ofcondition is converted to an integer. Therefore, use a comparison operator when trying to match a string or a floating-point value. The function returns a numeric or a string value depending on its use. As of version 4.0.3 of MySQL, if the second or the third argument is NULL, the type (i.e., string, float, or integer) of the other non-NULL argument will be returned:

SELECT clients.client_id AS ID,

CONCAT(name_first, SPACE(1), name_last) AS Client,

telephone_home AS Telephone, SUM(qty) AS Shares,

IF(

(SELECT SUM(qty * price)

FROM investments, stock_prices

WHERE stock_symbol = symbol

AND client_id = ID )

> 100000, 'Large', 'Small') AS 'Size'

FROM clients, investments

WHERE stock_symbol = 'GT'

AND clients.client_id = investments.client_id

GROUP BY clients.client_id LIMIT 2;

+------+----------------+-----------+--------+-------+

+------+----------------+-----------+--------+-------+

| 8532 | Jerry Neumeyer | 834-8668 | 200 | Large |

| 4638 | Rusty Osborne | 833-8393 | 200 | Small |

+------+----------------+-----------+--------+-------+

This SQL statement is designed to retrieve the names and telephone numbers of clients who own Goodyear stock (the stock symbol is GT) because the broker wants to call them to recommend that they sell it. The example utilizes a subquery (available as of version 4.1 of MySQL) to tally the value of all the clients’ stocks first (not just Goodyear stock), as a condition of the IF() function. It does this by joining the investments table (which contains a row for each stock purchase and sale) and the stock_prices table (which contains current prices for all stocks). If the sum of the value of all stocks owned by the client (the results of the subquery) is more than $100,000, a label of Large is assigned to the Size column. Otherwise, the client is labeled Small. The broker wants to call her large clients first. Notice in the results shown that both clients own the same number of shares of Goodyear, but one has a large portfolio.

Note that the IF statement used in stored procedures has a different syntax from the IF() function described here. See Chapter 17 for more information on the IF statement.

Name

IFNULL()

Synopsis

IFNULL(condition, result)

This function returns the results of the condition given in the first argument of the function if its results are not NULL. If the condition results are NULL, the results of the expression or string given in the second argument are returned. It will return a numeric or a string value depending on the context:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Client,

telephone_home AS Telephone,

IFNULL(goals, 'No Goals Given') AS Goals

FROM clients LIMIT 2;

+----------------+-----------+----------------+

| Client | Telephone | Goals |

+----------------+-----------+----------------+

| Janice Sogard | 835-1821 | No Goals Given |

| Kenneth Bilich | 488-3325 | Long Term |

+----------------+-----------+----------------+

This SQL statement provides a list of clients and their telephone numbers, along with their investment goals. If the client never told the broker of an investment goal (i.e., the goals column is NULL), the text “No Goals Given” is displayed.

Name

ISNULL()

Synopsis

ISNULL(column)

Use this function to determine whether the value of the argument given in parentheses is NULL. It returns 1 if the value is NULL and 0 if it is not NULL. Here is an example:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Client,

telephone_work AS 'Work Telephone'

FROM clients

WHERE ISNULL(telephone_home);

In this example, after realizing that we don’t have home telephone numbers for several of our clients, we use the ISNULL() function in the WHERE clause of a SELECT statement to list client names and their work telephone numbers so that we can call them to get their home telephone numbers. Only rows in which the home_telephone column is NULL will result in a value of 1 and will therefore be shown in the results.

Name

NULLIF()

Synopsis

NULLIF(condition1, condition2)

This function returns NULL if the two arguments given are equal. Otherwise, it returns the value or results of the first argument. Here is an example:

SELECT clients.client_id AS ID,

CONCAT(name_first, SPACE(1), name_last) AS Client,

telephone_home AS Telephone,

NULLIF(

(SELECT SUM(qty * price)

FROM investments, stock_prices

WHERE stock_symbol = symbol

AND client_id = ID ), 0)

AS Value

FROM clients, investments

WHERE clients.client_id = investments.client_id

GROUP BY clients.client_id;

In this example, NULL is returned for the Value column if the value of the client’s stocks is 0 (i.e., the client had stocks but sold them all). If there is a value to the stocks, however, the sum of their values is displayed.