How to merge two tables by date?

Question

SQL

Hello, there are 2 tables
time_dimension
id
db_date
client
id
date_create
delete
name
......
I want to get the number of records from the clients table for each day, there can be several or 0. The time_dimension table stores all dates for 10 years.
I managed to get this list with the following query:

SELECT 
  time_dimension.db_date, COUNT(clients.date_create)
FROM
  time_dimension
LEFT JOIN 
  clients
ON
  time_dimension.db_date = DATE_FORMAT(clients.date_create,"%Y-%m-%d")
WHERE 
  time_dimension.db_date BETWEEN '2019-12-01' AND '2019-12-30'
GROUP BY
  time_dimension.db_date
ORDER BY
  time_dimension.db_date

but if I add clients.`delete`='0' in while I get records only for the days when there were new clients, and all days for the period are necessary

Answer 1

Direct variant - full join
Only whether it is necessary to receive a cloth from ~3650 lines where quantities different from zero will occasionally flicker here and there?
ps judging by the structure of time_dimension - it was done approximately for this
pps I strongly suspect that all this should then fall into some graphs of the dynamics of the client base, it is better to feed raw data there

Answer 2

If d-stream is right and you need a calendar report, for example:
Then the following variant of its construction is suggested: q628559 .

Answer 3

As I understand it, when you wrote while. you meant WHERE. Then you need to move the clients.`delete`='0' clause from the WHERE clause to the ON clause.

SELECT 
  time_dimension.db_date, COUNT(clients.date_create)
FROM
  time_dimension
LEFT JOIN 
  clients
ON
  time_dimension.db_date = DATE_FORMAT(clients.date_create,"%Y-%m-%d")
  AND clients.`delete`='0'
WHERE 
  time_dimension.db_date BETWEEN '2019-12-01' AND '2019-12-30'
GROUP BY
  time_dimension.db_date
ORDER BY
  time_dimension.db_date