if-else clause in SQL

Question

Is there any way to create query for such request without PL/SQL:

if (select count(column) from table = 0)
then select column1, column2, column 3 from table where condition1
else select column1, column2, column 3 from table where condition2

Are you selecting from the same table called `table` in all three `select`s? — Sergey Kalinichenko, Jul 03 '13 at 10:02

score 1 · Answer 1 · answered Jul 03 '13 at 10:07

1

select column1, column2, column3 
  from table 
 where (condition1 and (select count(column) from table) = 0)
    or (condition2 and (select count(column) from table) != 0)

answered Jul 03 '13 at 10:07

chetan

2,876
1
14
15

1

`exists (select column from table)` is usually more efficient than `(select count(column) from table) = 0` – ypercubeᵀᴹ Jul 03 '13 at 10:10
1

@ypercube That "exists" would always be true if there were any rows in the table at all, regardless of column being null or not. You'd want "exists (select null from table where column is not null)" – David Aldridge Jul 03 '13 at 10:47

score 0 · Answer 2 · answered Jul 03 '13 at 10:04

0

SQL = SELECT QUERY;

if(Column != 0)
{
     SELECT QUERY;
}
if(Column2 != 0)
{
     SELECT QUERY;
}

It should work when you don't add an else statement.

answered Jul 03 '13 at 10:04

Matheno

4,112
6
36
53

According to this thread: http://stackoverflow.com/questions/9870009/if-else-statement-in-sql' – Matheno Jul 03 '13 at 10:05
The OP is asking about re-writing PL/SQL (which is Oracle's SQL extension) in plain SQL. The syntax in your answer is *not* PL/SQL (probably TSQL for SQL server?), and it's certainly not plain SQL. – Frank Schmitt Jul 03 '13 at 10:36

score 0 · Answer 3 · answered Jul 03 '13 at 10:56

You could use two LEFT JOINS and COALESCE (here, I use column1 > 2 / column1 <=2 as condition1 and condition2):

with 
  v1 as (select count(column4) as c4_cnt from table1),
  v_cond1 as (select column1, column2, column3 from table1 where column1 > 2),
  v_cond2 as (select column1, column2, column3 from table1 where column1 <= 2)
select 
  v1.*, 
  coalesce(v_cond1.column1, v_cond2.column1) as column1,  
  coalesce(v_cond1.column2, v_cond2.column2) as column2,
  coalesce(v_cond1.column3, v_cond2.column3) as column3
from v1
left join v_cond1 on v1.c4_cnt = 0
left join v_cond2 on v1.c4_cnt != 0

if-else clause in SQL

3 Answers3