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

You may want to take a look at this [link](http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx). Regarding: SQL WHERE clauses: Avoid CASE, use Boolean logic — Somebody, Feb 14 '12 at 21:21
@Somebody: not really relevant because the article talks about using logical rewrite rules to convert an implication into a disjunction. The clue is the word 'logical' i.e. something that resolves to true or false, which doesn't apply to projection. TL;DR article applies to `WHERE` and `CHECK` but not `SELECT`. — onedaywhen, May 11 '16 at 16:06
@MartinSmith's answer is the most elegant - use IIF in SQL 2012+. — Murray Foxcroft, Mar 27 '17 at 13:06

score 1957 · Accepted Answer · edited Sep 04 '21 at 07:06

1957

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

edited Sep 04 '21 at 07:06

Hameed Syed

3,531
2
18
27

answered Sep 15 '08 at 14:38

Darrel Miller

134,621
30
189
240

62

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 :) – Archan Mishra Sep 13 '11 at 15:41
24

and don't forget the END – Simon_Weaver Jan 28 '14 at 10:52
Although this works, using the IFF() function will be the better answer – Gerrie Pretorius Oct 09 '15 at 15:42
@ReeveStrife You mean `IIF()`https://msdn.microsoft.com/en-us/library/hh213574.aspx?f=255&MSPPError=-2147217396 – katalin_2003 Mar 30 '16 at 15:03
7

@ReeveStrife Only iif SQL Server 2012+ – stuartdotnet May 30 '16 at 03:26
1

@ArchanMishra Hi Archan, could you please explain to me why you should not enclose your conditions ? – Bernard Walters Jan 16 '17 at 12:30
4

Beware of dirty secrets of CASE/IIF: https://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression – Jan 27 '17 at 12:21
1

@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. – Fandango68 Mar 21 '17 at 03:22
Thank you good sir, CASE WHEN was what i was looking for! – Jacob Broughton Jul 17 '21 at 21:59

score 353 · Answer 2 · edited Oct 28 '15 at 00:07

353

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.

edited Oct 28 '15 at 00:07

Christoph

4,053
3
23
38

answered Sep 15 '08 at 15:10

Jonathan

24,773
13
64
85

34

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 – Ben Jun 18 '12 at 10:22
9

I always feel like the 2nd one is simpler. – Hogan Apr 15 '16 at 20:32
4

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. – magnum_pi May 18 '16 at 16:34
2

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. – Remus.A Feb 23 '18 at 11:24
i dont think its a personal preference of choosing one over the other.. its the use case :D – Michael Fronzek Nov 25 '20 at 11:20

score 300 · Answer 3 · edited Jun 03 '19 at 15:54

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.

This should be the answer if you want a IF.. then statement in SQL. — Mr.J, Mar 16 '17 at 03:27

score 102 · Answer 4 · edited May 24 '11 at 17:31

102

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

edited May 24 '11 at 17:31

Peter Mortensen

30,030
21
100
124

answered Sep 15 '08 at 14:39

sven

17,736
10
49
62

4

see: http://meta.stackexchange.com/questions/103053/how-aggressively-should-we-maintain-and-improve-very-popular-questions for an interesting discussion. I the two links you provide do add extra context, which I support. – Sam Saffron Aug 19 '11 at 02:47
2

The refer is really useful and highly recommended in case of additional details – baymax Dec 07 '16 at 14:56

score 82 · Answer 5 · answered Sep 15 '08 at 14:37

82

Use CASE. Something like this.

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

answered Sep 15 '08 at 14:37

palehorse

24,595
3
37
47

score 52 · Answer 6 · edited Mar 29 '12 at 12:36

52

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

edited Mar 29 '12 at 12:36

Zyku

1,399
2
22
37

answered Sep 15 '08 at 14:37

John Sheehan

75,663
30
157
192

score 49 · Answer 7 · edited Jun 03 '19 at 15:52

49

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?

edited Jun 03 '19 at 15:52

Peter Mortensen

30,030
21
100
124

answered Jan 06 '10 at 01:02

Ken

2,135
2
21
32

3

This isn't what the requester wanted, but is very useful to know that you can use if statements *outside* a select statement. – Jonathan Apr 10 '13 at 08:06
2

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. – JustJohn Feb 11 '16 at 21:12

score 49 · Answer 8 · edited Jun 03 '19 at 15:50

49

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

edited Jun 03 '19 at 15:50

Peter Mortensen

30,030
21
100
124

answered Sep 15 '08 at 14:40

user7658

944
8
7

1

why wouldn't you just do `where Obsolete = 'N' or InStock = 'Y'` and cut the where in half practically – maraaaaaaaa Jun 07 '19 at 22:04

score 46 · Answer 9 · answered Sep 15 '08 at 14:37

