多表连接查询

连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，然后通过连接进行查询。

一、多表连接查询概述

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、什么是笛卡尔积

案例：

查询所有员工和所有部门

1	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 指定

1 2	格式： 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

1 2	格式： 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 可以省略

1 2	格式： select */字段列表 from 左表 left [outer] join 右表 on 条件表达式;

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL

案例：

在部门表中增加一个部门

1 2	mysql> insert into dept(name) values('执行部'); Query OK, 1 rows affected (0.08 秒)

用内连接查询信息

1	mysql> select * from dept d inner join emp e on d.id=e.dept_id;

用左连接查询信息

1	mysql> select * from dept d left join emp e on d.id=e.dept_id;

注意：

左连接表示的是在内连接的基础上保证左表的信息全部显示

3.2、右连接

使用 right outer join…on，outer 可以省略

1 2	格式： select */字段列表 from 左表 right [outer] join 右表 on 条件表达式;

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL

案例：

在员工表中加入一个新员工

1 2	mysql> insert into emp values (null, '王一','男',6666,'2013-12-05',null); Query OK, 1 rows affected (0.02 秒)

用内连接查询信息

1	mysql> select * from dept d inner join emp e on d.id=e.dept_id;

用右连接查询信息

1	mysql> select * from dept d right join emp e on d.id=e.dept_id;

注意：

右连接表示的是在内连接的基础上保证右表的信息全部显示

本文采用署名-非商业性使用-相同方式共享 4.0 国际许可协议，转载请注明出处。