IF( )

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

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

IF(condition, result, result)

Explanation

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

Examples

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