Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 of condition 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;

+------+----------------+-----------+--------+-------+
| ID   | Client         | Telephone | Shares | Size  |
+------+----------------+-----------+--------+-------+
| 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.