MySQL高级性能优化---JOIN连接

本是古典何须时尚 2021-12-11 06:21 390阅读 0赞

# 1. 先准备测试表数据 #

创建两种表部门表(department)和员工表(employee)  
![在这里插入图片描述][2019071016055993.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70]

# 2. 内连接 #

## 2.1 作用 ##

查询两张表的共有部分  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 1]

## 2.2 语句 ##

Select <select\_list> from tableA A Inner join tableB B on A.key = B.key

select * from employee e INNER JOIN department d on e.dep_id = d.id

![在这里插入图片描述][20190710161933808.png]

# 3. 左连接与右连接 #

以左连接举例

## 3.1 作用 ##

把左边表的内容全部查出，右边表只查出满足条件的记录  
![在这里插入图片描述][20190710162242122.png]

## 3.2 语句 ##

Select <select\_list> from tableA A left join tableB B on A.key = B.key

select * from employee e LEFT JOIN department d on e.dep_id = d.id

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 2]

# 4. 查询左表独有数据和查询右表独有数据 #

以左连接举例

## 4.1 作用 ##

![在这里插入图片描述][2019071016262851.png]

## 4.2 语句 ##

Select <select\_list> from tableA A left join tableB B on A.key = B.key WHERE B.key is NULL

select * from employee e LEFT JOIN department d on e.dep_id = d.id where d.id is NULL

![在这里插入图片描述][20190710162845695.png]

# 5. 全连接 #

## 5.1 作用 ##

查询两张表的所有信息  
![在这里插入图片描述][20190710163045642.png]

## 5.2 语句 ##

Select <select\_list> from tableA A Full Outter join tableB B on A.key = B.key  
mysql不支持full outter join， 下边的mysql全连接的另一种写法

select * from employee e LEFT JOIN department d on e.dep_id = d.id
    UNION
    select * from employee e RIGHT JOIN department d on e.dep_id = d.id

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 3]

# 6. 查询左右表各自独有的数据 #

## 5.1 作用 ##

查询两张表独有的数据  
![在这里插入图片描述][20190710164143544.png]

## 5.2 语句 ##

select * from employee e LEFT JOIN department d on e.dep_id = d.id where d.id is null
    UNION
    select * from employee e RIGHT JOIN department d on e.dep_id = d.id where e.id is null

![在这里插入图片描述][20190710164248662.png]

[2019071016055993.png]: /images/20211211/6871cd59b0f74b5baf2500a03764e025.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70]: /images/20211211/c1f6d18f4cb24622afd11ac7e740e3fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 1]: /images/20211211/172726fad95041c38ed2c1392b233b64.png
[20190710161933808.png]: /images/20211211/46cc58e507b84a3690257255c35361cc.png
[20190710162242122.png]: /images/20211211/4e6a85b6c5dd49b69e0390b7bb98af3d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 2]: /images/20211211/bf05ffe4349243c3b5486183107b6464.png
[2019071016262851.png]: /images/20211211/445e27a6d6cd4f7985463965cf6098c4.png
[20190710162845695.png]: /images/20211211/d53c5d4494f240c6a17bcfb81f30ea1f.png
[20190710163045642.png]: /images/20211211/8e02f5ea953d48be9a12ac21028fc234.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 3]: /images/20211211/cbc872f0d65d469da4af43c2b0922595.png
[20190710164143544.png]: /images/20211211/47e20ae6e52647b4a0d6f0e942620d93.png
[20190710164248662.png]: /images/20211211/d7a89c4df23f4326805393d95c557970.png