using if condition in snowflake

Question

I have two tables as shown below with columns:

Table A
a, b, c, id 

Table B 
d, e, f, g, h, id

Now I need perform a query basically I will get a id from user, so I need to check if that id is present in table A or table B. So the record will be present in any of one table

SELECT * FROM tableA WHERE id = 123 
OR 
SELECT * FROM tableB WHERE id = 123

So the response will be either columns of tableA or columns of tableB. But I can't perform a union since the columns should be equal among two tables.

So it's basically a if condition, how can I get the desired output in Snowflake.

And using if is the best optimized approach or any other way is there

A SQL query must return a fixed set of columns. If you want all columns from the table where the id is found, you cannot do this in a single query. — GMB
but if i give select * which means its should return all isn't — Learner
I remvoved the postgreql tag, because Snowflake is a completely different DBMS product than Postgres. — a_horse_with_no_name
@GMB i didn't understood clearly, can you explain how can i approach this one since i am a beginner to db — Learner

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-02-11T12:36:09

You can use union all -- assuming the types are compatible. Just pad the smaller table:

select a, b, c, null as g, null as h, id 
from a
where id = 123
union all
select d, e, f, g, h, id 
from b
where id = 123;

If you want the columns separated, then a full join accomplishes that:

select *
from a full join
     b
     using (id)
where a.id = 123 or b.id = 123;

using if condition in snowflake

1 Answers