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

Question

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

For example:

SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Answer 1

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

Answer 2

The case statement is your friend in this situation, and takes one of two forms:

The simple case:

SELECT CASE <variable> WHEN <value>      THEN <returnvalue>
                       WHEN <othervalue> THEN <returnthis>
                                         ELSE <returndefaultcase>
       END AS <newcolumnname>
FROM <table>

The extended case:

SELECT CASE WHEN <test>      THEN <returnvalue>
            WHEN <othertest> THEN <returnthis>
                             ELSE <returndefaultcase>
       END AS <newcolumnname>
FROM <table>

You can even put case statements in an order by clause for really fancy ordering.

Answer 3

From SQL Server 2012 you can use the IIF function for this.

SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM   Product

This is effectively just a shorthand (albeit not standard SQL) way of writing CASE.

I prefer the conciseness when compared with the expanded CASE version.

Both IIF() and CASE resolve as expressions within a SQL statement and can only be used in well-defined places.

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

If your needs can not be satisfied by these limitations (for example, a need to return differently shaped result sets dependent on some condition) then SQL Server does also have a procedural IF keyword.

IF @IncludeExtendedInformation = 1
  BEGIN
      SELECT A,B,C,X,Y,Z
      FROM   T
  END
ELSE
  BEGIN
      SELECT A,B,C
      FROM   T
  END

Care must sometimes be taken to avoid parameter sniffing issues with this approach however.

Answer 4

You can find some nice examples in The Power of SQL CASE Statements, and I think the statement that you can use will be something like this (from 4guysfromrolla):

SELECT
    FirstName, LastName,
    Salary, DOB,
    CASE Gender
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
    END
FROM Employees

Answer 5

Use CASE. Something like this.

SELECT Salable =
        CASE Obsolete
        WHEN 'N' THEN 1
        ELSE 0
    END

Answer 6

SELECT  
(CASE 
     WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES'
                                            ELSE 'NO' 
 END) as Salable
, * 
FROM Product

Answer 7

 SELECT
   CASE 
      WHEN OBSOLETE = 'N' or InStock = 'Y' THEN 'TRUE' 
      ELSE 'FALSE' 
   END AS Salable,
   * 
FROM PRODUCT

Answer 8

From this link, we can understand IF THEN ELSE in T-SQL:

IF EXISTS(SELECT *
          FROM   Northwind.dbo.Customers
          WHERE  CustomerId = 'ALFKI')
  PRINT 'Need to update Customer Record ALFKI'
ELSE
  PRINT 'Need to add Customer Record ALFKI'

IF EXISTS(SELECT *
          FROM   Northwind.dbo.Customers
          WHERE  CustomerId = 'LARSE')
  PRINT 'Need to update Customer Record LARSE'
ELSE
  PRINT 'Need to add Customer Record LARSE' 

Isn't this good enough for T-SQL?

Answer 9

Microsoft SQL Server (T-SQL)

In a select, use:

select case when Obsolete = 'N' or InStock = 'Y' then 'YES' else 'NO' end

In a where clause, use:

where 1 = case when Obsolete = 'N' or InStock = 'Y' then 1 else 0 end

Answer 10

Simple if-else statement in SQL Server:

DECLARE @val INT;
SET @val = 15;

IF @val < 25
PRINT 'Hi Ravi Anand';
ELSE
PRINT 'By Ravi Anand.';

GO

Nested If...else statement in SQL Server -

DECLARE @val INT;
SET @val = 15;

IF @val < 25
PRINT 'Hi Ravi Anand.';
ELSE
BEGIN
IF @val < 50
  PRINT 'what''s up?';
ELSE
  PRINT 'Bye Ravi Anand.';
END;

GO

Answer 11

Use a CASE statement:

SELECT CASE
       WHEN (Obsolete = 'N' OR InStock = 'Y')
       THEN 'Y'
       ELSE 'N'
END as Available

etc...

Answer 12

A new feature, IIF (that we can simply use), was added in SQL Server 2012:

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

Answer 13

Use pure bit logic:

DECLARE @Product TABLE (
    id INT PRIMARY KEY IDENTITY NOT NULL
   ,Obsolote CHAR(1)
   ,Instock CHAR(1)
)

