Can you have if-then-else logic in SQL?

Question

I need to do select data from a table based on some kind of priority like so:

select product, price from table1 where project = 1

-- pseudo: if no price found, do this:
select product, price from table1 where customer = 2

-- pseudo: if still no price found, do this:
select product, price from table1 where company = 3

That is, if I found 3 products with prices based on project = X, I don't want to select on customer = Y. I just want to return the resulting 3 rows and be done.

How are you supposed to do stuff like this in SQL? Use some kind of CASE-statement for the pseudo-if's? Do a union or some other smart thing?

Edit: I'm using MS SQL.

Thanks!

This will help you http://stackoverflow.com/questions/5487892/sql-server-case-when-or-then-else-end-the-or-is-not-supported — Meherzad, Feb 26 '13 at 09:55
Wich RDBMS are you using, MS SQL Server, MySQL, PostgreSQL...? For each one of them the control flow instructions set are bit different — Yaroslav, Feb 26 '13 at 09:55
Sorry, it is bit unclear. What do you mean no price found? Is that for no records found? and how many records do you need at final results? One or many? — Kaf, Feb 26 '13 at 10:33
Also see question ... http://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-an-sql-select — SteveC, Sep 05 '13 at 11:50
the flagged duplicate doesn't actually apply. It was a question about in-query `IF` handling, and this is a question about control of flow handling — Tab Alleman, Jan 05 '16 at 15:05

Alex · Accepted Answer · 2013-02-26T10:15:39.080

112

You can make the following sql query

IF ((SELECT COUNT(*) FROM table1 WHERE project = 1) > 0) 
    SELECT product, price FROM table1 WHERE project = 1
ELSE IF ((SELECT COUNT(*) FROM table1 WHERE project = 2) > 0) 
    SELECT product, price FROM table1 WHERE project = 2
ELSE IF ((SELECT COUNT(*) FROM table1 WHERE project = 3) > 0)
    SELECT product, price FROM table1 WHERE project = 3

edited Feb 26 '13 at 10:15

answered Feb 26 '13 at 09:57

Alex

8,827
3
42
58

How do you this in a codeigniter query builder? – Cronas De Se Feb 13 '16 at 14:07
ORA-00900: sentencia SQL no válida – Dec 04 '17 at 10:25
@delive This is MS SQL, not Oracle. – T. Sar Mar 21 '19 at 12:14
That's not valid... So not sure how it was upvoted 100 times. – Dalibor May 16 '19 at 07:08
@Dalibor What is your concern about the answer? It does not work for your? – Alex May 16 '19 at 13:56
MS SQL does not have an ELSE IF, so definitely does not work. I'd update the answer to say it's for Oracle which i think it is for. – Dalibor May 17 '19 at 00:21
@Dalibor Check this link: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql?view=sql-server-2017 – Alex May 21 '19 at 14:44
@Dalibor According to the link Transact SQL supports IF ELSE where can be another IF ELSE for simple cases or BEGIN END statement for more complicated scenarios or any other statement supported by SQL Server. Not sure which version you was using to run the query and why if failed but I remember that the syntax worked for me. You can find other examples in SO as well (https://stackoverflow.com/questions/32614843/t-sql-how-to-end-an-if-else-if-else-block) – Alex May 21 '19 at 15:00
Hi Alex, I wasn't talking about IF ELSE. I am talking about ELSE IF. It's a very big difference between the two. – Dalibor May 26 '19 at 21:31
Please check below links to know more regarding Common Alternatives to IF ELSE Logic in SQL Server with Simple Example https://arulmouzhi.wordpress.com/2019/11/15/common-alternatives-to-if-else-logic-in-sql-server-with-simple-example/ https://arulmouzhi.wordpress.com/2020/01/08/common-alternatives-to-if-else-logic-in-sql-server-with-simple-example-in-sql-server-part-2/ – Arulmouzhi Jan 17 '20 at 15:49

score 31 · Answer 2 · edited Oct 28 '13 at 18:58

31

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

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

edited Oct 28 '13 at 18:58

jordan

3,436
11
44
75

answered Feb 26 '13 at 09:56

Rohit

10,056
7
50
82

10

This doesn't answer the question. – Kaf Feb 26 '13 at 10:36
While CASE is useful on all version of MS SQL, more recent versions do have an IF ELSE functionality. – Christopher Wade Cantley Nov 28 '16 at 14:50

score 18 · Answer 3 · answered Feb 26 '13 at 10:00

18

Instead of using EXISTS and COUNT just use @@ROWCOUNT:

select product, price from table1 where project = 1

IF @@ROWCOUNT = 0
BEGIN
    select product, price from table1 where customer = 2

    IF @@ROWCOUNT = 0
    select product, price from table1 where company = 3
END

answered Feb 26 '13 at 10:00

Andrey Gordeev

30,606
13
135
162

2

+1 for a good solution – Aleksandr Fedorenko Feb 26 '13 at 11:05
this is not good solution... does not use inherent db set capabilities. i don't think it's correct either where there are multiple different products. actually - i think most knowledgeable in dbs would consider this a poor solution. this is exactly the type of thing that we try to teach people NOT to do. – mson Feb 26 '13 at 22:04
beeing a programmer and not very into dbs, I think this solution looks the most simple to read and understand :) However, I get multiple result sets since it does more than 1 select. Is there any way to only return 1 resut set? Also, does anyone back up @mson on this being a bad idea? – Cotten Feb 28 '13 at 09:29
new question at: http://stackoverflow.com/questions/15132192/can-i-have-multiple-select-but-only-return-one-result-set – Cotten Feb 28 '13 at 09:45
1

