By Eric Labashosky


2008-09-15 14:34:09 8 Comments

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

24 comments

@Tomasito 2013-06-08 22:21:28

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 MSSQL

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.

@Heliac 2013-06-22 08:47:00

-1 for Code Obfuscation. Seriously, this is about as far from WYSIWYG as you can get! A ruddy unreadable mess, and if I had to work on your code, I would be cursing all day long... sorry :-/

@Tomasito 2013-06-23 09:52:05

@Heliac put cte part in View and you will never see the mess. For long and complicated AND, OR, NOT it it more readable than CASE (that part outside cte of course).

@Mark Hurd 2016-11-16 00:19:38

I've given this a +1 for the neatness, once it's in a cte, but note that the answer is currently wrong for the question. You need an '|' not an '&'.

@objectNotFound 2017-11-24 17:04:22

Totally agree with @Heliac. While it is syntactically correct and works fine it is just not easily supportable. Putting it in a CTE will just move that piece of un-readable code somewhere else.

@Suncat2000 2018-11-30 13:19:31

The table method of checking combination could have its advantages. Using a table variable and joining it to the existing query could provide a set-based solution without a case. This answer is a poor example, but the table idea itself has merit.

@abdul qayyum 2019-02-17 10:18:05

NULLIF link from Microsoft is expired. here is new working link: docs.microsoft.com/en-us/sql/t-sql/language-elements/… Updated in answer.

@David Cohn 2018-11-02 15:44:37

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.

@gii96 2018-10-08 06:34:01

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

@sɐunıɔןɐqɐp 2018-10-08 06:52:02

From Review: Welcome to Stack Overflow! Please don't answer just with source code. Try to provide a nice description about how your solution works. See: How do I write a good answer?. Thanks

@Serkan Arslan 2018-01-31 11:31:07

As an alternative solution to the CASE statement 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

@sandeep rawat 2016-04-26 15:10:54

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

@jk7 2018-10-31 19:47:50

This answer repeats (with less detail) what was already provided in the answer by Martin Smith several years ago.

@sandeep rawat 2018-11-01 02:46:14

@jk7 this was the first answer to the question .

@jk7 2018-11-02 14:40:53

Not from what I see. It says your answer was posted Apr 26 '16 and Martin's was posted Jul 20 '11.

@user8422856 2017-08-08 21:33:52

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

@Ravi Anand 2015-12-17 17:24: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

@abdul qayyum 2019-02-17 10:23:07

Late but is it usable inside SELECT as OP asked?

@SURJEET SINGH Bisht 2016-11-30 11:05:06

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

@Vince Bowdren 2016-11-30 16:27:31

Hi Surjeet Singh Bisht; your code might be correct, but with some context it would make a better answer; for example, you could explain how and why this proposed change would resolve the questioner's problem, perhaps including a link to the relevant documentation. That would make it more useful to them, and also more useful to other site readers who are looking for solutions to similar problems.

@S.L. Barth 2016-11-30 19:20:48

This answer doesn't add anything new. In fact this exact same line has been part of the accepted answer for over 5 years.

@Ivan Rascon 2017-01-26 16:24:03

In addition is important to mention that the IIF only applies for SQL Server starting with 2012

@Christopher 2008-09-15 14:40:26

Use a CASE statement:

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

etc...

@JustJohn 2016-02-11 21:35:21

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]

@JustJohn 2016-10-12 16:30:01

The edit that reformatted the Case statements is all fine and dandy and makes it more understandable but the SQL would still all lump up in the view that is using it.

@Mr.J 2017-03-22 07:16:38

I'm just wandering why CASE become upvoted and marked as a answer instead of the IF which should have been the answer, like this one, this is still a CASEstatement, not an IF.

@JosephDoggie 2018-10-02 16:40:51

@Mr.J: though not my answer, a 'case' generalizes an 'if-then-else' (from 2 cases to many)

@Darrel Miller 2008-09-15 14:38:24

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 do the CAST 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

@Archan Mishra 2011-09-13 15:41:37

Just a additional word of caution don't enclose your conditions in brakets when using case. Took quite a bit of time to realise that :)

@Simon_Weaver 2014-01-28 10:52:37

and don't forget the END

@Cas Bloem 2014-04-22 12:49:30

and the AS bit!

@Ujjwal Singh 2014-07-01 21:24:29

Case, When, Else and End should be indented parallel (along same line) - and only then should be indented further inwards - works best for me.

