Conditional Select in PostgreSQL without if-else statement

Question

I have the following table:

|--------|----------------|-------------|
|  url   |   description  |  for_region |
|--------|----------------|------------ |
| url1   |   desc1        |  All        |
| url2   |   desc2        |  All        |
| url2   |   desc3        | Germany     |
|--------|----------------|-------------|

Now I am trying to write the following query without if else statements:

IF EXISTS (SELECT 1 FROM my_table where for_country='Germany') THEN
   select * from my_table where for_country='Germany'
ELSE 
   select * from my_table where for_country='All'
END IF;

What is the best way to rewrite the above query without the use of if-else statements?

Radim Bača · Accepted Answer · 2018-08-09 08:45:52Z

2

You can add EXISTS into the WHERE clause

select * 
from my_table 
where (EXISTS (select 1 from my_table where for_country='Germany') and for_country='Germany') OR
      (NOT EXISTS (select 1 from my_table where for_country='Germany') and for_country='All')

DBFiddle DEMO

and a probably better solution is to use EXISTS and CROSS JOIN to avoid the duplicate call of the same subquery

select my_table.* 
from my_table 
cross join (
  select exists(select 1 from my_table where for_country='Germany') exst
) t
where (t.exst and for_country='Germany') OR
      (not t.exst and for_country='All')

DBFiddle DEMO

edited Aug 9, 2018 at 8:45

answered Aug 9, 2018 at 8:38

Radim Bača

10.7k1 gold badge21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ratan Uday Kumar Over a year ago

please can you have a look at this question stackoverflow.com/q/51758821/6633337

Fahmi · Accepted Answer · 2018-08-09 08:21:52Z

0

Try below query:

select m1.name from
   (
    select m1.*, case when m1.for_region='Germany' then 1  else 0  end  as cnt from tableA m1 ) m1
     inner join 
    (
    select max(Cnt) as Cnt from
    (
     select t1.*, case when for_region='Germany' then 1  else 0  end  as Cnt 
      from tableA t1
     ) as t2

     )as n 
     on m1.cnt=n.Cnt

answered Aug 9, 2018 at 8:21

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

1 Comment

Ratan Uday Kumar Over a year ago

please can have a look at this question stackoverflow.com/q/51758821/6633337

Piotr Rogowski · Accepted Answer · 2018-08-09 08:23:19Z

0

select * from my_table 
where 
((SELECT DISTINCT 1 FROM my_table where for_country='Germany') = 1 AND for_country='Germany')
OR for_country='All'

answered Aug 9, 2018 at 8:23

Piotr Rogowski

3,90021 silver badges24 bronze badges

1 Comment

Ratan Uday Kumar Over a year ago

please can you have a look at this question stackoverflow.com/q/51758821/6633337

Yogesh Sharma · Accepted Answer · 2018-08-09 09:16:18Z

0

I would use UNION with NOT EXISTS :

SELECT * 
FROM my_table 
WHERE for_country = 'Germany'
UNION ALL
SELECT *
FROM my_table
WHERE for_country = 'All' AND
      NOT EXISTS (SELECT 1 FROM my_table WHERE for_country = 'Germany');

answered Aug 9, 2018 at 9:16

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

1 Comment

Ratan Uday Kumar Over a year ago

please can you have a look at this question stackoverflow.com/q/51758821/6633337

Gordon Linoff · Accepted Answer · 2018-08-09 11:16:29Z

0

I would write this as:

select t.*
from my_table t.
where (for_country = 'Germany') or
      (not exists (select 1 from my_table where for_country = 'Germany') and
       for_country = 'All'
      );

answered Aug 9, 2018 at 11:16

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Ratan Uday Kumar Over a year ago

please can you have a look at this question stackoverflow.com/q/51758821/6633337

Collectives™ on Stack Overflow

Conditional Select in PostgreSQL without if-else statement

5 Answers 5

1 Comment

1 Comment

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related