How to JOIN with missing rows? I need to JOIN two tables to correct the price with exchange rates as

SELECT date, price/rate AS adjusted FROM
table1 a JOIN table2 b ON a.date=b.date


The problem is that table2 does not have the exchange rate for all dates.

How can I get a nearest exchange rate for missing dates in table2?

EXAMPLE

Table 1

date          price
2019-01-01    2.3
2019-01-03    3.4
2019-01-04    2.6
2019-01-05    2.2
2019-01-06    2.5
2019-01-07    5.1
2019-01-08    4.6
2019-01-09    4.2
2019-01-10    3.3


Table 2

date          rate
2019-01-04    1.1
2019-01-06    1.3
2019-01-09    1.0


Alternatively, an approach to fill the missing dates in table2 works too.
To perform the join without matching rows on the dates you need to use an outer join. The challenge here is going to be propagating down the nearest rates as you are missing various rates in table2 both ordered ascending and descending. I don't have much experience with MySQL so I'm not sure if Window Functions are available? I believe this will help you accomplish your goal.

ISNULL(ISNULL(rate, (SELECT TOP (1) rate FROM table2 AS t2 
WHERE a.date &lt;= t2.date)), 
(SELECT TOP (1) rate FROM table2 AS t2 
 WHERE a.date &gt;= t2.date 
 ORDER BY t2.date DESC))

SELECT a.[date],
       b.[date],
       a. price, 
       b.rate, 
       price/rate AS adjusted,
       ISNULL(ISNULL(rate, 
(SELECT TOP (1) rate FROM table2 AS t2 
 WHERE a.date &lt;= t2.date)), 
(SELECT TOP (1) rate FROM table2 AS t2 
 WHERE a.date &gt;= t2.date ORDER BY t2.date DESC))
FROM table1 AS a
LEFT OUTER JOIN table2 AS b
    ON a.[date] = b.[date];