mysql count 0_用mysql group by显示count为0的行

I have two tables in MySql

Company : (cname,city)

works : (ename,cname,salary)

I want to display number of employees working for every company,

even if that number is zero.

for e.g.

for

Company :

Microsoft Bangalore

IBM NY

works :

emp1 Microsoft 10000

emp2 Microsoft 90000

output should be :

Microsoft 2

IBM 0

But the following query and other similar queries print only

those companies which have at least one employee :

Select count(*) from works natural join company group by company.cname

If I use outer join, then the companies with zero employees will still show

up in one row, so that option is out as well.

How to do it?

解决方案

Classic case for a LEFT JOIN:

SELECT

c.cname,

COUNT(w.ename) wcount

FROM

company c

LEFT JOIN works w ON c.cname = w.cname

GROUP BY

c.cname