mysql if function
Syntax:
IF(expr,if_true_expr,if_false_expr)
- MySQL IF function is control flow functions that returns a value based on a condition.
- The IF function is sometimes referred to as IF ELSE or IF THEN ELSE function.
- If the expr evaluates to TRUE i.e., expr is not NULL and expr is not 0, the IF function returns the if_true_expr , otherwise, it returns if_false_expr The IF function returns a numeric or a string, depending on how it is used.
Let’s have an simple example:
We have a table with null value in state column, Now using IF function we can replace null with N/A values.
SELECT customerNumber, customerName, IF(state IS NULL, ‘N/A’, state) state, country FROM customers;
Example used with count function:
We have orders table in which we need to find the status of the object.
In Raw table we have the status column that contains status of the object as dispatched, hold, in progress, cancel etc.
order status
Now Using if function we can easily show the data in the reports as follows:
SELECT COUNT(IF(status = ‘Cancel’, 1, NULL)) Cancel, COUNT(IF(status = ‘Dispatched’, 1, NULL)) Dispatched, COUNT(IF(status = ‘In Process’, 1, NULL)) ‘In Process’, COUNT(IF(status = ‘Hold’, 1, NULL)) ‘Hold’ FROM orders;
Example used with sum function:
We can also use the if function to check the status summary of orders.
SELECT SUM(IF(status = ‘Hold’, 1, 0)) AS Hold, SUM(IF(status = ‘Cancel’, 1, 0)) AS Cancel FROM orders;
