MySQL 多表查询连接方式详解

快来打我* 2022-03-29 10:50 696阅读 0赞

多表连接分类：内连接、外连接、交叉连接

多表连接方式：

*  内连接：inner join，join
 *  外连接：left join，left outer join，right join，right outer join，union
 *  交叉连接：cross join

通过具体实例进行分析：

department部门表：

![20190108142515775.png][]

employee员工表：

![20190108142602789.png][]

其中，外键对应关系为：employee.dept\_id=department.id。employee.leader中的数字的含义为：张一的leader是张二，张二没有leader，张三的leader是张一。

**1. 内连接**

inner join 或 join（等同于inner join）

语法：

SELECT ··· FROM table_1
    [INNER] JOIN table_2
    [ON join_condition] 
    WHERE where_definition

解释：只列出这些连接表中与连接条件相匹配的数据行。INNER可以不写，则默认为内连接。\[ON join\_condition\]里面写的是连接的条件。

举例：

select e.name, d.name from employee e inner join department d on e.dept_id = d.id;
    或
    select e.name, d.name from employee e join department d on e.dept_id = d.id;

等价于：

select e.name,d.name from employee e , department d where e.dept_id = d.id;

运行结果：

![20190108152755603.png][]

![20190108152821433.png][]

应用场景：

![20190108155844646.png][]

这种场景下得到的是满足某一条件的A，B内部的数据；正因为得到的是内部共有数据，所以连接方式称为内连接。

**2. 外连接**

外连接分类：

*  左外连接(LEFT \[OUTER\] JOIN)
 *  右外连接(RIGHT \[OUTER\] JOIN)
 *  全外连接(FULL \[OUTER\] JOIN)  注：MySQL5.1的版本暂不支持

语法：

SELECT ··· FROM table_1
    (LEFT | RIGHT | FULL) [OUTER] JOIN table_2
    ON join_condition
    WHERE where_definition

解释：不仅列出与连接条件（on）相匹配的行，还列出左表table1(左外连接)、或右表table2(右外连接)、或两个表(全外连接)中所有符合WHERE过滤条件的数据行。一般都是用左连接或者外连接。其中，\[OUTER\]部分可以不写，(LEFT | RIGHT | FULL)部分要写其中一个。

*   左外连接：左表列出全部，右表只列出匹配的部分

举例：

SELECT * FROM employee e LEFT JOIN department d ON e.dept_id = d.id;

运行结果：

![20190108161237324.png][]

应用场景：

![20190108161757583.png][]

这种场景下得到的是A的所有数据，和满足某一条件的B的数据。

*  \[left   join 或者left outer join(等同于left join)\]  +  \[where B.column is null\]

举例：

SELECT e.name,d.name FROM employee e LEFT JOIN department d ON e.dept_id = d.id WHERE d.id is NULL;

运行结果：

![20190108163239939.png][]

应用场景：

![20190108163426708.png][]

这种场景下得到的是A中的所有数据减去"与B满足同一条件 的数据"，然后得到的A剩余数据

*  右外连接：右表列出全部，左表列出匹配部分

举例：

SELECT * FROM employee e RIGHT JOIN department d ON e.dept_id = d.id;

运行结果：

![20190108162223840.png][]

应用场景：

![20190108162331758.png][]

这种场景下得到的是B的所有数据，和满足某一条件的A的数据

*  \[right  join 或者right outer join(等同于right join)\]  +  \[where A.column is null\]

举例：

SELECT e.name,d.name FROM employee e RIGHT JOIN department d ON e.dept_id = d.id WHERE e.dept_id is NULL;

运行结果：

![20190108163923506.png][]

应用场景：

![20190108164022660.png][]

这种场景下得到的是B中的所有数据减去 "与A满足同一条件 的数据“，然后得到的B剩余数据

*  full join （mysql不支持，但是可以用 left join  union right join代替）

举例：

SELECT e.name,d.name FROM employee e LEFT JOIN department d ON e.dept_id = d.id
    UNION
    SELECT e.name,d.name FROM employee e RIGHT JOIN department d ON e.dept_id = d.id;

运行结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FuZ2VsaWE2MjA_size_16_color_FFFFFF_t_70][]

应用场景：

![20190108164816101.png][]

这种场景下得到的是满足某一条件的公共记录，和独有的记录

*  full join + is null（mysql不支持，但是可以用 （left join + is null） union （right join + is null）代替）

举例：

SELECT e.name,d.name FROM employee e LEFT JOIN department d ON e.dept_id = d.id WHERE d.id IS NULL
    UNION
    SELECT e.name,d.name FROM employee e RIGHT JOIN department d ON e.dept_id = d.id WHERE e.dept_id IS NULL;

