CASE, IF, IFNULL is control flow operators
- CASE is one of the control flow function where this is just like the switch case in most programming languages.
- SELECT OrderID, Quantity,
WHEN Quantity > 30 THEN "The quantity is greater than 30"
WHEN Quantity = 30 THEN "The quantity is 30"
ELSE "The quantity is something else"
- IF(expr1,expr2,expr3). IF function accepts three arguments and the result is returned based on if expr1 is TRUE.
- SELECT ProductName,
IF(UnitPrice>=20, 'High Price', 'Low Price') AS "Price Level"
- IFNULL(expr1,expr2). If expr1 is not NULL, the function returns expr1. Otherwise it returns expr2.
- SELECT FirstName, LastName, IFNULL(ReportsTo, 'Is CEO') FROM Employees;