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

November 8, 2021

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;

>Solution :

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.