Bring data from range

Question

I would like to bring even those where there was value in the consultation For example;

table dice

id    |   valor  |   data
 1    |   5      |   01/10/2018
 2    |   6      |   03/10/2018
 3    |   7      |   04/10/2018
 5    |   8      |   05/10/2018

I’d like it when you make an appointment

SELECT * FROM dados WHERE data BETWEEN '01/10/018' AND '06/10/2018'

Should the day come 02/10/2018 and the day 06/10/2018 even without the data

id    |   valor  |   data
 1    |   5      |   01/10/2018
      |   null   |   02/10/2018
 2    |   6      |   03/10/2018
 3    |   7      |   04/10/2018
 5    |   8      |   05/10/2018
      |   null   |   06/10/2018  

Has as?

Answer 1

You need to list the dates on a sub-select and do the LEFT JOIN to list all:

SELECT dados.id, dados.valor, datas.data
FROM (
    SELECT '2018-10-01' AS data
    UNION
    SELECT '2018-10-02' AS data
    UNION
    SELECT '2018-10-03' AS data
    UNION
    SELECT '2018-10-04' AS data
    UNION
    SELECT '2018-10-05' AS data
    UNION
    SELECT '2018-10-06' AS data
) AS datas
LEFT JOIN dados ON dados.data = datas.data

Case your column data table dados is not in format date, you will need to do so:

SELECT dados.id, dados.valor, datas.data
FROM (
    SELECT '01/10/2018' AS data
    UNION
    SELECT '02/10/2018' AS data
    UNION
    SELECT '03/10/2018' AS data
    UNION
    SELECT '04/10/2018' AS data
    UNION
    SELECT '05/10/2018' AS data
    UNION
    SELECT '06/10/2018' AS data
) AS datas
LEFT JOIN dados ON dados.data = datas.data