How Can I Implement Conditional Logic in SQL SELECT Statements using CASE and IIF?

Mastering Conditional Logic in SQL SELECT Statements: CASE and IIF

Efficiently managing conditional logic within SQL SELECT statements is crucial for data manipulation and filtering. While a direct IF...THEN construct isn't available in standard SQL, the CASE statement and IIF (in SQL Server Denali and later) provide robust alternatives.

The CASE Statement: Your SQL Conditional Workhorse

The CASE statement mirrors the functionality of IF...THEN, evaluating conditions sequentially and returning a value based on the first true condition. Its structure is as follows:

<code class="language-sql">SELECT
  CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ...
    ELSE default_value
  END AS <alias>,  -- Assign an alias to the result
  *
FROM <table_name></code>

Consider this practical application:

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

Note the CAST operator. Without it, the result is an integer (0 or 1); with CAST, it's converted to a Boolean (TRUE or FALSE).

IIF: A Concise Conditional Option (SQL Server)

SQL Server introduced the IIF (Immediate If) statement, offering a more compact alternative to CASE:

<code class="language-sql">SELECT IIF(condition, true_value, false_value) AS <alias>,
  *
FROM <table_name></code>

Here's how it applies to our example:

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

Both CASE and IIF are indispensable tools for implementing conditional logic in SQL SELECT queries, empowering dynamic data manipulation and efficient filtering.

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