MySQL IF() Function

The MySQL IF() function returns a value if a condition is TRUE, or another value if a condition is FALSE.

Syntax

IF(condition, value_if_true, value_if_false)

Parameters

Return Value

Returns value based on the condition.

Example 1:

The example below shows the usage of IF() function.

mysql> SELECT IF(100>200, "TRUE", "FALSE");
Result: "FALSE"

mysql> SELECT IF(100<200, "T", "F");
Result: "T"

mysql> SELECT IF(100<200, 500, 600);
Result: 500

mysql> SELECT IF(DATE(NULL), 500, 600);
Result: 600

mysql> SELECT IF('', 500, 600);
Result: 600

mysql> SELECT IF(ISNULL(NULL), "Replace NULL", "This is not NULL");
Result: Replace NULL

Example 2:

Consider a database table called Product with the following records:

If the OrderQuantity is optional and can contain NULL values. The statement mentioned below will give NULL value.

SELECT *, Price * (StockQuantity + OrderQuantity) AS Inventory  
FROM Product;

This will produce the result as shown below:

To avoid such situations, the IF() function in conjunction with ISNULL() function can be used to provide an alternative value to the column if it contains NULL value.

SELECT *, 
Price * (StockQuantity + IF(ISNULL(OrderQuantity), 0, OrderQuantity) AS Inventory
FROM Product;

This will produce the result as shown below: