Oracle SQL find missing dates and populate-CodePudding

I have a date table which includes consecutive dates. Ex:

I have another table that stores some values against the dates.

Ex:

If you can see, 03-03 and 05-05, dates are missing and there could be any date missing.

How can i identify the missing dates and in same query populate those to get the desired result set?

CodePudding user response：

SELECT D.DATE_NOW,X.CURRENT_DATE,X.VALUE
FROM YOUR_FIRST_TABLE D
LEFT JOIN YOUR_SECOND_TABLE X ON D.DATE_NOW=X.CURRENT_DATE

CodePudding user response：

That's outer join:

SQL> with
  2  t1 (date_now) as
  3    (select date '2022-05-01'   level - 1
  4     from dual
  5     connect by level <= 6
  6    ),
  7  t2 (current_date, value) as
  8    (select date '2022-05-01', 'Collaborat' from dual union all
  9     select date '2022-05-02', 'Tentative'  from dual union all
 10     select date '2022-05-04', 'NA'         from dual union all
 11     select date '2022-05-06', 'NA'         from dual
 12    )
 13  select a.date_now, b.value
 14  from t1 a left join t2 b on a.date_now = b.current_date
 15  order by a.date_now;

DATE_NOW   VALUE
---------- ----------
01.05.2022 Collaborat
02.05.2022 Tentative
03.05.2022
04.05.2022 NA
05.05.2022
06.05.2022 NA

6 rows selected.

SQL>