How to Implement Conditional Logic in SQL SELECT Statements?

Conditional Logic within SQL SELECT Statements

SQL's SELECT statements don't directly support IF...THEN constructs. However, two effective alternatives exist for implementing conditional logic: the CASE statement and the IIF function (available in certain SQL dialects).

Utilizing the CASE Statement

The CASE statement mirrors the functionality of IF...THEN in other programming languages. It evaluates conditions sequentially and returns a value based on the first condition met.

Syntax:

SELECT
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        ELSE default_result
    END AS new_column_name,
    other_columns
FROM your_table;

Example:

SELECT
    CASE
        WHEN IsActive = 'Yes' THEN 'Active'
        WHEN IsActive = 'No' THEN 'Inactive'
        ELSE 'Unknown'
    END AS Status,
    *
FROM Users;

This query adds a "Status" column, assigning 'Active', 'Inactive', or 'Unknown' based on the IsActive column's value.

Employing the IIF Function (SQL Server)

The IIF function (introduced in SQL Server 2012) offers a concise way to handle conditional logic within SELECT statements.

Syntax:

IIF(condition, true_result, false_result)

Example:

SELECT
    IIF(OrderTotal > 100, 'High Value', 'Low Value') AS OrderCategory,
    *
FROM Orders;

This example creates an "OrderCategory" column, classifying orders as 'High Value' or 'Low Value' depending on the OrderTotal. Note that IIF is specific to SQL Server and might not be available in other database systems. For broader compatibility, the CASE statement is generally preferred.

The above is the detailed content of How to Implement Conditional Logic in SQL SELECT Statements?. For more information, please follow other related articles on the PHP Chinese website!