Snowflake – Left Join not working as expected

I’m looking to reconcile dates between two tables. In other words, I’m looking for where the date column is NULL from the right table.

The first query generates a table with sequence of dates between 2017-2022:

    select -1 + row_number() over(order by 0) i, start_date + i generated_date 
from (select '2017-01-01'::date start_date, '2022-12-31'::date end_date)
join table(generator(rowcount => 10000 )) x
qualify i < 1 + end_date - start_date

Sample of the table below:

The second query generates a tables generates with a date along with other items.

select distinct date
from table 
where i.id = id

Sample of the table below:

I join both queries:

WITH calendar_table as (
select -1 + row_number() over(order by 0) i, start_date + i generated_date 
from (select '2017-01-01'::date start_date, '2022-12-31'::date end_date)
join table(generator(rowcount => 10000 )) x
qualify i < 1 + end_date - start_date)

select distinct t.generated_date, i.date
from calendar_table t 
left join table i on t.date = i.date
where i.id = 'id'
order by t.generated_date desc

I would expect the result to show this:

However, this is what appears. I thought a left join is supposed to bring back everything from the FROM clause. I know that 2021-05-03 exist in the left table. Can I please get suggestions on how to fix this?

>Solution :

When performing LEFT JOIN and later using WHERE with column from right part causes the query to behave as INNER JOIN:

from calendar_table t 
left join table i 
  on t.date = i.date
where i.id = 'id'    -- here

behaves as:

from calendar_table t 
inner join table i 
  on t.date = i.date
where i.id = 'id'    -- here

The i.id = '...' condition should be moved either as subquery or ON clause:

-- a)
from calendar_table t 
left join table i 
  on t.date = i.date
 and i.id = 'id'    

-- b)
from calendar_table t 
left join (SELECT * FROM table WHERE id = 'id') AS i 
  on t.date = i.date