@JMD 2015-07-10 20:33:26

I had a need for nested CASEs and was forgetting that I needed multiple matching ENDs.

@Gerrie Pretorius 2015-10-09 15:42:56

Although this works, using the IFF() function will be the better answer

@katalin_2003 2016-03-30 15:03:22

@ReeveStrife You mean IIF()msdn.microsoft.com/en-us/library/…

@stuartdotnet 2016-05-30 03:26:07

@ReeveStrife Only iif SQL Server 2012+

@Bernard Walters 2017-01-16 12:30:07

@ArchanMishra Hi Archan, could you please explain to me why you should not enclose your conditions ?

@user170442 2017-01-27 12:21:58

Beware of dirty secrets of CASE/IIF: sqlperformance.com/2014/06/t-sql-queries/…

@Fandango68 2017-03-21 03:22:40

@ArchanMishra you can. I thought he meant that it would slow things down, but no. You can use brackets as many times as you want in SQL Server, as long as it make logical sense to SQL.

@Abdul Ahad 2018-01-20 13:44:39

@DarrelMiller you can probably return TRUE or FALSE in the CASE statement and avoid the CAST, not sure if it works on SQL Server, but I think it's SQL. I've never seen anything like that

@onedaywhen 2016-05-11 15:56:30

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' )

@Chanukya 2015-12-09 12:15:36

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

@Guanxi 2015-12-09 13:32:05

Could you please put some explanation on how this answers the question asked?

@JosephDoggie 2018-10-02 16:39:15

@Guanxi: though not my answer, a 'case' generalizes an 'if-then-else' (from 2 cases to many)

@Ken 2010-01-06 01:02:08

From this link, we can uderstand 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 ?

@Jonathan 2013-04-10 08:06:51

This isn't what the requester wanted, but is very useful to know that you can use if statements outside a select statement.

@JustJohn 2016-02-11 21:12:24

EXISTS is good because it kicks out of the search loop if item is found. A COUNT runs until the end of table rows. Nothing to do with question, but something to know.

@Jonathan 2008-09-15 15:10:49

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.

@Ben 2012-06-18 10:22:11

I know this is old, but i think it should be noted that you can add a AS Col_Name after the END to name the resulting column

@Hogan 2016-04-15 20:32:58

I always feel like the 2nd one is simpler.

@magnum_pi 2016-05-18 16:34:49

Agreed, I almost always end up using the extended case statement because the conditions I want to test on are always more complex than just the one variable itself. It also just feels easier to me to read.

@Remus.A 2018-02-23 11:24:50

Good explanation of both situations, with or without variable. With the variable the condition needs to satisfy an equality between the variable after the case statement and the one you base your condition on, without variable you can add a self-sufficient condition to test.

@Stanley Okpala Nwosa 2018-10-20 09:30:40

I am more convenient with the second option. The two are equally fine.

@atik sarker 2015-08-25 09:44:46

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

@Martin Smith 2011-07-20 23:39:10

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 dependant 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.

@Mr.J 2017-03-16 03:27:52

This should be the answer if you want a IF.. then statement in SQL.

@Dibin 2014-01-08 10:18:59

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 

@jk7 2018-10-31 19:44:22

This answer repeats (with less detail) what was already provided in the answer by Martin Smith several years ago.

@Robert B. Grossman 2012-10-26 15:30: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');

@Malachi 2012-12-27 15:46:27

the tags say SQL Server, TSQL

@John Sheehan 2008-09-15 14:37:44

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

@sven 2008-09-15 14:39:49

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

@Sam Saffron 2011-08-19 02:47:35

see: meta.stackexchange.com/questions/103053/… for an interesting discussion. I the two links you provide do add extra context, which I support.

@baymax 2016-12-07 14:56:44

The refer is really useful and highly recommended in case of additional details

@user7658 2008-09-15 14:40:04

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

@Santiago Cepas 2008-09-15 14:37:56

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

@palehorse 2008-09-15 14:37:49

Use CASE. Something like this.

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

Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

32 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

38 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

24 Answered Questions

22 Answered Questions

[SOLVED] Check if table exists in SQL Server

25 Answered Questions

37 Answered Questions

22 Answered Questions

[SOLVED] Insert into ... values ( SELECT ... FROM ... )

38 Answered Questions

[SOLVED] Parameterize an SQL IN clause

Sponsored Content