MySQL: Is it possible to 'fill' a SELECT with values without a table?

Question

I need to display the total of 'orders' for each year and month. But for some months there is no data, but I DO want to display that month (with a total value of zero). I could make a helpertable 'months' with 12 records for each year, but is there maybe a way to get a range of months, without introducing a new table?

Something like:

SELECT [all year-month combinations between january 2000 and march 2011] 
FROM DUAL AS years_months

Does anybody have an idea how to do this? Can you use SELECT with some kind of formula, to 'create' data on the fly?!

UPDATE:

Found this myself: generate days from date range

The accepted answer in this question is kind of what I'm looking for. Maybe not the easiest method, but it does what I want: fill a select with data, based on a formula....

To 'create' a table on the fly with all months of the last 10 years:

SELECT CONCAT(MONTHNAME(datetime), ' ' , YEAR(datetime)) AS YearMonth,
       MONTH(datetime) AS Month,
       YEAR(datetime) AS Year 
FROM (
    select (curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) MONTH) as datetime
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    LIMIT 120
) AS t
ORDER BY datetime ASC 

I must admit, this is VERY exotic, but it DOES work...

I can use this select to join it with my 'orders'-table and get the totals for each month, even when there is no data in a certain month.

But using a 'numbers' or 'calendar' table is probably the best option, so I'm going to use that.

Answer 1

If at all possible, try to stay away from generating data on the fly. It makes very simple queries ridiculusly complex, but above all: it confuses the optimizer to no end.

If you need a series of integers, use a static table of integers. If you need a series of dates, months or whatever, use a calendar table. Unless you are dealing with some truly extraordinary requirements, a static table is the way to go.

I gave an example on how to create a table of numbers and a minimal calendar table(only dates) in this answer.

If you have those tables in place, it becomes easy to solve your query.

1. Aggregate the order data to MONTH.
2. Right join to the table of months (or distinct MONTH from the table of dates)

Answer 2

You could try something like this

select * from 
    (select 2000 as year union
     select 2001 as year union
     select 2009
    ) as years, 
    (select 1 as month union 
     select 2 as month union 
     select 3 as month union 
     select 4 as month union 
     select 5 as month union 
     select 6 as month union 
     select 7 as month union 
     select 8 as month union 
     select 9 as month
     )
    AS months 
    WHERE year between 2001 AND 2008 OR (year=2000 and month>0) OR (year = 2009 AND month < 4) 
    ORDER by year,month