Control flow functions in MySQL allow you to perform conditional operations within SQL queries. They are useful for making decisions and handling NULL values. Here is a list of common control flow functions, along with real-time examples and explanations for each:

1. IF():

   • IF() allows you to perform a conditional operation and return one of two values based on a specified condition. It has the following syntax: IF(condition, value_if_true, value_if_false).

   Example:

   sqlCopy code
   SELECT product_name, IF(price < 10, 'Low Price', 'High Price') AS price_category
   FROM products;
   
   

   Output: This query categorizes products as "Low Price" or "High Price" based on their price.

2. CASE:

   • CASE allows you to perform conditional logic with multiple conditions and values. It has two forms: simple CASE and searched CASE. The simple CASE form is used when you want to compare a single expression with multiple possible values.

   Example (Simple CASE):

   sqlCopy code
   SELECT product_name,
          CASE category
              WHEN 'Electronics' THEN 'High-Tech'
              WHEN 'Clothing' THEN 'Fashion'
              ELSE 'Other'
          END AS category_group
   FROM products;
   
   

   Output: This query categorizes products into groups based on their category.

   Example (Searched CASE):

   sqlCopy code
   SELECT product_name,
          CASE
              WHEN price < 10 THEN 'Low Price'
              WHEN price >= 10 AND price <= 50 THEN 'Medium Price'
              ELSE 'High Price'
          END AS price_category
   FROM products;
   
   

   Output: This query categorizes products as "Low Price," "Medium Price," or "High Price" based on their price.

3. COALESCE():

   • COALESCE() returns the first non-null value from a list of expressions. It is often used to handle NULL values gracefully.

   Example:

   sqlCopy code
   SELECT product_name, COALESCE(discounted_price, regular_price) AS final_price
   FROM products;
   
   

   Output: This query returns the final price for each product, either the discounted price or the regular price, whichever is not null.

4. NULLIF():

   • NULLIF() compares two expressions and returns null if they are equal; otherwise, it returns the first expression.

   Example:

   sqlCopy code
   SELECT product_name, NULLIF(supplier, 'Unknown') AS supplier_name
   FROM products;
   
   

   Output: This query replaces the supplier name with NULL if it is "Unknown," effectively filtering out unknown suppliers.

These control flow functions provide conditional logic within SQL queries, allowing you to make decisions, handle NULL values, and customize the output based on specific conditions.