answermysql> select student.sname, count(study.cname) from student left join study on (student.sname=study.sname) group by student.sname; +-------+--------------------+ | sname | count(study.cname) | +-------+--------------------+ | A | 2 | | B | 0 | +-------+--------------------+ 2 rows in set (0.00 sec)