Conditional Statements in SQL

Introduction

Conditional statements in SQL allow you to control the flow of query execution based on specific conditions. They enable you to execute different logic based on different scenarios, making SQL more dynamic and flexible for retrieving and manipulating data.

In this section, we will explore the most commonly used conditional statements in SQL, including CASE, IF, and IFNULL/ISNULL, as well as discuss their practical applications using examples.

The CASE Statement

The CASE statement is one of the most versatile and widely used conditional expressions in SQL. It allows you to evaluate conditions and return a value based on the first condition that evaluates to true.

Syntax: The CASE statement can be written in two forms:

Tables Orders

Products

1. Simple CASE: Compares an expression to multiple possible values.

   CASE expression
       WHEN value1 THEN result1
       WHEN value2 THEN result2
       ...
       ELSE default_result
   END
   

2. Searched CASE: Evaluates a series of conditions rather than comparing a single expression.

   CASE 
       WHEN condition1 THEN result1
       WHEN condition2 THEN result2
       ...
       ELSE default_result
   END
   

Example: Suppose you have an Orders table, and you want to classify orders into different shipping categories based on the order value.

SELECT order_id, total_amount,
CASE
    WHEN total_amount >= 1000 THEN 'High Value'
    WHEN total_amount BETWEEN 500 AND 999 THEN 'Medium Value'
    ELSE 'Low Value'
END AS OrderCategory
FROM Orders;

In this example, the CASE statement categorizes orders into "High Value," "Medium Value," or "Low Value" depending on the total_amount.

Practical Use: CASE is commonly used for:

• Categorizing data into different groups.
• Displaying custom labels for data.
• Replacing IF-THEN-ELSE logic in queries.

The IIF Statement

The IIF function in MySQL evaluates a condition and returns one value if the condition is true, and another value if it is false. It is similar to an IF-THEN-ELSE construct in traditional programming languages.

Syntax:

IIF(condition, true_result, false_result)

Example: Suppose you have a Orders table. You want tofind if customer is a returning customer.

SELECT customer_id, IIF(count(customer_id)  > 1, 'Yes', 'No') AS ReturningCustomer  
FROM Orders
Group by customer_id ;

Practical Use:

• Applying different logic based on conditions (e.g., applying discounts).
• Dynamically altering the result set based on business rules.

The IFNULL() and ISNULL() Functions

The IFNULL() and ISNULL() functions are used to handle NULL values in SQL. Since NULL represents the absence of data, these functions help in replacing NULL values with more meaningful data or a default value.

Syntax: ISNULL(): Similar to IFNULL(), it replaces NULL with a specified value.

  ISNULL(expression, replacement_value)

Example: Let’s say you have a Products table where some products do not have a Quantity listed (NULL), and you want to replace NULL values with a default value =1.

SELECT product_id,Quantity, ISNULL(Quantity, 1) AS price
FROM Products;

In this case, any NULL value in the price column is replaced with the default value of 100.

Practical Use:

• Replacing NULL values with default values to avoid returning incomplete or invalid data.
• Ensuring data consistency in reports.

The COALESCE() Function

The COALESCE() function returns the first non-NULL value from a list of expressions. It is useful when working with multiple columns or expressions that might contain NULL values.

Syntax:

COALESCE(expression1, expression2, ..., expressionN)

Example: Suppose you have a Customers table with columns for phone and AlternatePhone. You want to display the primary phone number if available; otherwise, show the alternate phone number.

SELECT customer_id, customer_name, 
COALESCE(phone, AlternatePhone, 'No Contact') AS ContactNumber
FROM Customers;

Practical Use:

• Handling multiple optional columns to ensure at least one valid value is returned.
• Avoiding NULL values when querying multiple data sources.

Practical Use:

• Filtering data dynamically based on user status, transaction amounts, or other conditions.
• Simplifying complex filtering rules with logical operators.

Practical Application

Let’s tie everything together with a practical example using a customer management system. Suppose we have a Customers table, and we want to categorize customers based on their loyalty points and ensure we handle missing contact information appropriately.

SELECT customer_id, customer_name, 
CASE 
    WHEN LoyaltyPoints >= 1000 THEN 'Platinum'
    WHEN LoyaltyPoints BETWEEN 500 AND 999 THEN 'Gold'
    ELSE 'Silver'
END AS LoyaltyLevel,
COALESCE(phone, AlternatePhone, 'No Contact') AS ContactNumber,
IF(LoyaltyPoints > 500, 'Eligible for Discount', 'Not Eligible') AS DiscountStatus
FROM Customers;

Explanation:

• LoyaltyLevel: We use a CASE statement to classify customers into different loyalty tiers based on their points.
• ContactNumber: We use COALESCE() to prioritize the primary phone number, the alternate phone number, or return 'No Contact' if both are NULL.
• DiscountStatus: An IF function checks if the customer has more than 500 loyalty points to determine if they qualify for a discount.

Conclusion

Conditional statements in SQL provide flexibility in querying and manipulating data by allowing you to handle various conditions dynamically. Whether using CASE for complex logic, IF for simple true/false scenarios, or COALESCE() to handle NULL values, mastering these tools is essential for writing robust SQL queries.

Asked in Interview

• What is a CASE statement in SQL? How does it work?
• What is the difference between IF() and CASE in SQL?
• How is IFNULL() used in SQL?
• How do you handle NULL values in conditional statements?
• What is the difference between COALESCE() and IFNULL()?
• Write a query that uses CASE to assign an employee rank based on their years of experience: "Senior" for more than 10 years, "Mid-level" for 5 to 10 years, and "Junior" for less than 5 years.