Reputation: 719
similar to Left-Outer Join in Postgres Not Returning Values for Null , but the explanation there isn't improving my situation.
my expectation is that an outer join leaves the specified direction (left or right) intact while putting nulls when the unspecified direction (opposite to the left/right specified) has no match. I have successfully used this in the past on another database, but no longer have access to the sample code.
this is my toy example that works properly:
with complete as (
select generate_series(
'2018-06-15'::date,
'2018-06-17'::date,
'1 day'::interval
)::date as date
), incomplete as (
select *
from (
values('2018-06-15'::date),('2018-06-17'::date)
) as foo(date)
)
select
complete.date as complete
, incomplete.date as incomplete
from complete
left outer join incomplete using(date)
;
result is as expected:
complete | incomplete
------------+------------
2018-06-15 | 2018-06-15
2018-06-16 |
2018-06-17 | 2018-06-17
(3 rows)
now the same thing using real data:
with alldates as (
select
generate_series(
min(datetime::date),
max(datetime::date),
'1 day'::interval
)::date as date
from garagetime
), datadates as (
select distinct datetime::date as date
from garagetime
)
select
alldates.date as alldate
, datadates.date as datadate
from alldates
left outer join datadates using(date)
;
not working as intended:
alldate | datadate
------------+------------
2016-08-10 | 2016-08-10
2016-08-11 | 2016-08-11
2016-08-12 | 2016-08-12 -- skips right over 13..16
2016-08-17 | 2016-08-17 -- skips again
2016-08-22 | 2016-08-22
2016-08-23 | 2016-08-23
...
expected result:
alldate | datadate
------------+------------
2016-08-10 | 2016-08-10
2016-08-11 | 2016-08-11
2016-08-12 | 2016-08-12
2016-08-13 | -- nulls, as expected
2016-08-14 |
2016-08-15 |
2016-08-16 |
2016-08-17 | 2016-08-17
2016-08-18 |
2016-08-19 |
2016-08-20 |
2016-08-21 |
2016-08-22 | 2016-08-22
2016-08-23 | 2016-08-23
...
I tried switching the join left/right, switching the order that the table names are specified in the join (though I'm pretty confident that I know when to use "left" vs "right" based on the result I'm trying to obtain), using "ON alldates.date=datadates.date" instead of "USING(date)", renaming the "AS date" columns to something other than "date".
instead of this:
select
alldates.date as alldate
, datadates.date as datadate
I also tried this:
select
alldates.date as alldate
, case
when alldates.date=datadates.date then alldates.date
else null
end as datadate
and this:
select
alldates.date
, datadates.date
as soon as I drop the join, the query returns all the dates, but in that case I can't see which dates are missing.
Upvotes: 0
Views: 166
Reputation: 1271241
This is too long for a comment. I suspect it is working correctly, but that the results are not in the order that you expect.
Does adding an order by
bring all the dates in the order you want?
select alldates.date as alldate,
datadates.date as datadate
from alldates left outer join
datadates
using(date)
order by alldates.date;
Upvotes: 1