So I have two datasets in a database
DF1
ID value
001 A
002 A
003 B
004 C
And I have this other data set as follows
ID content
002 walter
003 hank
So all I want to do is make a Common Table Expression(CTE) in Snowflake where if an ID in DF1 appears in DF2, then a new variable indicates this (1 or 0)
Final result
ID value id_exist
001 A 0
002 A 1
003 B 1
004 C 0
with DF1 as
(
select * from (values (1, 'A'), (2, 'A'), (3, 'B'), (4, 'C')) as X(ID, VALUE)
),
DF2 as
(
select ID, CONTENT from (values (2, 'Walter'), (3, 'Hank')) as X(ID, CONTENT)
)
select DF1.ID, DF1.VALUE, iff(DF2.ID is not null, 1, 0) as ID_EXISTS from DF1 left join DF2 on DF1.ID = DF2.ID
;
If wanting to use a CTE, you could do this:
with cte1 as
(select 1 as vcount, id
from df2
)
select a.id, a.value, coalesce(b.vcount, 0) as id_exist
from df1 a
left join cte1 b
on b.id = a.id
