select 高级查询之连接查询

￡神魔★判官ぃ 2023-02-22 10:36 6阅读 0赞

![在这里插入图片描述][2020070220255628.jpg]

## 1.1 笛卡尔积 ##

### 1.1.1 概述 ###

笛卡尔乘积是指在数学中，两个集合 X 和 Y 的笛卡尔积(Cartesian product)，又称直积，表示为 X×Y，第一个对象是 X 的成员而第二个对象是 Y 的所有可能有序对的其中一个成员。假设集合 A=\{a, b\}，集合 B=\{0, 1, 2\}，则两个集合的笛卡尔积为\{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)\}。  
![在这里插入图片描述][20200702203410251.png]

### 1.1.2 数据准备 ###

mysql> select * from dept;
    +---------+-----------+
    | dept_id | dept_name |
    +---------+-----------+
    |       1 | 技术部    |
    |       2 | NULL      |
    |    NULL | 财务部    |
    |       4 | 办公室    |
    |       5 | 运营部    |
    |       6 | NULL      |
    +---------+-----------+
    6 rows in set (0.06 sec)
    
    mysql> select * from emp;
    +--------+----------+
    | emp_id | emp_name |
    +--------+----------+
    |      1 | 张三     |
    |      2 | 李四     |
    |      3 | NULL     |
    |      4 | 马六     |
    |   NULL | 田七     |
    +--------+----------+
    5 rows in set (0.05 sec)

### 1.1.3 MySQL 中的笛卡尔积 ###

# 第一种方式
    select * from tb_name_1, tb_name_2, ···;
    
    # 第二种方式
    select * from tb_name_1 join tb_name_2 join ···;

> ![在这里插入图片描述][20200702210414682.jpg_pic_center]

### 1.1.4 示例 ###

mysql> select * from dept, emp;
    +---------+-----------+--------+----------+
    | dept_id | dept_name | emp_id | emp_name |
    +---------+-----------+--------+----------+
    |       1 | 技术部    |      1 | 张三     |
    |       1 | 技术部    |      2 | 李四     |
    |       1 | 技术部    |      3 | NULL     |
    |       1 | 技术部    |      4 | 马六     |
    |       1 | 技术部    |   NULL | 田七     |
    |       2 | NULL      |      1 | 张三     |
    |       2 | NULL      |      2 | 李四     |
    |       2 | NULL      |      3 | NULL     |
    |       2 | NULL      |      4 | 马六     |
    |       2 | NULL      |   NULL | 田七     |
    |    NULL | 财务部    |      1 | 张三     |
    |    NULL | 财务部    |      2 | 李四     |
    |    NULL | 财务部    |      3 | NULL     |
    |    NULL | 财务部    |      4 | 马六     |
    |    NULL | 财务部    |   NULL | 田七     |
    |       4 | 办公室    |      1 | 张三     |
    |       4 | 办公室    |      2 | 李四     |
    |       4 | 办公室    |      3 | NULL     |
    |       4 | 办公室    |      4 | 马六     |
    |       4 | 办公室    |   NULL | 田七     |
    |       5 | 运营部    |      1 | 张三     |
    |       5 | 运营部    |      2 | 李四     |
    |       5 | 运营部    |      3 | NULL     |
    |       5 | 运营部    |      4 | 马六     |
    |       5 | 运营部    |   NULL | 田七     |
    |       6 | NULL      |      1 | 张三     |
    |       6 | NULL      |      2 | 李四     |
    |       6 | NULL      |      3 | NULL     |
    |       6 | NULL      |      4 | 马六     |
    |       6 | NULL      |   NULL | 田七     |
    +---------+-----------+--------+----------+
    30 rows in set (0.05 sec)

## 1.2 内连接 ##

内连接也叫连接，是最早的一种连接。还可以被称为普通连接或者自然连接，内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。内连接相当于在笛卡尔积的基础上加上了连接的条件；当没有连接条件的时候，内连接上升为笛卡尔积。

![在这里插入图片描述][20200702215129341.jpg_pic_center]

### 1.2.1 语法 ###

