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

user_1385107 0
Viewed 430

I have 2 tables with the exact same schema. the idea is if table2 is returned null then simply use table1.

CREATE TABLE table1 
(
  name varchar(10)
);

CREATE TABLE table2
(
  name varchar(10)
);

insert into table1
values('abc');

insert into table1
values('xyz');

IF EXISTS (select 1 from table2)
    select * from table2;
else
    select * from table1;
END IF;
snowflake-cloud-data-platform
1 Answers

Snowflake as for today does not support SQL-based procedural logic. The query could be rewritten as:

SELECT *
FROM (SELECT *, 1 AS priority FROM table2
      UNION ALL
      SELECT *, 2 AS priority FROM table1) sub
QUALIFY piority = MIN(priority) OVER();


-- or
SELECT * FROM table2
UNION ALL
SELECT * FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table2);

Assumption: both tables have the same structure.

EDIT:

Using Snowflake scripting it is possible to use branching structures:

declare
    res RESULTSET;
    query VARCHAR;
    tab_name STRING;
begin
    if (EXISTS(select 1 from table2)) then
       tab_name := 'table2';
    else
       tab_name := 'table1';
    end if;
    
    query := 'SELECT * FROM ' || :tab_name;
   
    res := (execute immediate :query);
    return table(res);
end;
user_50708790
Related