How do you use conditional logics in SQL in select statements?

 In SQL, you can implement conditional logic in SELECT statements using several methods, including CASE, IF, and COALESCE, depending on the SQL dialect you're working with. The most common approach is the CASE expression, which allows you to apply conditional logic to individual rows in your query results.

1. CASE Expression

The CASE expression is SQL's way of adding conditional logic within a query. It allows you to perform conditional checks and return specific values based on those conditions.

There are two forms of the CASE expression:

a. Simple CASE Expression:

In this form, the expression checks one value against multiple conditions.

SELECT column_name,
       CASE column_name
           WHEN 'Condition1' THEN 'Result1'
           WHEN 'Condition2' THEN 'Result2'
           ELSE 'DefaultResult'
       END AS result_column
FROM table_name;

• This checks the value of column_name. If it matches 'Condition1', it returns 'Result1'; if it matches 'Condition2', it returns 'Result2'; otherwise, it returns 'DefaultResult'.

b. Searched CASE Expression:

This form allows you to evaluate more complex conditions.

SELECT column_name,
       CASE 
           WHEN column_name > 10 THEN 'GreaterThan10'
           WHEN column_name = 10 THEN 'EqualTo10'
           ELSE 'LessThan10'
       END AS result_column
FROM table_name;

• Here, the CASE expression checks for multiple conditions (e.g., greater than 10, equal to 10, or less than 10).

Example:

Let's say you have a sales table with columns amount and region, and you want to categorize sales into "High", "Medium", and "Low" based on the amount.

SELECT amount,
       CASE
           WHEN amount > 1000 THEN 'High'
           WHEN amount BETWEEN 500 AND 1000 THEN 'Medium'
           ELSE 'Low'
       END AS sales_category
FROM sales;

2. Using IF (MySQL-Specific)

In MySQL, you can also use the IF() function for conditional logic, which works similarly to an IF-ELSE statement in programming.

SELECT column_name,
       IF(column_name > 10, 'GreaterThan10', 'LessThanOrEqual10') AS result_column
FROM table_name;

• The first parameter (column_name > 10) is the condition. If it is true, the second parameter ('GreaterThan10') is returned; otherwise, the third parameter ('LessThanOrEqual10') is returned.

3. Using COALESCE and NULLIF

• COALESCE(): Returns the first non-NULL value in the list of arguments.

SELECT COALESCE(column_name, 'Default Value') AS result_column
FROM table_name;

• NULLIF(): Compares two expressions and returns NULL if they are equal, or the first expression otherwise.

SELECT NULLIF(column_name, 0) AS result_column
FROM table_name;

4. Example with Multiple Conditions

Let's say you want to classify products in a products table into "Expensive", "Affordable", or "Cheap" based on the price and category. You can use the CASE expression:

SELECT product_name,
       price,
       category,
       CASE
           WHEN price > 1000 THEN 'Expensive'
           WHEN price BETWEEN 500 AND 1000 THEN 'Affordable'
           ELSE 'Cheap'
       END AS price_category
FROM products;

Summary of Options:

• CASE (standard SQL): Used for complex conditions and multiple scenarios.
• IF (MySQL specific): A more straightforward conditional structure for simple conditions.
• COALESCE: Returns the first non-NULL value.
• NULLIF: Returns NULL if two expressions are equal, otherwise returns the first expression.

Each of these can be used within the SELECT statement to apply conditional logic to your query results, helping you categorize or transform data based on various conditions.