有3个表，一个放学生名字，一个放学科名字，一个放学生学的课。
student(sname)
course(cname)
study(sname,cname)
数据是这样的
student: {(A), (B)}
course: {(CS1101), (CS1102)}
study: {(A, CS1101), (B, CS1102)}

现在我想知道每个学生学了几门课(自我感觉这是个很普通的目的，一点也不刁钻)，我希望得到的答案是{(A, 2), (B, 0)}
于是我这样
select student.sname, count(study.cname) from student, study where student.sname=study.sname group by student.sname
但得出的结果是{(A, 2)}。没有B，因为B没有学任何课。
有没有什么办法达到这个目的?不能有嵌入查询，最好不要创建临时表。


下面是我在mysql上的实验结果。
mysql> create table student (sname char(10) primary key);
Query OK, 0 rows affected (0.08 sec)

mysql> create table course (cname char(10) primary key);
Query OK, 0 rows affected (0.05 sec)

mysql> create table study (sname char(10), cname char(10));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into student values ('A'), ('B');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into course values ('CS1101'), ('CS1102');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into study (sname, cname) values ('A', 'CS1101'), ('A', 'CS1102');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select student.sname, count(study.cname) from student, study where student.sname=study.sname group by student.sname;
+-------+--------------------+
| sname | count(study.cname) |
+-------+--------------------+
| A | 2 |
+-------+--------------------+
1 row in set (0.00 sec)

mysql> 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)