Join date to date range SQL

I have event on the calendar table as follows

================================================
                     calendar
================================================
events    start          end            country
e1        2021-02-01     2021-02-04     us
e2        2021-02-06     2021-02-07     us
e3        2021-02-03     2021-02-03     uk
================================================

And I have a table daily as follows

===================================
             daily
===================================
date              value
2021-02-01        10
2021-02-02        20
2021-02-03        30
2021-02-04        40
2021-02-05        50
2021-02-06        60
2021-02-07        70
2021-02-08        80
===================================

I would like to join these table to get the daily with the new column of event name base on specific country, for example: I will consider only US event. So the expected output should be the following table.

===================================
         output
===================================
date              value   events
2021-02-01        10      e1
2021-02-02        20      e1
2021-02-03        30      e1
2021-02-04        40      e1
2021-02-05        50
2021-02-06        60      e1
2021-02-07        70      e1
2021-02-08        80
==================================

May I know how to do joining effectively in postgresql?

Answer

i would do it like this:

select d.date, d.value, c.events
from daily d left join
     calendar c
     on (d.date between c.start and c.end and
         c.country = 'us'); 

I recreated your tables (with slight differences in column-names) in a db-fiddle and so you can look up the query there.

Note that i am not an PostgreSQL expert, i just thought i maybe can help you with your problem. 🙂