MySQL in a Nutshell

Name

IF( )

Synopsis

IF(condition, result, result)

This function returns the second argument result if condition (the first argument) is met, and the third argument is not. Note that the value of condition is converted to an integer. It will return 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 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) to tally the value of all the client’s stocks first (not just Goodyear stock), as a condition of the ...