【MySQL】内连接，外连接，自连接（详解）

首先，内连接可以用表1 join 表2on 条件 或表1 别名1 join 表2 别名2 on 条件也可以用from表1,表2

我们首先先来创建2张表：

然后我们来理解一下内连接，外连接：

下面是内连接
mysql> select student.name,class.className from student join class on student.classId = class.classId;
+------+-----------+
| name | className |
+------+-----------+
| z1   | c1        |
| z2   | c2        |
| z3   | c3        |
+------+-----------+
3 rows in set (0.00 sec)
mysql> select stu.name,cla.className from student stu join class cla on stu.classId = cla.classId;
+------+-----------+
| name | className |
+------+-----------+
| z1   | c1        |
| z2   | c2        |
| z3   | c3        |
+------+-----------+
3 rows in set (0.00 sec)
下面是左外连接
mysql> select student.name,class.className from student left join class on student.classId = class.classId;
+------+-----------+
| name | className |
+------+-----------+
| z1   | c1        |
| z2   | c2        |
| z3   | c3        |
| z4   | NULL      |
| z5   | NULL      |
+------+-----------+
5 rows in set (0.00 sec)
mysql> select stu.name,cla.className from student stu left join class cla on stu.classId = cla.classId;
+------+-----------+
| name | className |
+------+-----------+
| z1   | c1        |
| z2   | c2        |
| z3   | c3        |
| z4   | NULL      |
| z5   | NULL      |
+------+-----------+
5 rows in set (0.00 sec)
下面是右外连接
mysql> select student.name,class.className from student right join class on student.classId = class.classId;
+------+-----------+
| name | className |
+------+-----------+
| z1   | c1        |
| z2   | c2        |
| z3   | c3        |
| NULL | c99       |
| NULL | c88       |
+------+-----------+
5 rows in set (0.00 sec)
mysql> select stu.name,cla.className from student stu right join class cla on stu.classId = cla.classId;
+------+-----------+
| name | className |
+------+-----------+
| z1   | c1        |
| z2   | c2        |
| z3   | c3        |
| NULL | c99       |
| NULL | c88       |
+------+-----------+
5 rows in set (0.00 sec)

接下来我们再来了解一下自连接，自连接的表是它自己，也就是自己和自己组合：
下面我们来创建一张表:

mysql> select * from studentscore as ss1,studentscore as ss2
    -> where ss1.studentId = ss2.studentId
    -> and ss1.courseId = 1
    -> and ss2.courseId = 3
    -> and ss1.score< ss2.score;
+-----------+-------------+----------+------------+-------+-----------+-------------+----------+------------+-------+
| studentId | studentName | courseId | courseName | score | studentId | studentName | courseId | courseName | score |
+-----------+-------------+----------+------------+-------+-----------+-------------+----------+------------+-------+
|         3 | z3          |        1 | chinese    |    45 |         3 | z3          |        3 | english    |    89 |
+-----------+-------------+----------+------------+-------+-----------+-------------+----------+------------+-------+
1 row in set (0.00 sec)