Select query to results all 12 months even if data not exist

[jazzyb]

I have table names 'payroll' with following data

month , pay
January , 1200
March , 1500
December , 2000

I want the following result

Janury , 1200
February , 00
March , 1500
April , 00
May , 00
June , 00
July , 00
August , 00
September , 00
October , 00
November , 00
December , 00

Please help to make the query.

Thanks in advance

[smantscheff]

Build a reference table with all month names and left join it to your data table.

[drjohn]

nearly right

it's a left outer join

SELECT months.month, IFNULL( payroll.pay, 0 ) AS pay
FROM months
LEFT OUTER JOIN payroll ON payroll.month = months.month

[smantscheff]

Please explain the difference between LEFT JOIN and LEFT OUTER JOIN in MySQL. To the best of my knowledge there isn't any.

[d5e5]

Please explain the difference between LEFT JOIN and LEFT OUTER JOIN in MySQL. To the best of my knowledge there isn't any.

I can't find a clear explanation to this in docs but this question was asked on StackOverflow and the accepted answer was that it makes no difference which you use.

At another site someone wrote the following: "MySql only supports the LEFT OUTER JOIN syntax so as to support ODBC compliance."

[mwasif]

At another site someone wrote the following: "MySql only supports the LEFT OUTER JOIN syntax so as to support ODBC compliance."

Right

[d5e5]

You could use the following query as an alternative to creating a table of months.

SELECT m.month, p.pay
FROM (
SELECT 'January' AS
MONTH
UNION SELECT 'February' AS
MONTH
UNION SELECT 'March' AS
MONTH
UNION SELECT 'April' AS
MONTH
UNION SELECT 'May' AS
MONTH
UNION SELECT 'June' AS
MONTH
UNION SELECT 'July' AS
MONTH
UNION SELECT 'August' AS
MONTH
UNION SELECT 'September' AS
MONTH
UNION SELECT 'October' AS
MONTH
UNION SELECT 'November' AS
MONTH
UNION SELECT 'December' AS
MONTH
) AS m
LEFT JOIN payroll p ON m.month = p.month

[drjohn]

Please explain the difference between LEFT JOIN and LEFT OUTER JOIN in MySQL. To the best of my knowledge there isn't any.

Sorry, my mistake. I just had it drummed into me to say left outer join and forgot that left join was identical.