MySQL in a Nutshell, 2nd Edition

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