MySQL中join的用法

客官°小女子只卖身不卖艺 2022-04-18 04:33 320阅读 0赞

转载自 [https://www.cnblogs.com/beili/p/9140019.html][https_www.cnblogs.com_beili_p_9140019.html]

近期用phpcms v9做项目，初期没有问题，后期随着数据量的增大，phpcms v9后台出现的栏目更新不动的情况，初期我以为是程序的问题，进行了程序排查，没有发现任何问题，登录上centos服务器后free命令发现内存也没有满。瞬间感觉很蛋疼，不知如何处理，后来登录阿里云账号，发现更新栏目时,cpu使用率直接到达100%，xhell远程登录进入服务器后用top检测cpu使用率的情况，发现MySQL占用的cpu使用率的100%,直接登录进入MySQL数据库，输入show full processlist; 可以看到正在执行的语句。看到是一条嵌套的sql语句执行异常缓慢！由于做项目之前没有考虑到数据量的问题，所以出现这样的情况，想比较嵌套的sql语句，关联查询性能效率会高很多，多表关联查询有一个关键的词就是join。

JOIN的含义就如英文单词“join”一样，连接两张表，大致分为内连接，外连接，右连接，左连接，自然连接。这里描述先甩出一张用烂了的图，然后插入测试数据。

![1264003-20180605152352881-1456130326.png][]

![ContractedBlock.gif][]

## 笛卡尔积：CROSS JOIN ##

要理解各种JOIN首先要理解笛卡尔积。笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n\*m条记录。下面的例子，t\_blog有10条记录，t\_type有5条记录，所有他们俩的笛卡尔积有50条记录。有五种产生笛卡尔积的方式如下。

![ContractedBlock.gif][]

## 内连接：INNER JOIN ##

内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集，从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。有INNER JOIN，WHERE（等值连接），STRAIGHT\_JOIN,JOIN(省略INNER)四种写法。至于哪种好我会在**MySQL的JOIN（二）：优化**讲述。示例如下。

![复制代码][copycode.gif]

SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId=t_type.id;
        SELECT * FROM t_blog,t_type WHERE t_blog.typeId=t_type.id;
        SELECT * FROM t_blog STRAIGHT_JOIN t_type ON t_blog.typeId=t_type.id; --注意STRIGHT_JOIN有个下划线
        SELECT * FROM t_blog JOIN t_type ON t_blog.typeId=t_type.id;
    
        +----+-------+--------+----+------+
        | id | title | typeId | id | name |
        +----+-------+--------+----+------+
        |  1 | aaa   |      1 |  1 | C++  |
        |  2 | bbb   |      2 |  2 | C    |
        |  7 | ggg   |      2 |  2 | C    |
        |  3 | ccc   |      3 |  3 | Java |
        |  6 | fff   |      3 |  3 | Java |
        |  4 | ddd   |      4 |  4 | C#   |
        |  5 | eee   |      4 |  4 | C#   |
        +----+-------+--------+----+------+

![复制代码][copycode.gif]

## 左连接：LEFT JOIN ##

左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲，就是先从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录（见最后三条）。

![复制代码][copycode.gif]

SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;
    
        +----+-------+--------+------+------+
        | id | title | typeId | id   | name |
        +----+-------+--------+------+------+
        |  1 | aaa   |      1 |    1 | C++  |
        |  2 | bbb   |      2 |    2 | C    |
        |  7 | ggg   |      2 |    2 | C    |
        |  3 | ccc   |      3 |    3 | Java |
        |  6 | fff   |      3 |    3 | Java |
        |  4 | ddd   |      4 |    4 | C#   |
        |  5 | eee   |      4 |    4 | C#   |
        |  8 | hhh   |   NULL | NULL | NULL |
        |  9 | iii   |   NULL | NULL | NULL |
        | 10 | jjj   |   NULL | NULL | NULL |
        +----+-------+--------+------+------+

![复制代码][copycode.gif]

## 右连接：RIGHT JOIN ##

同理右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。再次从笛卡尔积的角度描述，右连接就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上右表中剩余的记录（见最后一条）。

![复制代码][copycode.gif]

SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;
    
        +------+-------+--------+----+------------+
        | id   | title | typeId | id | name       |
        +------+-------+--------+----+------------+
        |    1 | aaa   |      1 |  1 | C++        |
        |    2 | bbb   |      2 |  2 | C          |
        |    3 | ccc   |      3 |  3 | Java       |
        |    4 | ddd   |      4 |  4 | C#         |
        |    5 | eee   |      4 |  4 | C#         |
        |    6 | fff   |      3 |  3 | Java       |
        |    7 | ggg   |      2 |  2 | C          |
        | NULL | NULL  |   NULL |  5 | Javascript |
        +------+-------+--------+----+------------+

![复制代码][copycode.gif]

## 外连接：OUTER JOIN ##

外连接就是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录，最后加上右表中剩余的记录。另外MySQL不支持OUTER JOIN，但是我们可以对左连接和右连接的结果做UNION操作来实现。

![复制代码][copycode.gif]

SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id
        UNION
        SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;
    
        +------+-------+--------+------+------------+
        | id   | title | typeId | id   | name       |
        +------+-------+--------+------+------------+
        |    1 | aaa   |      1 |    1 | C++        |
        |    2 | bbb   |      2 |    2 | C          |
        |    7 | ggg   |      2 |    2 | C          |
        |    3 | ccc   |      3 |    3 | Java       |
        |    6 | fff   |      3 |    3 | Java       |
        |    4 | ddd   |      4 |    4 | C#         |
        |    5 | eee   |      4 |    4 | C#         |
        |    8 | hhh   |   NULL | NULL | NULL       |
        |    9 | iii   |   NULL | NULL | NULL       |
        |   10 | jjj   |   NULL | NULL | NULL       |
        | NULL | NULL  |   NULL |    5 | Javascript |
        +------+-------+--------+------+------------+

![复制代码][copycode.gif]

## USING子句 ##

MySQL中连接SQL语句中，ON子句的语法格式为：table1.column\_name = table2.column\_name。当模式设计对联接表的列采用了相同的命名样式时，就可以使用 USING 语法来简化 ON 语法，格式为：USING(column\_name)。   
所以，USING的功能相当于ON，区别在于USING指定一个属性名用于连接两个表，而ON指定一个条件。另外，SELECT \*时，USING会去除USING指定的列，而ON不会。实例如下。

![复制代码][copycode.gif]

SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId =t_type.id;
        +----+-------+--------+----+------+
        | id | title | typeId | id | name |
        +----+-------+--------+----+------+
        |  1 | aaa   |      1 |  1 | C++  |
        |  2 | bbb   |      2 |  2 | C    |
        |  7 | ggg   |      2 |  2 | C    |
        |  3 | ccc   |      3 |  3 | Java |
        |  6 | fff   |      3 |  3 | Java |
        |  4 | ddd   |      4 |  4 | C#   |
        |  5 | eee   |      4 |  4 | C#   |
        +----+-------+--------+----+------+
    
    
        SELECT * FROM t_blog INNER JOIN t_type USING(typeId);
        ERROR 1054 (42S22): Unknown column 'typeId' in 'from clause'
        SELECT * FROM t_blog INNER JOIN t_type USING(id); -- 应为t_blog的typeId与t_type的id不同名，无法用Using，这里用id代替下。
        +----+-------+--------+------------+
        | id | title | typeId | name       |
        +----+-------+--------+------------+
        |  1 | aaa   |      1 | C++        |
        |  2 | bbb   |      2 | C          |
        |  3 | ccc   |      3 | Java       |
        |  4 | ddd   |      4 | C#         |
        |  5 | eee   |      4 | Javascript |
        +----+-------+--------+------------+

![复制代码][copycode.gif]

## 自然连接：NATURE JOIN ##

自然连接就是USING子句的简化版，它找出两个表中相同的列作为连接条件进行连接。有**左自然连接**，**右自然连接**和**普通自然连接**之分。在t\_blog和t\_type示例中，两个表相同的列是id，所以会拿id作为连接条件。   
另外千万分清下面三条语句的区别 。  
自然连接:SELECT \* FROM t\_blog NATURAL JOIN t\_type;   
笛卡尔积:SELECT \* FROM t\_blog NATURA JOIN t\_type;   
笛卡尔积:SELECT \* FROM t\_blog NATURE JOIN t\_type;

![ContractedBlock.gif][]

## 补充 ##

博客开头给出的第一张图除去讲了的内连接、左连接、右连接、外连接，还有一些特殊的韦恩图，这里补充一下。

![ContractedBlock.gif][]

写完这篇博客发现有点“孔乙己：茴字的四种写法的感觉”，但还是有收获的。另外，等三面通知等的好急啊!!

[https_www.cnblogs.com_beili_p_9140019.html]: https://www.cnblogs.com/beili/p/9140019.html
[1264003-20180605152352881-1456130326.png]: /images/20220418/185b760f67584b81b8b93b3afffb0aa8.png
[ContractedBlock.gif]: /images/20220418/dbc6727e11b4477fae4f045d6bbaabfe.png
[copycode.gif]: /images/20220418/29b16f4257c64220a77c50a858045f36.png