46

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

answered Sep 15 '08 at 14:37

Santiago Cepas

3,984
2
24
31

score 32 · Answer 10 · edited Jun 03 '19 at 15:57

32

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

edited Jun 03 '19 at 15:57

Peter Mortensen

30,030
21
100
124

answered Dec 17 '15 at 17:24

Ravi Anand

4,559
7
40
70

2

Late but is it usable inside `SELECT` as OP asked? – abdul qayyum Feb 17 '19 at 10:23

score 24 · Answer 11 · edited Sep 30 '17 at 00:12

24

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

edited Sep 30 '17 at 00:12

twoleggedhorse

4,850
4
22
38

answered Apr 26 '16 at 15:10

sandeep rawat

4,590
1
17
35

1

This answer repeats (with less detail) what was [already provided in the answer by Martin Smith](https://stackoverflow.com/a/6769805/4515489) several years ago. – jk7 Oct 31 '18 at 19:47
1

@jk7 this was the first answer to the question . – sandeep rawat Nov 01 '18 at 02:46
4

Not from what I see. It says your answer was posted Apr 26 '16 and Martin's was posted Jul 20 '11. – jk7 Nov 02 '18 at 14:40

score 24 · Answer 12 · edited Nov 04 '16 at 12:45

24

Use a CASE statement:

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

etc...

edited Nov 04 '16 at 12:45

Krizzy

77
1
3
6

answered Sep 15 '08 at 14:40

score 22 · Answer 13 · edited Jun 03 '19 at 15:56

22

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.

edited Jun 03 '19 at 15:56

Peter Mortensen

30,030
21
100
124

answered Jun 08 '13 at 22:21

Tomasito

1,834
20
42

19

-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 :-/ – Riegardt Steyn Jun 22 '13 at 08:47
2

@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). – Tomasito Jun 23 '13 at 09:52
1

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 '&'. – Mark Hurd Nov 16 '16 at 00:19
3

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. – objectNotFound Nov 24 '17 at 17:04
1

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. – Suncat2000 Nov 30 '18 at 13:19
1

`NULLIF` link from Microsoft is expired. here is new working link: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql?view=sql-server-2017 Updated in answer. – abdul qayyum Feb 17 '19 at 10:18

score 18 · Answer 14 · answered May 11 '16 at 15:56

18

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

answered May 11 '16 at 15:56

onedaywhen

53,058
12
94
134

score 16 · Answer 15 · answered Aug 25 '15 at 09:44

16

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

answered Aug 25 '15 at 09:44

Mohammad Atiour Islam

4,970
3
41
46

score 13 · Answer 16 · answered Dec 09 '15 at 12:15

13

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

answered Dec 09 '15 at 12:15

Chanukya

5,685
1
18
35

2

Could you please put some explanation on how this answers the question asked? – Guanxi Dec 09 '15 at 13:32
@Guanxi: though not my answer, a 'case' generalizes an 'if-then-else' (from 2 cases to many) – JosephDoggie Oct 02 '18 at 16:39

score 12 · Answer 17 · edited Oct 11 '16 at 08:00

12

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]

edited Oct 11 '16 at 08:00

NzGuy

382
3
14

answered Feb 11 '16 at 21:35

JustJohn

1,227
2
21
44

1

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. – JustJohn Oct 12 '16 at 16:30
1

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 `CASE`statement, not an `IF`. – Mr.J Mar 22 '17 at 07:16
@Mr.J: though not my answer, a 'case' generalizes an 'if-then-else' (from 2 cases to many) – JosephDoggie Oct 02 '18 at 16:40

score 11 · Answer 18 · answered Oct 26 '12 at 15:30

11

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

answered Oct 26 '12 at 15:30

Robert B. Grossman

151
1
2

4

the tags say SQL Server, TSQL – Malachi Dec 27 '12 at 15:46

score 9 · Answer 19 · edited Jun 03 '19 at 16:00

9

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

edited Jun 03 '19 at 16:00

Peter Mortensen

30,030
21
100
124

answered Jan 31 '18 at 11:31

Serkan Arslan

12,740
4
28
42

Saleable is used in where condition in query? – Bhavin Thummar Nov 08 '19 at 12:14
It can be use in where condition. – Serkan Arslan Nov 08 '19 at 12:36

score 8 · Answer 20 · answered Aug 08 '17 at 21:33

8

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

answered Aug 08 '17 at 21:33

score 7 · Answer 21 · edited Jun 03 '19 at 16:02

7

You can have two choices for this to actually implement:

Using IIF, which got introduced from SQL Server 2012:

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

Using Select Case:

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

edited Jun 03 '19 at 16:02

Peter Mortensen

30,030
21
100
124

answered Feb 28 '19 at 19:09

