Conditional logic based on two datasets in SQL/Snowflake-CodePudding

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

CodePudding user response：

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
;

CodePudding user response：

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