CODEWITHSUNDEEP
sqlpostgresqlouter-join
violet
violet

Reputation: 719

PSQL left outer join not returning values for unmatched join

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions