mysql - 如何从总记录中获取不存在的记录为空？

Question

表 1:tbl_users:

+----+--------+
| id | name   |
+----+--------+
| 1  | waheed |
+----+--------+
| 2  | fareed |
+----+--------+

表 2:tbl_watched:

+------------+----+--------+
| id_watched | id | name   |
+------------+----+--------+
| 1          | 2  | fareed |
+------------+----+--------+

如果 tbl_watched 不存在，我想获取总记录，它应该返回为 zero 或 null

输出:

+----+--------+--------+
| id | name   |watched |
+----+--------+--------+
| 1  | waheed |  90    |
+----+--------+--------+
| 2  | fareed |  null  |
+----+--------+--------+

我怎样才能得到这样的结果？

Answer 1

使用左连接

Select t1.id , t1.name, count(*) as total from tbl_users as t1 
left join tbl_watched as t2 on t1.id=t2.id_watched
group by t1.id , t1.name