一道sql题引起的对于内连接、外连接等的学习

题目：

现有两个表，分别是学生表t_stu，还有s_id学号，s_name学生姓名和c_id班级号三个字段；还有一张表是班级表，有c_id班级号，c_name班级名两个字段。那么要求通过sql语句能够查询到班级名字和各自班级的人数。

解决方案：

此题一拿到，我就想到通过两个表的连接来实现查询，具体到连接有内连接和外连接之分，于是乎，需要研究到底是应用内连接还是外连接。

内连接(inner join)：用于将两张表中相同字段的内容进行连接，并整合成一张表。因为此题中，第二张班级表的c_id包含了所有的表一的所有学生所对应的班级编号，所以可以通过c_id字段来实现内连接。那么可以采用如下语句来实现内连接：

(select * from t_stu inner join t_class on t_stu.c_id = t_class.c_id);

显示：

+------+--------+------+------+---------+
| s_id | s_name | c_id | c_id | c_name  |
+------+--------+------+------+---------+
|    1 | tom    |    1 |    1 | class 1 |
|    2 | mary   |    1 |    1 | class 1 |
|    3 | green  |    2 |    2 | class 2 |

这是内连接的结果。然后需要找出相关的每个班级的人数和班级名，那么用group by按照班级号来划分，再重新计算相关的每个班级的人数。

select t.c_name,count(t.s_id) from (select t1.s_id,t1.c_id,t2.c_name from t_stu t1 inner join t_class t2 on t1.c_id = t2.c_id) as t group by t.c_id;

上述代码执行后的结果如下：

+---------+---------------+
| c_name  | count(t.s_id) |
+---------+---------------+
| class 1 |             2 |
| class 2 |             1 |
+---------+---------------+
2 rows in set (0.00 sec)

这是对内连接在两张具有某一列相同元素之间的应用。

上述sql代码没有考虑一种情况，即如果班级表中出现了某些在学生表中没有学生所在的班级的班级，那么这些班级所在的班级人数就显示为0。所以需要涵盖这种边缘情况，方便进行处理。

因此首先得将inner join改为right join，以班级表为base，按照班级表所有的元素为准去匹配相关的学生表。现在的学生表为：

+------+--------+------+
| s_id | s_name | c_id |
+------+--------+------+
|    1 | tom    |    1 |
|    2 | mary   |    1 |
|    3 | green  |    2 |
|    4 | bill   |    4 |
+------+--------+------+

一共有4条记录，最右侧一列表述班级号，这些学生分布在1，2，4班，没有3班的学生。然后是班级表：

+------+---------+
| c_id | c_name  |
+------+---------+
|    1 | class 1 |
|    2 | class 2 |
|    3 | class 3 |
|    4 | class 4 |
+------+---------+

班级表一共有4个班级，分别为1，2，3，4班。

那么现在还是要显示每个班级的班级名和所在班级的学生个数。那么首先用right join右连接，执行的语句为：

select * from t_stu t1 right join t_class t2 on t1.c_id = t2.c_id;

+------+--------+------+------+---------+
| s_id | s_name | c_id | c_id | c_name  |
+------+--------+------+------+---------+
|    1 | tom    |    1 |    1 | class 1 |
|    2 | mary   |    1 |    1 | class 1 |
|    3 | green  |    2 |    2 | class 2 |
|    4 | bill   |    4 |    4 | class 4 |
| NULL | NULL   | NULL |    3 | class 3 |

因为在学生表中，有些数据没有对应所有的班级号，所以用null来代替了，但是因为原来的学生表中，学号默认是不能为null的，所以我用-1来代替null，为了后续group by能够使用。

故执行如下语句：

select ifnull((t.s_id),-1) s_id,t.c_id,t.c_name from (select t1.s_id,t2.c_id,t2.c_name from t_stu t1 right join t_class t2 on t1.c_id = t2.c_id) as t;

显示结果为：

+------+------+---------+
| s_id | c_id | c_name  |
+------+------+---------+
|    1 |    1 | class 1 |
|    2 |    1 | class 1 |
|    3 |    2 | class 2 |
|    4 |    4 | class 4 |
|   -1 |    3 | class 3 |
+------+------+---------+

注意这里用了一个ifnull的函数，也就是通过判断如果为null，那么就原来的那个值置为-1。ifnull(t1,t2)，即为判断t1是否为null，如果为null，那么就将其置为-1。

然后可以对新生成的t表，进行group by操作，以c_id为分类进行计算。但是在计算count时候，应该要将-1的值直接置为0，而不用再纳入到计算班级人数的范畴中去，因此在count函数中，我们需要if语句来进行判断匹配。其中count(null)为0，而count中插入if语句的格式如下所示：

count(if(t1,t2,t3))，其中t1为判断语句，如果t1的值为true,则执行t2；如果t1的值为false，则执行t3。具体的结果如下：

select tt.c_name,count(if(tt.s_id > 0,tt.s_id,null)) as count from (select ifnull((t.s_id),-1) s_id,t.c_id,t.c_name from (select t1.s_id,t2.c_id,t2.c_name from t_stu t1 right join t_class t2 on t1.c_id = t2.c_id) as t) as tt group by tt.c_id;

具体的执行结果如下：

+---------+-------+
| c_name  | count |
+---------+-------+
| class 1 |     2 |
| class 2 |     1 |
| class 3 |     0 |
| class 4 |     1 |
+---------+-------+

这样就显示完成，也考虑了各种情况，尤其是针对边缘情况进行讨论。