How Can I Perform Conditional Logic in SQL SELECT Statements?

Conditional Logic within SQL SELECT Statements: A Comprehensive Guide

SQL often requires conditional logic within SELECT statements to modify or filter data. The CASE statement provides a versatile solution, supported across numerous SQL dialects.

To mimic an IF...THEN structure in a SELECT query, employ the CASE statement as shown below:

<code class="language-sql">SELECT CAST(
    CASE
        WHEN condition1 OR condition2 THEN value_if_true
        ELSE value_if_false
    END AS data_type) AS new_column, *
FROM table_name;</code>

Here's a breakdown of the syntax:

• condition1 and condition2: The conditions to evaluate.
• value_if_true and value_if_false: The values returned depending on whether the conditions are true or false.
• data_type: The data type of the new_column result.
• table_name: The table from which data is selected.

For instance, to add a Saleable column to a Product table, indicating product availability based on Obsolete and InStock status:

<code class="language-sql">SELECT CAST(
    CASE
        WHEN Obsolete = 'N' OR InStock = 'Y' THEN 1
        ELSE 0
    END AS bit) AS Saleable, *
FROM Product;</code>

A simplified version, omitting CAST, yields an integer result:

<code class="language-sql">SELECT CASE
    WHEN Obsolete = 'N' OR InStock = 'Y' THEN 1
    ELSE 0
END AS Saleable, *
FROM Product;</code>

CASE statements can be nested or integrated into aggregate functions. Moreover, SQL Server 2012 introduced the IIF statement, offering an alternative concise syntax:

<code class="language-sql">SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Saleable, *
FROM Product;</code>

This simplifies conditional logic within your SELECT statements, providing flexibility and efficiency in data manipulation.

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