How to Select Data Between Dates Including Empty Rows in MySQL?

Selecting Data Between Dates with Empty Rows in MySQL

Retrieving data between specified dates from a database is a common task. However, it can become challenging when you also need to include empty rows within the date range. In this article, we will explore a technique to address this problem in MySQL.

Consider a table named "tbl" with rows containing data and dates:

2009-06-25    75
2009-07-01    100
2009-07-02    120

A typical query to select data between two dates, say '2009-06-25' and '2009-07-01', would be:

SELECT data FROM tbl WHERE date BETWEEN '2009-06-25' AND '2009-07-01'

However, this query will miss any empty dates within this range. To include them, MySQL offers a powerful concept known as "calendar tables."

A calendar table is a helper table that contains a series of dates. You can easily create one using a technique demonstrated by this guide:

-- create some infrastructure
CREATE TABLE ints (i INTEGER);
INSERT INTO ints VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

-- only works for 100 days, add more ints joins for more
SELECT cal.date, tbl.data
FROM (
    SELECT '2009-06-25' + INTERVAL a.i * 10 + b.i DAY as date
    FROM ints a JOIN ints b
    ORDER BY a.i * 10 + b.i
) cal LEFT JOIN tbl ON cal.date = tbl.date
WHERE cal.date BETWEEN '2009-06-25' AND '2009-07-01';

This query will return all rows from '2009-06-25' to '2009-07-01', including empty rows with a zero value for 'data'. Note that you can optimize this technique by replacing the subquery with a regularly updated calendar table.

The above is the detailed content of How to Select Data Between Dates Including Empty Rows in MySQL?. For more information, please follow other related articles on the PHP Chinese website!