MariaDB Tutorial MariaDB Advanced MariaDB Database Account Management MariaDB References

MariaDB IF() Function



The MariaDB 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

condition Required. Specify the value to test.
value_if_true Required. Specify the value to return if condition is TRUE.
value_if_false Required. Specify the value to return if condition is FALSE.

Return Value

Returns value based on the condition.

Example 1:

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

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

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

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

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

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

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:

ProductNamePriceStockQuantityOrderQuantity
Apple1.0010020
Banana1.2512030
Orange2.15105NULL
Watermelon3.507515

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

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

This will produce the result as shown below:

ProductNamePriceStockQuantityOrderQuantityInventory
Apple1.0010020120.0
Banana1.2512030187.5
Orange2.15105NULLNULL
Watermelon3.507515315.0

To avoid such situations, the IF() function in conjunction with ISNULL() function can be used to provides 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:

ProductNamePriceStockQuantityOrderQuantityInventory
Apple1.0010020120.0
Banana1.2512030187.5
Orange2.15105NULL225.75
Watermelon3.507515315.0

❮ MariaDB Functions

5