0

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:

I Generate_Date
0 2021-01-01
1 2017-01-02

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:

ID Date
ID1 2021-01-01
ID2 2017-01-02

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:

Generated_date Date
2021-05-02 2021-05-02
2021-05-03 NULL

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?

Generated_date Date
2021-05-01 2021-05-01
2021-05-02 2021-05-02
2021-05-04 2021-05-04
2021-05-05 2021-05-05
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
H.Hernandez
  • 23
  • 4
  • Does this answer your question? [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) – philipxy Mar 24 '23 at 19:58
  • Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] Reflect research in posts. – philipxy Mar 24 '23 at 20:14
  • Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Mar 24 '23 at 20:15

1 Answers1

0

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
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Hi @LukaszSzozda - how do you expand this to iterate over many IDs? I noticed if I put the id in the select clause, the ID column pulls back NULLs. That makes sense to me as the date doesn't exist. However, I need to iterate through many ids and see per ID which dates come back NULL. – H.Hernandez Mar 24 '23 at 20:29
  • @H.Hernandez `WHERE id IN ('id1', 'id2', ...)` – Lukasz Szozda Mar 24 '23 at 20:40
  • I understand that part but is there a way to force the id to show in the output even though the record is NULL? – H.Hernandez Mar 24 '23 at 20:47