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

order status

Now Using if function we can easily show the data in the reports as follows:

mysql if
mysql if

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;

 

Satya Prakash

VOIP Expert: More than 8 years of experience in Asterisk Development and Call Center operation Management. Unique Combination of Skill Set as IT, Analytics and operation management.

Leave a Reply