mysql 分组统计总数 没有的默认为0

a表：
id room_id
1 room1
2 room2
3 room3
b表
id client_name room_name
1 xiaowang room1
2 xiangzhang room1
3 xiangli room2

统计这三个房间的人数，没有的默认为0

SELECT 
  a.room_id,
  IFNULL(t1.count1, 0) 
FROM
  a 
  LEFT JOIN 
    (SELECT 
      room_name,
      COUNT(*) AS count1 
    FROM
      b 
    GROUP BY room_name) t1 
    ON a.`room_id` = t1.room_name