MySQL总结(十)表连接查询(内、外连接)-详解

╰+攻爆jí腚メ 2023-02-24 11:21 4阅读 0赞

[精选30+云产品，助力企业轻松上云！>>> ![hot3.png][]][30_ _hot3.png]

![up-a344f41e81d3568e3310b5da00c57ced8ea.png][]

# 表连接查询 #

## 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);

*  多表查询的作用：
    
     *  比如：我们想查询孙悟空的名字和他所在的部门的名字，则需要使用多表查询。
     *  如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。每张表取 1 列或多列。

### 多表查询的分类： ###

![up-b694f4247cd915d3f1747b07b8217bb005b.png][]

## 2. 笛卡尔积现象 ##

### 2.1 什么是笛卡尔积现象 ###

-- 需求：查询所有的员工和所有的部门
    select * from emp,dept;

![up-21905c5d45ddaf7e73536a9747b8cf9e159.png][]

### 2.2 如何清除笛卡尔积现象的影响 ###

我们发现不是所有的数据组合都是有用的，只有 员工表.dept\_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。

-- 设置过滤条件 Column 'id' in where clause is ambiguous
    select * from emp,dept where id=5;
    select * from emp,dept where emp.`dept_id` = dept.`id`;
    -- 查询员工和部门的名字
    select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

## 3. 内连接 ##

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键

### 3.1 隐私内连接 ###

看不到 JOIN 关键字，条件使用 WHERE 指定

-- SELECT 字段名 FROM 左表, 右表 WHERE 条件
    select * from emp,dept where emp.`dept_id` = dept.`id`;

![up-770c2a70227600c7562bd6007335eb8593e.png][]

### 3.2 显示内连接 ###

使用 INNER JOIN ... ON 语句, 可以省略 INNER

-- SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称，我们发现需要联合 2 张表同时才能查询出需要的数据，使用内连接。

![up-4a09f064ab64cb96aa5336c5a60da8e367b.png][]

*  1）确实查询哪些表

select * from emp inner join dept;

![up-b81c7a367b8b2fc22d7d2b42a84164307a2.png][]

*  2）确定表连接条件，员工表.dept\_id = 部门表.id 的数据才是有效的

select * from emp e inner join dept d on e.`dept_id` = d.`id`;

![up-f294987b8b5375702953812411505fa9d89.png][]

*  3）确定查询条件，我们查询的是唐僧的信息，员工表.name='唐僧'

select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

![up-9454c5d70a8b91b6131fa015acae8cd1b93.png][]

*  4）确定查询字段，查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称

select e.`id`,e.`name`,e.`gender`,e.`salary`,d.`name` from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

![up-dba0ad3fb86e3466e2b256a21a372ff0a9c.png][]

*  5）我们发现写表名有点长，可以给表取别名，显示的字段名也使用别名

select e.`id` 编号,e.`name` 姓名,e.`gender` 性别,e.`salary` 工资,d.`name` 部门名字 from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

![up-618ac2ae95af65a2d1ab72abf9428f45f1f.png][]

### 3.3 总结内连接查询步骤： ###

*  1)确定查询哪些表
 *  2)确定表连接的条件
 *  3)确定查询的条件
 *  4)确定查询的字段

## 4. 左外连接 ##

使用 LEFT OUTER JOIN ... ON，OUTER 可以省略

-- SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

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

可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)

-- 在部门表中增加一个销售部
    insert into dept (name) values ('销售部');
    select * from dept;
    -- 使用内连接查询
    select * from dept d inner join emp e on d.`id` = e.`dept_id`;
    -- 使用左外连接查询
    select * from dept d left join emp e on d.`id` = e.`dept_id`;

![up-d6c6b414f6b86543a0caf37f96b46910106.png][]

## 5. 右外连接 ##

使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略

-- SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

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

可以理解为：在内连接的基础上保证右表的数据全部显示

-- 在员工表中增加一个员工
    insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);
    select * from emp;

![up-0a223ce080fee613dac3648976a44243ac9.png][]

-- 使用内连接查询
    select * from dept inner join emp on dept.`id` = emp.`dept_id`;
    -- 使用右外连接查询
    select * from dept right join emp on dept.`id` = emp.`dept_id`;

![up-e0a5b3d3ffd519bdb3c93d82366d158a6d7.png][]

# OK,这篇就到这里 #

[hot3.png]: /images/20230209/bd972fd2316a46d3a3ead29b4f1306d5.png
[30_ _hot3.png]: https://www.oschina.net/action/visit/ad?id=1221
[up-a344f41e81d3568e3310b5da00c57ced8ea.png]: https://oscimg.oschina.net/oscnet/up-a344f41e81d3568e3310b5da00c57ced8ea.png
[up-b694f4247cd915d3f1747b07b8217bb005b.png]: https://oscimg.oschina.net/oscnet/up-b694f4247cd915d3f1747b07b8217bb005b.png
[up-21905c5d45ddaf7e73536a9747b8cf9e159.png]: https://oscimg.oschina.net/oscnet/up-21905c5d45ddaf7e73536a9747b8cf9e159.png
[up-770c2a70227600c7562bd6007335eb8593e.png]: https://oscimg.oschina.net/oscnet/up-770c2a70227600c7562bd6007335eb8593e.png
[up-4a09f064ab64cb96aa5336c5a60da8e367b.png]: https://oscimg.oschina.net/oscnet/up-4a09f064ab64cb96aa5336c5a60da8e367b.png
[up-b81c7a367b8b2fc22d7d2b42a84164307a2.png]: https://oscimg.oschina.net/oscnet/up-b81c7a367b8b2fc22d7d2b42a84164307a2.png
[up-f294987b8b5375702953812411505fa9d89.png]: https://oscimg.oschina.net/oscnet/up-f294987b8b5375702953812411505fa9d89.png
[up-9454c5d70a8b91b6131fa015acae8cd1b93.png]: https://oscimg.oschina.net/oscnet/up-9454c5d70a8b91b6131fa015acae8cd1b93.png
[up-dba0ad3fb86e3466e2b256a21a372ff0a9c.png]: https://oscimg.oschina.net/oscnet/up-dba0ad3fb86e3466e2b256a21a372ff0a9c.png
[up-618ac2ae95af65a2d1ab72abf9428f45f1f.png]: https://oscimg.oschina.net/oscnet/up-618ac2ae95af65a2d1ab72abf9428f45f1f.png
[up-d6c6b414f6b86543a0caf37f96b46910106.png]: https://oscimg.oschina.net/oscnet/up-d6c6b414f6b86543a0caf37f96b46910106.png
[up-0a223ce080fee613dac3648976a44243ac9.png]: https://oscimg.oschina.net/oscnet/up-0a223ce080fee613dac3648976a44243ac9.png
[up-e0a5b3d3ffd519bdb3c93d82366d158a6d7.png]: https://oscimg.oschina.net/oscnet/up-e0a5b3d3ffd519bdb3c93d82366d158a6d7.png