Control Flow Functions in MySQL

By Avinash

October 01, 2011TutorialStart Discussion

MySQL have several Control Flow Functions listed as below:

1) CASE
2) IF
3) IFNULL
4) NULLIF

Control function returns value based on each row processed by the query executed. Control functions can be used on SELECT, WHERE, ORDER BY and GROUP BY statements.

1) CASE

We can say this is just like the switch case in programming languages.

Sample Queries:
// Below will return zero SELECT CASE 0 WHEN 0 THEN 'zero' WHEN 1 THEN 'one' ELSE 'no one' END; // Below will return true SELECT CASE WHEN 5>2 THEN 'true' ELSE 'false' END;
  // Below will return zero
  SELECT CASE 0 WHEN 0 THEN 'zero' WHEN 1 THEN 'one' ELSE 'no one' END;
 
  // Below will return true
  SELECT CASE WHEN 5>2 THEN 'true' ELSE 'false' END;

2) IF

This function takes the three parameters as expression, if expression one is true the it will return second parameter otherwise it will return third parameter.

Sample Queries:
SELECT IF(expr1,expr2,expr3); // return yes SELECT IF(1<5,'yes','no');
  SELECT IF(expr1,expr2,expr3);

  // return yes
  SELECT IF(1<5,'yes','no');

3) IFNULL

This function takes two parameters as a expression. And if expression one is not num then it will return expression one otherwise it will return expression two.

Sample Queries:
SELECT IFNULL(expr1, expr2); // Return 5 SELECT IFNULL(5,0); // Return 10 SELECT IFNULL(NULL,10);
  SELECT IFNULL(expr1, expr2);
 
  // Return 5
  SELECT IFNULL(5,0);
 
  // Return 10
  SELECT IFNULL(NULL,10);

4) NULLIF

This function takes two parameter as a expression. It will return NULL if expr1=expr2 will return TRUE otherwise it will return expr1.

Sample Queries:
SELECT NULLIF(expr1,expr2); // Return NULL SELECT NULLIF(5,5); // Return 10 SELECT NULLIF(10,4);
  SELECT NULLIF(expr1,expr2);
 
  // Return NULL
  SELECT NULLIF(5,5);
 
  // Return 10
  SELECT NULLIF(10,4);

Note: MySQL evaluates expr1 twice if the arguments are not equal.

Share This Article