Control Flow

JOURNAL #EIGHT [DAT602]

Control Flow

Control Flow Functions

There are several functions available in SQL that allow logic to be added to queries, this can be done without using the procedural code. Control flow functions return a value for each row that is processed, the values returned are a result of an evaluated comparison. Control flow functions can be utilised in the following clauses:

• SELECT
• WHERE
• ORDER BY
• GROUP BY

Control flow functions allow MySQL uses to add IF - THEN - ELSE logic by utilising the following control flow functions:

CASE Operator

In a THEN branch, the function returns the corresponding result if the condition in the WHEN branch is satisfied, if it is not satisfied, the result in the ELSE branch is returned.

CASE example 1:

SELECT ProductID, StockAmount,
CASE
WHEN StockAmount > 10 THEN "Stock is sufficient"
WHEN StockAmount = 10 THEN "Re-order stock"
ELSE "Stock is very low, urgent re-order"
END
FROM tblStockDetails;


CASE example 2:

SELECT ManufacturerID, Town, Postcode
FROM tblManufacturer
ORDER BY
(CASE
WHEN Town IS NULL THEN Postcode
ELSE Town
END); -- returns ManufacturerID ordered by Town unless Town is NULL in which case ordered by Postcode


IF/ELSE Construct

A value will be returned as a result of a given condition.

IF example:

SELECT ProductID, StockAmount,
IF(StockAmount > 10, 'More', 'Less')
FROM tblStockDetails; -- returns 'More' if the condition is true and 'Less' if the condition is 'False'


IFNULL

The first argument will be returned if it is not NULL, if it is NULL then the second argument is returned.

IFNULL example:

SELECT IFNULL(NULL, 10); -- returns 10


NULLIF

Returns NULL if the first argument equals the second argument, if it does not equal the second argument then it will return the first argument.

NULLIF example:

SELECT NULLIF(25, 25); -- returns NULL
SELECT NULLIF(15, 25); -- returns 15
SELECT NULLIF(15, 5); -- returns 15


References

MySQL Control Flow Functions Overview. (n.d.). MySQL Tutorial. Retrieved April 22, 2021, from https://www.mysqltutorial.org/mysql-control-flow-functions/

Use MySQL Control Flow Functions—CASE, IF, IFNULL, and NULLIF. (n.d.). Retrieved April 22, 2021, from https://www.geeksengine.com/database/single-row-functions/control-flow-functions.php