数据库：SQL 连接(JOIN)

ゝ一世哀愁。 2022-04-23 11:28 249阅读 0赞

SQL join 用于把来自两个或多个表的行结合起来。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。(图是在菜鸟教程上找的)

![sql-join.png][]

### 首先准备两张表  ###

employee表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIzNjQwNA_size_16_color_FFFFFF_t_70][]

department表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIzNjQwNA_size_16_color_FFFFFF_t_70 1][]

## INNER JOIN (常用JOIN语句) ##

inner语句会从多个表中返回满足 JOIN 条件的所有行。

SELECT * FROM tbl_employee e INNER JOIN tbl_department d on e.d_id = d.id;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIzNjQwNA_size_16_color_FFFFFF_t_70 2][]

### LEFT JOIN ###

SELECT * FROM tbl_employee e LEFT JOIN tbl_department d on e.d_id = d.id;

LEFT JOIN 会把左表全部查询出来 即使右表中没有匹配，也从左表返回所有的行;

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIzNjQwNA_size_16_color_FFFFFF_t_70 3][] ###

### RIGHT JOIN ###

SELECT * FROM tbl_employee e RIGHT JOIN tbl_department d on e.d_id = d.id;

RIGHT JOIN 会把右表表全部查询出来 即使左表中没有匹配，也从右表返回所有的行

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIzNjQwNA_size_16_color_FFFFFF_t_70 4][]

### FULL JOIN ###

mysql 不支持FULL JOIN 实际上就是把两表都查询出来,

只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

如果 "tbl\_employee" 表中的行在 "tbl\_department" 中没有匹配或者 "tbl\_department" 表中的行在 "tbl\_employee" 表中没有匹配，也会列出这些行。

[sql-join.png]: /images/20220320/ba61ce58041b4ed1b049739c788e3121.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIzNjQwNA_size_16_color_FFFFFF_t_70]: /images/20220320/2d84ee78779e418583c2fd70669b7282.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIzNjQwNA_size_16_color_FFFFFF_t_70 1]: /images/20220320/de5d76d3d7a947419bc5917280481da9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIzNjQwNA_size_16_color_FFFFFF_t_70 2]: /images/20220320/99bb9fc5a4f245d78fb0d57af6801df6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIzNjQwNA_size_16_color_FFFFFF_t_70 3]: /images/20220320/7a1954b8c10f4f309541988dbe373fe0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjIzNjQwNA_size_16_color_FFFFFF_t_70 4]: /images/20220320/a0ba657831904c75aca68b8d883feea2.png