In SQL, you cannot directly use an IF...THEN construct within a SELECT statement as you would in procedural programming languages. However, SQL provides conditional expressions like CASE that allow you to achieve similar functionality.

---

Using CASE in a SELECT Statement

The CASE statement is used to create conditional logic within a query. Here’s the syntax:

sqlCopy codeSELECT 
    column1,
    column2,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE default_result
    END AS alias_name
FROM table_name;

• WHEN specifies the condition to evaluate.
• THEN defines the value to return if the condition is true.
• ELSE provides a default value if none of the conditions are met (optional).
• END marks the end of the CASE statement.

---

Example: Basic Usage

Suppose you have a table Employees with columns Name, Salary, and Department. You want to create a new column that categorizes employees based on their salary.

sqlCopy codeSELECT 
    Name,
    Salary,
    CASE
        WHEN Salary > 80000 THEN 'High Income'
        WHEN Salary BETWEEN 50000 AND 80000 THEN 'Middle Income'
        ELSE 'Low Income'
    END AS IncomeCategory
FROM Employees;

This query creates a new column, IncomeCategory, that classifies employees into income groups.

---

Example: Multiple Conditions

You can use multiple conditions to handle more complex scenarios. For example, adding department-specific logic:

sqlCopy codeSELECT 
    Name,
    Department,
    CASE
        WHEN Department = 'IT' AND Salary > 90000 THEN 'Top IT Earner'
        WHEN Department = 'HR' AND Salary > 60000 THEN 'Top HR Earner'
        ELSE 'Standard Employee'
    END AS EmployeeCategory
FROM Employees;

---

Example: Using CASE with Aggregations

CASE can also be used in aggregate functions to conditionally count or sum values. For example:

sqlCopy codeSELECT 
    Department,
    SUM(CASE WHEN Salary > 50000 THEN Salary ELSE 0 END) AS TotalHighSalaries,
    COUNT(CASE WHEN Salary > 50000 THEN 1 ELSE NULL END) AS CountHighEarners
FROM Employees
GROUP BY Department;

This query calculates the total salaries and counts employees with a salary above 50,000, grouped by department.

---

Key Points to Remember

1. Placement:
   • CASE can be used in the SELECT, WHERE, GROUP BY, and ORDER BY clauses.
2. Data Types:
   • Ensure all results from the CASE statement have the same or compatible data types.
3. Performance:
   • Complex CASE conditions can impact performance, so optimize where necessary.

---

Example: Conditional Filtering with CASE in WHERE

While CASE cannot directly replace an IF in the WHERE clause, you can use it creatively to simulate conditional logic:

sqlCopy codeSELECT *
FROM Employees
WHERE 
    1 = CASE 
            WHEN Department = 'IT' AND Salary > 90000 THEN 1
            WHEN Department = 'HR' AND Salary > 60000 THEN 1
            ELSE 0
        END;

This query applies conditional filtering based on department and salary.

---

Using CASE is the go-to method for conditional logic within a SELECT statement in SQL.