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

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

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

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

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."

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

Right

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
commented: What a nice trick! +1

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.