MYSQL内外连接

1. 内连接

内连接实际上就是利用 where 子句对两张表形成的笛卡尔积进行筛选。我们前面学习的所有多表查询（from 表1, 表2 where 条件）本质上都是内连接，这也是开发过程中使用最多的连接查询。

语法：SELECT 字段 FROM 表1 INNER JOIN 表2 ON 连接条件 [WHERE 其他条件];

INNER 关键字可以省略，直接写 JOIN 即可。

案例：显示 smith 的名字和部门名称

按照之前的写法（隐式内连接）：select ename, dname from emp, dept where emp.deptno=dept.deptno and ename=’smith’;

mysql> select ename, dname from emp, dept where emp.deptno=dept.deptno and ename='smith';
+-------+-----------+
| ename | dname     |
+-------+-----------+
| smith | research  |
+-------+-----------+
1 row in set (0.00 sec)

标准内连接写法（显示内连接）：select ename, dname from emp inner join dept on emp.deptno=dept.deptno and ename=’smith’;

mysql> select ename, dname from emp inner join dept on emp.deptno=dept.deptno and ename='smith';
+-------+----------+
| ename | dname    |
+-------+----------+
| smith | research |
+-------+----------+
1 row in set (0.00 sec)

两种写法在结果上是完全一样的。显式内连接使用 ON 关键字来指定连接条件，结构更清晰。

2. 外连接

外连接分为左外连接和右外连接。它们的区别在于：以哪张表为"主表"，主表的数据会全部显示，即使另一张表中没有匹配的记录。

为了演示外连接，先创建两张测试表：学生表和成绩表。

mysql> create table stu (id int, name varchar(30));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create table exam (id int, grade int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into exam values(1, 56),(2,76),(11, 8);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from stu;
+------+------+
| id   | name |
+------+------+
|    1 | jack |
|    2 | tom  |
|    3 | kity |
|    4 | nono |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from exam;
+------+-------+
| id   | grade |
+------+-------+
|    1 |    56 |
|    2 |    76 |
|   11 |     8 |
+------+-------+
3 rows in set (0.00 sec)

观察两张表的数据：stu 表有 id 为 1、2、3、4 的学生；exam 表有 id 为 1、2、11 的成绩；学生 kity（id=3）和 nono（id=4）没有成绩；成绩表中 id=11 没有对应的学生信息。

2.1 左外连接

左外连接的定义：如果联合查询时，左侧的表完全显示，我们就说是左外连接。

语法：select 字段名 from 表名1 left join 表名2 on 连接条件;

案例：查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

使用内连接查询：select stu.id, grade from stu inner join exam on stu.id=exam.id;

mysql> select stu.id, grade from stu inner join exam on stu.id=exam.id;
+------+-------+
| id   | grade |
+------+-------+
|    1 |    56 |
|    2 |    76 |
+------+-------+
2 rows in set (0.00 sec)

结果只显示有成绩的学生（id=1 和 id=2），id=3、4 的学生没有成绩就不会出现。

使用左外连接查询：select stu.id, grade from stu left join exam on stu.id=exam.id;

mysql> select stu.id, grade from stu left join exam on stu.id=exam.id;
+------+-------+
| id   | grade |
+------+-------+
|    1 |    56 |
|    2 |    76 |
|    3 |  NULL |
|    4 |  NULL |
+------+-------+
4 rows in set (0.00 sec)

结果会显示所有学生：有成绩的显示成绩，没有成绩的 grade 显示为 NULL。

左外连接以左表（stu）为主，左表的所有记录都会出现。当左表和右表没有匹配时，右表的字段填充为 NULL。

2.2 右外连接

右外连接的定义：如果联合查询，右侧的表完全显示我们就说是右外连接。

语法：select 字段 from 表名1 right join 表名2  on 连接条件；

案例：把所有的成绩都显示出来，即使这个成绩没有学生与它对应

使用右外连接查询：select stu.id, grade from stu right join exam on stu.id=exam.id;

mysql> select stu.id, grade from stu right join exam on stu.id=exam.id;
+------+-------+
| id   | grade |
+------+-------+
|    1 |    56 |
|    2 |    76 |
| NULL |     8 |
+------+-------+
3 rows in set (0.00 sec)

结果会显示所有成绩记录：有对应学生的成绩，显示学生信息；没有对应学生的成绩（id=11），学生信息显示为 NULL

右外连接以右表（exam）为主，右表的所有记录都会出现。当右表和左表没有匹配时，左表的字段填充为 NULL。

2.3 总结

左外连接和右外连接本质上是可以互相转换的：A LEFT JOIN B ON 条件  <=>  B RIGHT JOIN A ON 条件。因此，在实际开发中，通常只使用左外连接，通过调整表的顺序来满足需求。

外连接综合案例：列出部门名称和这些部门的员工信息，同时列出没有员工的部门

在emp表中，只有10，20，30部门存在员工，40号部门没有员工。

使用左外连接（以 dept 表为主）：select * from dept left join emp on dept.deptno=emp.deptno;

mysql> select dname, ename, dept.deptno from dept left join emp on dept.deptno=emp.deptno;
+------------+--------+--------+
| dname      | ename  | deptno |
+------------+--------+--------+
| accounting  | clark  |     10 |
| accounting  | king   |     10 |
| accounting  | miller |     10 |
| research    | smith  |     20 |
| research    | jones  |     20 |
| research    | scott  |     20 |
| research    | adams  |     20 |
| research    | ford   |     20 |
| sales       | allen  |     30 |
| sales       | ward   |     30 |
| sales       | martin |     30 |
| sales       | blake  |     30 |
| sales       | turner |     30 |
| sales       | james  |     30 |
| operations  | NULL   |     40 |
+------------+--------+--------+
15 rows in set (0.00 sec)

结果会显示所有部门：有员工的部门显示员工信息，没有员工的部门（40号）员工信息显示为 NULL。