问一个关于统计的问题

[abz867]

tableA
Id food
1 苹果
2 苹果
3 西瓜


tableC
foodId foodName
1 苹果
2 西瓜
3 香蕉

求一条sql语句，能统计出这样一个效果

food 数量
苹果 2
西瓜 1
香蕉 0

[SQL77]

select food,count(1) rn from (select * from ta union all select * from tb) t group by food

[--小F--]

select
  a.foodname,isnull(b.num,0)
from
  tableC as a
left join
  (select food ,count(1) as num from tablea group by food)b
on
   a.foodName=b.food 

[叶子]

declare @tableA table (Id int,food varchar(4))
insert into @tableA
select 1,'苹果' union all
select 2,'苹果' union all
select 3,'西瓜'

declare @tableC table (foodId int,foodName varchar(4))
insert into @tableC
select 1,'苹果' union all
select 2,'西瓜' union all
select 3,'香蕉'

select foodName,
(select count(1) from @tableA where food=c.foodName) as 数量 from @tableC c
/*
foodName 数量
-------- -----------
苹果       2
西瓜       1
香蕉       0
*/