How to Perform an If...Then in an SQL Select

How do I perform an IF...THEN in an SQL SELECT?

The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server.

SELECT CAST(
             CASE
                  WHEN Obsolete = 'N' or InStock = 'Y'
                     THEN 1
                  ELSE 0
             END AS bit) as Saleable, *
FROM Product

You only need to use the CAST operator if you want the result as a Boolean value. If you are happy with an int, this works:

SELECT CASE
            WHEN Obsolete = 'N' or InStock = 'Y'
               THEN 1
               ELSE 0
       END as Saleable, *
FROM Product

CASE statements can be embedded in other CASE statements and even included in aggregates.

SQL Server Denali (SQL Server 2012) adds the IIF statement which is also available in access (pointed out by Martin Smith):

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product

How do you write an IF ELSE inside a SELECT statement in MSSQL 2008?

Use CASE ... WHEN. The most concise logic seems to be:

SELECT 
  CASE WHEN d.ItemType IN ('INVOICE', 'PACKING') THEN 0 ELSE 1 END
     AS MissingDocuments 
FROM dbo.DocumentDetails AS d

i.e. the Document is missing if it isn't 'Invoice' or 'Packing'

Using If else in SQL Select statement

you can use CASE

SELECT   ...,
         CASE WHEN IDParent < 1 THEN ID ELSE IDPArent END AS ColumnName,
         ...
FROM     tableName

SQL SELECT statement within an IF statement

The reason is that the Exists function is checking the result of the sub-query for existing - are there any rows or not. And, as you return the COUNT, it'll never be not-existing - COUNT returns 0 if there are no rows presented in database.

Try to store the resulting count in a local variable, like in this question:

Using IF ELSE statement based on Count to execute different Insert statements

DECLARE @retVal int

SELECT @retVal = COUNT(*) 
FROM TABLE
WHERE COLUMN = 'Some Value'

IF (@retVal > 0)
BEGIN
    --INSERT SOMETHING
END
ELSE
BEGIN
    --INSERT SOMETHING ELSE
END

How do I perform an IF...THEN in an SQL SELECT in snowflake?

Snowflake as for today does not support SQL-based procedural logic.
The query could be rewritten as:

SELECT *
FROM (SELECT *, 1 AS priority FROM table2
      UNION ALL
      SELECT *, 2 AS priority FROM table1) sub
QUALIFY piority = MIN(priority) OVER();


-- or
SELECT * FROM table2
UNION ALL
SELECT * FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table2);

Assumption: both tables have the same structure.

---

EDIT:

Using Snowflake scripting it is possible to use branching structures:

declare
    res RESULTSET;
    query VARCHAR;
    tab_name STRING;
begin
    if (EXISTS(select 1 from table2)) then
       tab_name := 'table2';
    else
       tab_name := 'table1';
    end if;
    
    query := 'SELECT * FROM ' || :tab_name;
   
    res := (execute immediate :query);
    return table(res);
end;

SQL Select Statement using IF statement and applying math to conditions

You were almost there...

SELECT Price
     , Quantity
     , [Type]
     , CASE WHEN [Type] = 'Fixed' THEN ( (Price * Quantity) * 1.00  /100)
            WHEN [Type] = 'Option' THEN ( (Price * Quantity) * 1.00 * 100)
            ELSE (Price * Quantity) * 1.00
        END AS Value
FROM Holding..Position

Times it by 1.00 to make sure you get the decimal places in your answer.

How to make a SQL IF-THEN-ELSE statement

The IF/Else construct is used to control flow of statements in t-sql. You want a case expression, which is used to conditionally return values in a column.

https://msdn.microsoft.com/en-us/library/ms181765.aspx

Yours would be something like:

case when x = 1 then A else B end as A

SELECT-FROM inside an IF STATEMENT

I think you should using the variable for them.

DECLARE 
    V_quantity_WH1 NUMBER;
    V_quantity_WH2 NUMBER;
BEGIN
    --Sum qty 1 --------------
    SELECT SUM(quantity) INTO V_quantity_WH1 FROM warehouse;

    --Sum qty 2 --------------
    SELECT SUM(quantity) INTO V_quantity_WH2 FROM warehouse2;

    --Compare qty1 and qty2 ----------------
    IF (V_quantity_WH1 > V_quantity_WH2) THEN
        ......;
    END IF; 
END;

if statement using a query in sql

(1) Using a statement block

IF 
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
BEGIN
   PRINT 'There are 5 Touring-3000 bikes.'
END
ELSE 
BEGIN
   PRINT 'There are Less than 5 Touring-3000 bikes.'
END ;

(2) Calling stored procedures.

DECLARE @compareprice money, @cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @compareprice OUT, 
    @cost OUTPUT
IF @cost <= @compareprice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@compareprice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'

More Examples:

MSDN 1
MSDN 2

---

Related Topics