共计 4815 个字符，预计需要花费 13 分钟才能阅读完成。
连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，然后通过连接进行查询。
一、多表连接查询概述
1.1、什么是多表查询
连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。
比如：
有一个部门表，有一个员工表，我想查询某部门中的所有员工的信息。这时我们要先找出部门 ID，通过部门 ID 查询出对应的员工信息。
这样我们在查询我们需要的信息的时候就应用了多表。所以这就是我们的多表查询。
1.2、多表查询的作用
比如：
我们想查询员工 A 的名字和他所在的部门的名字，则需要使用多表查询。
那么我们使用一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。而我们的结果要从每张表取 1 列或多列。这就是多表查询的作用。
1.3、多表查询分类
多表查询可以分为二类查询：
** 内连接：** 隐匿内连接、显示内连接
** 外连接：** 左外连接、右外连接
1.4、笛卡尔积现象
1.4.1、数据准备
创建表和数据
# 部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept (name) values ('研发部'),('渠道部'),('教务部');
# 创建员工表
create table emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), -- 性别
salary double, -- 工资
join_date date, -- 入职日期
dept_id int,
foreign key (dept_id) references dept(id) -- 外键，关联部门表 (部门表的主键)
);
insert into emp(name,gender,salary,join_date,dept_id) values('张三','男',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('李四','男',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('王五','男',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('赵六','女',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('吴七','女',4500,'2011-03-14',1);
显示表中数据
mysql> select * from dept;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 研发部 |
| 2 | 渠道部 |
| 3 | 教务部 |
+----+-----------+
3 行于数据集 (0.01 秒)
mysql> select * from emp;
+----+--------+--------+--------+------------+---------+
| id | name | gender | salary | join_date | dept_id |
+----+--------+--------+--------+------------+---------+
| 1 | 张三 | 男 | 7200 | 2013-02-24 | 1 |
| 2 | 李四 | 男 | 3600 | 2010-12-02 | 2 |
| 3 | 王五 | 男 | 9000 | 2008-08-08 | 2 |
| 4 | 赵六 | 女 | 5000 | 2015-10-07 | 3 |
| 5 | 吴七 | 女 | 4500 | 2011-03-14 | 1 |
+----+--------+--------+--------+------------+---------+
5 行于数据集 (0.01 秒)
1.4.2、什么是笛卡尔积
案例：
查询所有员工和所有部门
mysql> select * from emp,dept;
左表的每条数据和右表的每条数据组合，这种效果就是笛卡尔积
1.4.3、清除笛卡尔积
我们发现笛卡尔积所产生的数据并不是都是有用的，只有员工.dept_id= 部门.id 的值才是我们想要的。
所以我们需要过滤掉没有用的数据。那么如何设置过滤条件呢？
mysql> select * from emp,dept where emp.dept_id=dept.id;
+----+--------+--------+--------+------------+---------+-------+-----------+
| id | name | gender | salary | join_date | dept_id | id(2) | name(2) |
+----+--------+--------+--------+------------+---------+-------+-----------+
| 1 | 张三 | 男 | 7200 | 2013-02-24 | 1 | 1 | 研发部 |
| 5 | 吴七 | 女 | 4500 | 2011-03-14 | 1 | 1 | 研发部 |
| 2 | 李四 | 男 | 3600 | 2010-12-02 | 2 | 2 | 渠道部 |
| 3 | 王五 | 男 | 9000 | 2008-08-08 | 2 | 2 | 渠道部 |
| 4 | 赵六 | 女 | 5000 | 2015-10-07 | 3 | 3 | 教务部 |
+----+--------+--------+--------+------------+---------+-------+-----------+
mysql> select emp.name,dept.name from emp,dept where emp.dept_id=dept.id;
+--------+-----------+
| name | name(2) |
+--------+-----------+
| 张三 | 研发部 |
| 吴七 | 研发部 |
| 李四 | 渠道部 |
| 王五 | 渠道部 |
| 赵六 | 教务部 |
+--------+-----------+
5 行于数据集 (0.01 秒)
二、内连接
用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表. 外键 = 主表. 主键
2.1、隐式内连接
看不到 join 关键字，条件使用 where 指定
格式：select */ 字段列表 from 左表，右表 where 条件表达式;
案例：
查询员工表中所有员工及所在部门
mysql> select * from emp,dept where emp.dept_id=dept.id;
+----+--------+--------+--------+------------+---------+-------+-----------+
| id | name | gender | salary | join_date | dept_id | id(2) | name(2) |
+----+--------+--------+--------+------------+---------+-------+-----------+
| 1 | 张三 | 男 | 7200 | 2013-02-24 | 1 | 1 | 研发部 |
| 5 | 吴七 | 女 | 4500 | 2011-03-14 | 1 | 1 | 研发部 |
| 2 | 李四 | 男 | 3600 | 2010-12-02 | 2 | 2 | 渠道部 |
| 3 | 王五 | 男 | 9000 | 2008-08-08 | 2 | 2 | 渠道部 |
| 4 | 赵六 | 女 | 5000 | 2015-10-07 | 3 | 3 | 教务部 |
+----+--------+--------+--------+------------+---------+-------+-----------+
2.2、显式内连接
使用 inner join…on 语句，可以省略 inner
格式：select */ 字段列表 from 左表 [inner] join 右表 on 条件表达式;
案例：
查询王五的信息，显示员工 id，姓名，性别，工资和所在的部门名称。
mysql> select emp.id,emp.name,emp.gender,emp.salary,dept.name from emp join dept on emp.dept_id=dept.id where emp.name='王五';
+----+--------+--------+--------+-----------+
| id | name | gender | salary | name(2) |
+----+--------+--------+--------+-----------+
| 3 | 王五 | 男 | 9000 | 渠道部 |
+----+--------+--------+--------+-----------+
1 行于数据集 (0.02 秒)
SQL 优化
select emp.id,emp.name,emp.gender,emp.salary,dept.name from emp join dept on emp.dept_id=dept.id where emp.name='王五';
或
select e.id,e.name,e.gender,e.salary,d.name from emp e join dept d on e.dept_id=d.id where e.name='王五';
或
select e.id,e.name,gender,salary,d.name from emp e join dept d on dept_id=d.id where e.name='王五';
2.3、内连接使用步骤
-
确认查询的数据库表
-
确认数据库表连接条件
-
确认数据库表查询条件
-
确认数据库表显示字段
三、左 / 右连接
3.1、左连接
使用 left outer join…on，outer 可以省略
格式：select */ 字段列表 from 左表 left [outer] join 右表 on 条件表达式;
用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL
案例：
在部门表中增加一个部门
mysql> insert into dept(name) values('执行部');
Query OK, 1 rows affected (0.08 秒)
用内连接查询信息
mysql> select * from dept d inner join emp e on d.id=e.dept_id;
用左连接查询信息
mysql> select * from dept d left join emp e on d.id=e.dept_id;
注意：
左连接表示的是在内连接的基础上保证左表的信息全部显示
3.2、右连接
使用 right outer join…on，outer 可以省略
格式：select */ 字段列表 from 左表 right [outer] join 右表 on 条件表达式;
用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL
案例：
在员工表中加入一个新员工
mysql> insert into emp values (null, '王一','男',6666,'2013-12-05',null);
Query OK, 1 rows affected (0.02 秒)
用内连接查询信息
mysql> select * from dept d inner join emp e on d.id=e.dept_id;
用右连接查询信息
mysql> select * from dept d right join emp e on d.id=e.dept_id;
注意：
右连接表示的是在内连接的基础上保证右表的信息全部显示