# 第一种方式(显示内连接)，inner 可以省略，condition 为连接条件
    select * from tb_name_1 [inner] join tb_name_2 on condition;
    
    # 第二种方式(隐式内连接)
    select * from tb_name_1, tb_name_2 where condition;

☞ **说明**

① 第一种是在 on 后使用了连接条件  
 ② 第二种是先获取连接的结果，然后使用 where 中的条件再对连接结果进行过滤  
 ③ 内连接查询的数据不包含连接条件字段为 null 的数据

### 1.2.2 示例 ###

mysql> select * from dept join emp on dept.dept_id = emp.emp_id;
    +---------+-----------+--------+----------+
    | dept_id | dept_name | emp_id | emp_name |
    +---------+-----------+--------+----------+
    |       1 | 技术部    |      1 | 张三     |
    |       2 | NULL      |      2 | 李四     |
    |       4 | 办公室    |      4 | 马六     |
    +---------+-----------+--------+----------+
    3 rows in set (0.05 sec)

## 1.3 外连接 ##

外连接涉及到 2 个表，主表和从表，要查询的信息主要来自于哪个表，谁就是主表。外连接查询结果为主表中所有记录。如果从表中有和它匹配的，则显示匹配的值，这部分相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显示null。

![在这里插入图片描述][20200702215505123.jpg_pic_center]

### 1.3.1 左外连接 ###

☞ **语法**

# 左外链接使用 left join 关键字，left join 左边的是主表，outer 可以省略
    select * from tb_name_1 left [outer] join tb_name_2 on condition;

☞ **示例**

mysql> select * from dept left join emp on dept.dept_id = emp.emp_id;
    +---------+-----------+--------+----------+
    | dept_id | dept_name | emp_id | emp_name |
    +---------+-----------+--------+----------+
    |       1 | 技术部    |      1 | 张三     |
    |       2 | NULL      |      2 | 李四     |
    |       4 | 办公室    |      4 | 马六     |
    |    NULL | 财务部    |   NULL | NULL     |
    |       5 | 运营部    |   NULL | NULL     |
    |       6 | NULL      |   NULL | NULL     |
    +---------+-----------+--------+----------+
    6 rows in set (0.05 sec)

### 1.3.2 右外连接 ###

☞ **语法**

# 右外连接使用 right join关键字，right join 右边的是主表，outer 可以省略
    select * from tb_name_1 right [outer] join tb_name_2 on condition;

☞ **示例**

mysql> select * from emp left join dept on dept.dept_id = emp.emp_id;
    +--------+----------+---------+-----------+
    | emp_id | emp_name | dept_id | dept_name |
    +--------+----------+---------+-----------+
    |      1 | 张三     |       1 | 技术部    |
    |      2 | 李四     |       2 | NULL      |
    |      4 | 马六     |       4 | 办公室    |
    |      3 | NULL     |    NULL | NULL      |
    |   NULL | 田七     |    NULL | NULL      |
    +--------+----------+---------+-----------+
    5 rows in set (0.05 sec)

☞ **注意**

左外连接和右外连接看似可以互换，但是要考虑大小表问题，如：（小表 左连 大表） （大表 右连 小表）二者之间的效率天差地别，推荐左边的表使用小表。

--------------------

关注公众号一起学习 Java 开发，跟大家分享我的学习成长过程

![在这里插入图片描述][2020060821282046.jpg_pic_center]

[2020070220255628.jpg]: https://img-blog.csdnimg.cn/2020070220255628.jpg
[20200702203410251.png]: https://img-blog.csdnimg.cn/20200702203410251.png
[20200702210414682.jpg_pic_center]: https://img-blog.csdnimg.cn/20200702210414682.jpg#pic_center
[20200702215129341.jpg_pic_center]: https://img-blog.csdnimg.cn/20200702215129341.jpg#pic_center
[20200702215505123.jpg_pic_center]: https://img-blog.csdnimg.cn/20200702215505123.jpg#pic_center
[2020060821282046.jpg_pic_center]: https://img-blog.csdnimg.cn/2020060821282046.jpg#pic_center