how to write a correlated subquery in snowflake

Question

Can you please help me how to write a correlated sub query in snowflake?

select a
       b,
       (select d.x from d inner join b on d.id=b.id) As x,
       (select d.x from d inner join bon d.id!=b.id) AS Y  
FROM a 
inner join b on a.id=b.id

select X from d table based on join condition.select another column from same table based on another join condition The above query almost my original scenario. can you help me how to write a same query in snowflake

Please provide an example dataset and the result of the query you're expecting. But note that in the SELECT list you can only have subqueries that return EXACTLY ONE ROW each. I'm guessing that's not the case in your query. You can also consider a subquery like SELECT d.x FROM d WHERE d.id=b.id, that might help. But I don't see how this can work for both = and !=. — Marcin Zukowski

Claus Herther Claus Herther · Accepted Answer · 2019-01-07T03:42:23

Correlated subqueries are generally a bad idea, since in many cases they result in one query per row, which won't scale. If I'm reading your query correctly though, you could simply join d twice with different join conditions to get x and y.

select
    a.*,
    b.*,
    d1.x as x,
    d2.x as y
from
    a
    join
    b on a.id = b.id
    join
    d as d1 on d.id = b.id
    join
    d as d2 on d.id <> b.id

how to write a correlated subquery in snowflake

1 Answers