MySQL内外连接实战详解_Mysql

表的连接分类

表的连接主要分为内连接和外连接两种类型。

内连接

内连接实际上就是利用where子句对两种表形成的笛卡尔积进行筛选，这是我们前面学习的主要查询方式，也是开发过程中使用最多的连接查询。

我们除了可以使用“ , ”来隔离表，我们还可以使用下面的语法：和我们之前笛卡尔积，后面使用where条件筛选是一样的效果的！只不过下面的写法是比较标准的！

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；

前面学习的大部分查询都属于内连接。

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

传统写法：

select ename, dname from emp, dept where emp.deptno=dept.deptno and ename='smith';

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

外连接

外连接分为左外连接和右外连接。

为主的在哪一边，基本就是使用那一边的连接方式！--- 后面的练习部分体现！！！

左外连接

如果联合查询时，左侧的表完全显示（左侧的信息不要做任何的过滤和筛选，完全保留）（即使右侧没有匹配记录），我们就称为左外连接。

仅仅将 inner join 改成了 left join

select 字段名 from 表名1 left join 表名2 on 连接条件

创建测试表：

create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);
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)

查询所有学生的成绩，如果学生没有成绩也要显示个人信息：

select * from stu left join exam on stu.id=exam.id;

mysql> select * from stu left join exam on stu.id=exam.id;
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
|    3 | kity | null |  null |
|    4 | nono | null |  null |
+------+------+------+-------+
4 rows in set (0.00 sec)

这个查询会显示左边表的所有记录，即使右边表没有匹配的记录。

右外连接

如果联合查询时，右侧的表完全显示（即使左侧没有匹配记录），我们就称为右外连接。

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

对stu表和exam表联合查询，显示所有成绩，即使没有对应学生：

select * from stu right join exam on stu.id=exam.id;

mysql> select * from stu right join exam on stu.id=exam.id;
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
| null | null |   11 |     8 |
+------+------+------+-------+
3 rows in set (0.00 sec)

练习：

列出部门名称和这些部门的员工信息，同时列出没有员工的部门

方法一（使用左连接）：

select d.dname, e.* from dept d left join emp e on d.deptno=e.deptno;

mysql> select d.dname, e.* from dept d left join emp e on d.deptno=e.deptno;
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| dname      | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| accounting | 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    null |     10 |
| accounting | 007839 | king   | president | null | 1981-11-17 00:00:00 | 5000.00 |    null |     10 |
| accounting | 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    null |     10 |
| research   | 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    null |     20 |
| research   | 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    null |     20 |
| research   | 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    null |     20 |
| research   | 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    null |     20 |
| research   | 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    null |     20 |
| sales      | 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    null |     30 |
| sales      | 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| sales      | 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    null |     30 |
| sales      | 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| sales      | 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| sales      | 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| operations |   null | null   | null      | null | null                |    null |    null |   null |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
15 rows in set (0.00 sec)

方法二（使用右连接）：

select d.dname, e.* from emp e right join dept d on d.deptno=e.deptno;

mysql> select d.dname, e.* from emp e right join dept d on d.deptno=e.deptno;
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| dname      | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| accounting | 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    null |     10 |
| accounting | 007839 | king   | president | null | 1981-11-17 00:00:00 | 5000.00 |    null |     10 |
| accounting | 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    null |     10 |
| research   | 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    null |     20 |
| research   | 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    null |     20 |
| research   | 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    null |     20 |
| research   | 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    null |     20 |
| research   | 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    null |     20 |
| sales      | 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    null |     30 |
| sales      | 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| sales      | 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    null |     30 |
| sales      | 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| sales      | 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| sales      | 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| operations |   null | null   | null      | null | null                |    null |    null |   null |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
15 rows in set (0.00 sec)