this type of syntax is intuitive and is fine for a quick and dirty solution. in dbs it is not optimal because this syntax is procedural - it would be orders of magnitude slower than a proper set based query. – mson Feb 28 '13 at 13:42
This will give a 1-3 result sets. This will be a horror to actually program against. Only useful for as a debugger query or something. – Edwin Stoteler Sep 22 '15 at 13:25

Joachim Isaksson · Answer 4 · 2013-02-26T22:56:23.510

With SQL server you can just use a CTE instead of IF/THEN logic to make it easy to map from your existing queries and change the number of involved queries;

WITH cte AS (
    SELECT product,price,1 a FROM table1 WHERE project=1   UNION ALL
    SELECT product,price,2 a FROM table1 WHERE customer=2  UNION ALL
    SELECT product,price,3 a FROM table1 WHERE company=3
)
SELECT TOP 1 WITH TIES product,price FROM cte ORDER BY a;

An SQLfiddle to test with.

Alternately, you can combine it all into one SELECT to simplify it for the optimizer;

SELECT TOP 1 WITH TIES product,price FROM table1 
WHERE project=1 OR customer=2 OR company=3
ORDER BY CASE WHEN project=1  THEN 1 
              WHEN customer=2 THEN 2
              WHEN company=3  THEN 3 END;

Another SQLfiddle.

TechDo · Answer 5 · 2013-02-26T10:50:28.370

1

Please check whether this helps:

select TOP 1
    product, 
    price 
from 
    table1 
where 
    (project=1 OR Customer=2 OR company=3) AND
    price IS NOT NULL
ORDER BY company

edited Feb 26 '13 at 10:50

answered Feb 26 '13 at 10:05

TechDo

18,398
3
51
64

This will return a result matching _any_ of the conditions without any priority order. – Joachim Isaksson Feb 26 '13 at 21:55

mson · Answer 6 · 2013-02-26T22:01:57.263

0

there is a case statement, but i think the below is more accurate/efficient/easier to read for what you want.

select 
  product
  ,coalesce(t4.price,t2.price, t3.price) as price
from table1 t1
left join table1 t2 on t1.product = t2.product and t2.customer =2
left join table1 t3 on t1.product = t3.product and t3.company =3
left join table1 t4 on t1.product = t4.product and t4.project =1

edited Feb 26 '13 at 22:01

answered Feb 26 '13 at 10:02

mson

7,762
6
40
70

If you don't get a hit in `t1`, this will not return any results. – Joachim Isaksson Feb 26 '13 at 21:54
lol - t1, t2, t3 point to same table (as in poster's requirement) - but i see your point. same solution though – mson Feb 26 '13 at 21:59
http://sqlfiddle.com/#!6/b847b/3, good for testing. – Joachim Isaksson Feb 26 '13 at 22:06

score 0 · Answer 7 · edited Feb 26 '14 at 21:49

--Similar answer as above for the most part. Code included to test

DROP TABLE table1
GO
CREATE TABLE table1 (project int, customer int, company int, product int, price money)
GO
INSERT INTO table1 VALUES (1,0,50, 100, 40),(1,0,20, 200, 55),(1,10,30,300, 75),(2,10,30,300, 75)
GO
SELECT TOP 1 WITH TIES product
        , price
        , CASE WhereFound WHEN 1 THEN 'Project'
                WHEN 2 THEN 'Customer'
                WHEN 3 THEN 'Company'
            ELSE 'No Match'
            END AS Source
FROM 
    (
     SELECT product, price, 1 as WhereFound FROM table1 where project = 11
     UNION ALL
     SELECT product, price, 2 FROM table1 where customer = 0
     UNION ALL
     SELECT product, price, 3 FROM table1 where company = 30
    ) AS tbl
ORDER BY WhereFound ASC

Can you have if-then-else logic in SQL?

7 Answers7

Linked

Related