Athena query with joins-CodePudding

I am trying to run query on Athena which is not behaving as expected:

select distinct aw.year, aw.month, aw.day
  from aw
  left join f on aw.c1= f.c1
             and aw.c2 = f.c2
             and aw.c3 = f.c3
             and aw.year = '2022'
             and aw.month = '2'
             and aw.day = '5'

I am expecting this query to return 2022, 2, 5 but it is returning several other values of year, month and day. The below query works fine and returns only 2022, 2, 5.

select distinct aw.year, aw.month, aw.day
  from aw
  join f on aw.c1= f.c1
        and aw.c2 = f.c2
        and aw.c3 = f.c3
        and aw.year = '2022'
        and aw.month = '2'
        and aw.day = '5'

The problem is when I add left join but I am also adding the filer of required year, month and day. I am doing something wrong logically here?

CodePudding user response：

You appear to be confusing conditions within an ON with conditions in a WHERE.

Your left join query returned rows where aw had other values because you were providing conditions for how to join the rows between tables. It does not limit the rows from the 'left' table.

If you only want to return rows where the LEFT table (aw) date matches 2022-02-05, then you should put those conditions in a WHERE:

select distinct aw.year, aw.month, aw.day
  from aw
  left join f on aw.c1 = f.c1
             and aw.c2 = f.c2
             and aw.c3 = f.c3
  where aw.year = '2022'
    and aw.month = '2'
    and aw.day = '5'