score 5 · Answer 22 · edited Apr 07 '14 at 14:16

5

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

edited Apr 07 '14 at 14:16

demongolem

9,148
36
86
104

answered Jan 08 '14 at 10:18

Dibin

1,442
1
14
17

1

This answer repeats (with less detail) what was [already provided in the answer by Martin Smith](https://stackoverflow.com/a/6769805/4515489) several years ago. – jk7 Oct 31 '18 at 19:44

score 5 · Answer 23 · edited Dec 15 '20 at 07:34

5

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

edited Dec 15 '20 at 07:34

The AG

610
8
17

answered Nov 25 '20 at 14:04

yusuf hayırsever

624
6
17

score 4 · Answer 24 · answered Nov 30 '16 at 11:05

4

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

answered Nov 30 '16 at 11:05

SURJEET SINGH Bisht

866
10
15

7

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. – Vince Bowdren Nov 30 '16 at 16:27
5

This answer doesn't add anything new. In fact this exact same line has been part of the accepted answer [for over 5 years](http://stackoverflow.com/posts/63480/revisions). – S.L. Barth Nov 30 '16 at 19:20
1

In addition is important to mention that the [IIF](https://msdn.microsoft.com/en-us/library/hh213574.aspx) only applies for SQL Server starting with 2012 – Ivan Rascon Jan 26 '17 at 16:24

score 4 · Answer 25 · edited Mar 06 '19 at 07:32

4

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.

edited Mar 06 '19 at 07:32

Bikramjeet Singh

679
1
7
22

answered Nov 02 '18 at 15:44

David Cohn

69
4

Tharuka Madumal · Answer 26 · 2020-02-18T11:45:44.847

4

Using SQL CASE is just like normal If / Else statements. In below query, If obsolete value = 'N' or If InStock value = 'Y' Then Output will be 1. Otherwise 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

edited Feb 18 '20 at 11:45

answered Feb 18 '20 at 10:42

Tharuka Madumal

141
5

1

It is just like normal If / Else statements. If obsolete value = 'N' or If InStock value = 'Y' Then Output will be 1. Otherwise output will be 0. – Tharuka Madumal Feb 18 '20 at 11:22
2

Thank you. Please [Edit](https://stackoverflow.com/posts/60278997/edit) your post to add this explanation. Like: The `If..Then...Else..` statements usage in `SQL` as follows.... – Feb 18 '20 at 11:39

score 3 · Answer 27 · answered Oct 31 '19 at 12:06

3

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;

answered Oct 31 '19 at 12:06

Muhammad Awais

3,827
1
36
36

1

Can we use QuantityText value in where condition in a query ? for example `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 WHERE QuantityText = 'The quantity is 30'; ` – Bhavin Thummar Nov 08 '19 at 12:14

Prashant Marathay · Answer 28 · 2020-04-09T23:12:58.590

3

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 MySQLWorkbench but may not work in other platforms)

edited Apr 09 '20 at 23:12

answered Apr 09 '20 at 01:31

Prashant Marathay

141
2
5

2

[IF FUNCTION](https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html) It seems present in MySQL, but not in MSSQL, In MSSQL [IIF](https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-ver15) can be used since version 2012. – Luuk Aug 01 '20 at 09:15

score 2 · Answer 29 · edited Mar 06 '19 at 07:49

2

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

edited Mar 06 '19 at 07:49

Bikramjeet Singh

679
1
7
22

answered Oct 08 '18 at 06:34

laplace

488
5
13

8

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?](https://stackoverflow.com/help/how-to-answer). Thanks – sɐunıɔןɐqɐp Oct 08 '18 at 06:52
3

I think you will find this doesn't execute, because it is missing any output following the 'THEN' keyword. – Dodecaphone Apr 05 '19 at 14:00

score 2 · Answer 30 · answered Nov 28 '19 at 10:17

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

score 1 · Answer 31 · edited May 21 '20 at 21:23

1

You can use Case Statement:

Select 
Case WHEN (Obsolete = 'N' or InStock = 'Y') THEN 1 ELSE 0 END Saleable,
Product.*
from Product

edited May 21 '20 at 21:23

mechnicov

6,304
3
18
44

answered May 21 '20 at 18:44

The AG

610
8
17

This is really just a repeat of the existing answers. – Pang May 24 '21 at 06:15

score 1 · Answer 32 · answered Apr 20 '22 at 04: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

Davide Valiante · Answer 33 · 2022-04-07T08:52:58.717

0

You can find some nice examples in https://spikeslot.com/guides-and-tricks/ca, and I think the statement that you can use will be something like this (from spikeslot.com/ca):

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

edited Apr 07 '22 at 08:52

answered Apr 07 '22 at 08:43

Davide Valiante

157
2
12

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

33 Answers33

Linked

Related