INSERT INTO @Product ([Obsolote], [Instock])
    VALUES ('N', 'N'), ('N', 'Y'), ('Y', 'Y'), ('Y', 'N')

;
WITH cte
AS
(
    SELECT
        'CheckIfInstock' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Instock], 'Y'), 1), 'N'), 0) AS BIT)
       ,'CheckIfObsolote' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Obsolote], 'N'), 0), 'Y'), 1) AS BIT)
       ,*
    FROM
        @Product AS p
)
SELECT
    'Salable' = c.[CheckIfInstock] & ~c.[CheckIfObsolote]
   ,*
FROM
    [cte] c

See working demo: if then without case in SQL Server.

For start, you need to work out the value of true and false for selected conditions. Here comes two NULLIF:

for true: ISNULL(NULLIF(p.[Instock], 'Y'), 1)
for false: ISNULL(NULLIF(p.[Instock], 'N'), 0)

combined together gives 1 or 0. Next use bitwise operators.

It's the most WYSIWYG method.

Answer 14

SELECT 1 AS Saleable, *
  FROM @Product
 WHERE ( Obsolete = 'N' OR InStock = 'Y' )
UNION
SELECT 0 AS Saleable, *
  FROM @Product
 WHERE NOT ( Obsolete = 'N' OR InStock = 'Y' )

Answer 15

SELECT CASE WHEN profile.nrefillno = 0 THEN 'N' ELSE 'R'END as newref
From profile

Answer 16

case statement some what similar to if in SQL server

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

Answer 17

This isn't an answer, just an example of a CASE statement in use where I work. It has a nested CASE statement. Now you know why my eyes are crossed.

 CASE orweb2.dbo.Inventory.RegulatingAgencyName
    WHEN 'Region 1'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'Region 2'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'Region 3'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'DEPT OF AGRICULTURE'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactAg
    ELSE (
            CASE orweb2.dbo.CountyStateAgContactInfo.IsContract
                WHEN 1
                    THEN orweb2.dbo.CountyStateAgContactInfo.ContactCounty
                ELSE orweb2.dbo.CountyStateAgContactInfo.ContactState
                END
            )
    END AS [County Contact Name]

Answer 18

If you're inserting results into a table for the first time, rather than transferring results from one table to another, this works in Oracle 11.2g:

INSERT INTO customers (last_name, first_name, city)
    SELECT 'Doe', 'John', 'Chicago' FROM dual
    WHERE NOT EXISTS
        (SELECT '1' from customers
            where last_name = 'Doe'
            and first_name = 'John'
            and city = 'Chicago');

Answer 19

As an alternative solution to the CASE statement, a table-driven approach can be used:

DECLARE @Product TABLE (ID INT, Obsolete VARCHAR(10), InStock VARCHAR(10))
INSERT INTO @Product VALUES
(1,'N','Y'),
(2,'A','B'),
(3,'N','B'),
(4,'A','Y')

SELECT P.* , ISNULL(Stmt.Saleable,0) Saleable
FROM
    @Product P
    LEFT JOIN
        ( VALUES
            ( 'N', 'Y', 1 )
        ) Stmt (Obsolete, InStock, Saleable)
        ON  P.InStock = Stmt.InStock OR P.Obsolete = Stmt.Obsolete

Result:

ID          Obsolete   InStock    Saleable
----------- ---------- ---------- -----------
1           N          Y          1
2           A          B          0
3           N          B          1
4           A          Y          1

Answer 20

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

Answer 21

You can have two choices for this to actually implement:

1. Using IIF, which got introduced from SQL Server 2012:

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

2. Using Select Case:

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

Answer 22

Using SQL CASE is just like normal If / Else statements. In the below query, if obsolete value = 'N' or if InStock value = 'Y' then the output will be 1. Otherwise the output will be 0. Then we put that 0 or 1 value under the Salable Column.

SELECT
      CASE
           WHEN obsolete = 'N' OR InStock = 'Y'
          THEN 1
        ELSE 0
      END AS Salable
      , *
FROM PRODUCT

Answer 23

Question:

SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

ANSI:

Select 
  case when p.Obsolete = 'N' 
  or p.InStock = 'Y' then 1 else 0 end as Saleable, 
  p.* 