运行结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FuZ2VsaWE2MjA_size_16_color_FFFFFF_t_70 1][]

应用场景：

![20190108165319984.png][]

这种场景下得到的是A，B中不满足某一条件的记录之和

**3. 交叉连接**

语法：

SELECT ··· FROM table_1 CROSS JOIN table_2;

没有ON子句和WHERE子句，它返回的是连接表中所有数据行的笛卡尔积。

笛卡尔积举例：假设集合A=\{a,b\}，集合B=\{0,1,2\}，则两个集合的笛卡尔积为\{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)\}

其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

等价于：

SELECT ··· FROM table_1, table_2;

举例：

SELECT e.name,d.name FROM employee e ,department d;
    或
    SELECT e.name,d.name FROM employee e CROSS FROM department d;

运行结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FuZ2VsaWE2MjA_size_16_color_FFFFFF_t_70 2][]

**4. 自连接：参与连接的表式同一张表，通过给表取别名虚拟出两张表**

注：非常重要，在JavaWeb中的目录树中用的特别多

举例：查询出员工姓名和其leader的姓名（类似于求节点及其父节点）

SELECT e1.name 员工,e2.name 领导 FROM employee e1 LEFT JOIN employee e2 ON e1.leader = e2.id;

运行结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FuZ2VsaWE2MjA_size_16_color_FFFFFF_t_70 3][]

解释：对于同一张employee表，我们把e1作为员工表，e2作为领导表。首先把全部的员工列出来（基于左外连接），然后找到我们所需要的条件：员工的经理id（e1.leader）等于经理表的id（e2.id）。

举例：查询出所有leader的姓名

SELECT e2.name 领导 FROM employee e1 LEFT JOIN employee e2 ON e1.leader = e2.id;

运行结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FuZ2VsaWE2MjA_size_16_color_FFFFFF_t_70 4][]

分析道理同上。

其实，上面的两个查询结果都是下面这个查询结果的一部分：

SELECT * FROM employee e1 LEFT JOIN employee e2 ON e1.leader = e2.id;

运行结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FuZ2VsaWE2MjA_size_16_color_FFFFFF_t_70 5][]

[20190108142515775.png]: /images/20220329/3eff2f5a30fd4d1b8ac0423493de0a85.png
[20190108142602789.png]: /images/20220329/ea4bcfd3c8ac4eb0b8dc48bad9531ec4.png
[20190108152755603.png]: /images/20220329/2e19226fffb64871b97217e77b3b1192.png
[20190108152821433.png]: /images/20220329/33006d16a06344e4bba78bbbac1e10af.png
[20190108155844646.png]: /images/20220329/32de9aadcb74420e88046d43bc21f126.png
[20190108161237324.png]: /images/20220329/ff1d2942755c46fc837ff9fb5316db44.png
[20190108161757583.png]: /images/20220329/0a05ea91e3774cadae214469ec06bfa6.png
[20190108163239939.png]: /images/20220329/3301d473ee4449059f3c25bb8a2d9f9c.png
[20190108163426708.png]: /images/20220329/31cebdee98194849a4082fa81d1671b2.png
[20190108162223840.png]: /images/20220329/b96ffc1995f74452b455ddaf9f8fa750.png
[20190108162331758.png]: /images/20220329/c9c28cb4d28246949c5cc40e1d7e7f2e.png
[20190108163923506.png]: /images/20220329/818ec93efe9d435f86a2b61d39b1f7a5.png
[20190108164022660.png]: /images/20220329/d338057907ad49e686e7f5a44cf45e12.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FuZ2VsaWE2MjA_size_16_color_FFFFFF_t_70]: /images/20220329/384539a24c6f4256b95523c00c5aaf92.png
[20190108164816101.png]: /images/20220329/2168f3c2f0b44d5b8aeec1f6baa60afd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FuZ2VsaWE2MjA_size_16_color_FFFFFF_t_70 1]: /images/20220329/2d9e9cf5c6e44909bda2f1ca5d4f8d4f.png
[20190108165319984.png]: /images/20220329/735acc08d2ea4cf38c3a9fb31647067f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FuZ2VsaWE2MjA_size_16_color_FFFFFF_t_70 2]: /images/20220329/8aef2b4f88f04bd396fd34af22f8e4e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FuZ2VsaWE2MjA_size_16_color_FFFFFF_t_70 3]: /images/20220329/c0af692ea2354730b5eaad97b5465e3a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FuZ2VsaWE2MjA_size_16_color_FFFFFF_t_70 4]: /images/20220329/96028a7238e8406689c5a6f890857125.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FuZ2VsaWE2MjA_size_16_color_FFFFFF_t_70 5]: /images/20220329/c90a91cbc8c549b5adc990a1397d16ac.png