FROM 
  Product p;

Using aliases -- p in this case -- will help prevent issues.

Answer 24

SELECT
if((obsolete = 'N' OR instock = 'Y'), 1, 0) AS saleable, *
FROM
product;

Answer 25

For those who uses SQL Server 2012, IIF is a feature that has been added and works as an alternative to Case statements.

SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM   Product 

Answer 26

I like the use of the CASE statements, but the question asked for an IF statement in the SQL Select. What I've used in the past has been:

SELECT

   if(GENDER = "M","Male","Female") as Gender

FROM ...

It's like the Excel or sheets IF statements where there is a conditional followed by the true condition and then the false condition:

if(condition, true, false)

Furthermore, you can nest the if statements (but then use should use a CASE :-)

(Note: this works in MySQL Workbench, but it may not work on other platforms)

Answer 27

It will be something like that:

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN "The quantity is greater than 30"
    WHEN Quantity = 30 THEN "The quantity is 30"
    ELSE "The quantity is under 30"
END AS QuantityText
FROM OrderDetails;

Answer 28

For the sake of completeness, I would add that SQL uses three-valued logic. The expression:

obsolete = 'N' OR instock = 'Y'

Could produce three distinct results:

| obsolete | instock | saleable |
|----------|---------|----------|
| Y        | Y       | true     |
| Y        | N       | false    |
| Y        | null    | null     |
| N        | Y       | true     |
| N        | N       | true     |
| N        | null    | true     |
| null     | Y       | true     |
| null     | N       | null     |
| null     | null    | null     |

So for example if a product is obsolete but you dont know if product is instock then you dont know if product is saleable. You can write this three-valued logic as follows:

SELECT CASE
           WHEN obsolete = 'N' OR instock = 'Y' THEN 'true'
           WHEN NOT (obsolete = 'N' OR instock = 'Y') THEN 'false'
           ELSE NULL
       END AS saleable

Once you figure out how it works, you can convert three results to two results by deciding the behavior of null. E.g. this would treat null as not saleable:

SELECT CASE
           WHEN obsolete = 'N' OR instock = 'Y' THEN 'true'
           ELSE 'false' -- either false or null
       END AS saleable

Answer 29

Looks like an old question, however if I understood your question and thought correctly, you want to implement the if/else conditional statement in SQL. Both are calculated as a separate column.

The iif() function was introduced in 2012, however let's understand the difference between case and iif() statements. If you need to write just one condition, then iif() is sufficient, however if you want to write multiple iif() conditions within one statement then you will have to use `case' statement as demonstrated in above responses. Here is an example for your reference.

   `select 
    s.name,s.mrn, 
    s.specialtyshort,
    s.admissiondate, 
    s.dischargedate,
    iif(s.admissiondate is not null, 'linked','notlinked') as s.admissionStatus 
    from specialty s`

Now if you need to include any other filter or condition in the above statement, you will need to write another iif() statement. On the other hand, case expression can include multiple conditions within one expression. Do some research on the difference between a case statement and case expression. The below is a case expression that evaluates to a value.

`select
 t.name 
,t.MRID
,t.operationDate
,t.dischargeDate
,case 
     when t.specialty like '%obstetrics%' then 'OBS'
     when t.specialty like '%orthopaedics%' then 'ORT'
     when t.specialty like '%General Surgery%' then 'GEN'
     when t.specialty like '%Cardiology%' then 'CAR'
else 'not available' --evaluates to a value.
end as derived_specialty

from 
OperationsTables t`

Answer 30

There are multiple conditions.

SELECT

(CASE
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1001' THEN 'DM'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1002' THEN 'GS'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1003' THEN 'MB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1004' THEN 'MP'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1005' THEN 'PL'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1008' THEN 'DM-27'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1011' THEN 'PB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1012' THEN 'UT-2'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1013' THEN 'JGC'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1014' THEN 'SB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1015' THEN 'IR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1016' THEN 'UT-3'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1017' THEN 'UT-4'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1019' THEN 'KR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1020' THEN 'SYB-SB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1021' THEN 'GR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1022' THEN 'SYB-KP'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1026' THEN 'BNS'

  ELSE ''
END) AS OUTLET

FROM matrixcrm.Transact