Mysql Join总结

电玩女神 2022-02-04 10:36 232阅读 0赞

### Mysql Join总结 ###

*  Mysql 连接
 *  数据准备
 *   *  新建测试数据库
     *  新建测试数据表
 *  MySQL交叉连接(CROSS JOIN)
 *  MySQL内连接(INNER JOIN)
 *  MySQL左连接(LEFT JOIN)
 *  MySQL右连接(RIGHT JOIN)
 *  ON 与Where

首先，一张最完美的图来展示我们可能遇到的各种连接场景，当然这幅图不适合Mysql，因为Mysql不支持Full Join，但是我们依然可以来理解可能遇到的各种join场景。  
![sql joins][]

# Mysql 连接 #

MySQL连接是一种基于表之间的公共列的值来链接来自一个(自连接)或更多表的数据的方法。  
MySQL支持以下类型的连接：

*  **交叉连接(Cross join)**
 *  **内连接(Inner join)**
 *  **左连接(Left join)**
 *  **右连接(Right join)**

要连接表，可以对相应类型的连接使用CROSS JOIN，INNER JOIN，LEFT JOIN或RIGHT JOIN子句。 在SELECT语句中的FROM子句之后使用了连接子句。

请注意，MySQL不支持完全外部连接。

# 数据准备 #

## 新建测试数据库 ##

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */;

## 新建测试数据表 ##

use test;
    #新建产品品牌分类表
    CREATE TABLE `s_brand_category` (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `name` varchar(64) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    
    insert into s_brand_category(`name`) values ('手机'),('电脑');
    # 新建产品品牌表
    CREATE TABLE `s_product_brand` (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `category_id` int(11) UNSIGNED NOT NULL COMMENT '品牌分类Id' ,
      `name` varchar(64) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `#idx_category_id` (`category_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    
    insert into s_product_brand(`category_id`,`name`) values(1,'小米'),(1,'华为'),(2,'联想');

# MySQL交叉连接(CROSS JOIN) #

CROSS JOIN生成来自多个表的行的笛卡尔乘积。假设您使用CROSS JOIN来连接t1和t2表，结果集将包括t1表中的行与t2表中的行的组合笛卡尔乘积。 Mysql默认的连接方式即CROSS JOIN。

mysql> select * from s_brand_category ,s_product_brand;
    +----+--------+----+-------------+--------+
    | id | name   | id | category_id | name   |
    +----+--------+----+-------------+--------+
    |  1 | 手机   |  1 |           1 | 小米   |
    |  2 | 电脑   |  1 |           1 | 小米   |
    |  1 | 手机   |  2 |           1 | 华为   |
    |  2 | 电脑   |  2 |           1 | 华为   |
    |  1 | 手机   |  3 |           2 | 联想   |
    |  2 | 电脑   |  3 |           2 | 联想   |
    +----+--------+----+-------------+--------+
    6 rows in set (0.01 sec)
    
    mysql> select * from s_brand_category cross join s_product_brand;
    +----+--------+----+-------------+--------+
    | id | name   | id | category_id | name   |
    +----+--------+----+-------------+--------+
    |  1 | 手机   |  1 |           1 | 小米   |
    |  2 | 电脑   |  1 |           1 | 小米   |
    |  1 | 手机   |  2 |           1 | 华为   |
    |  2 | 电脑   |  2 |           1 | 华为   |
    |  1 | 手机   |  3 |           2 | 联想   |
    |  2 | 电脑   |  3 |           2 | 联想   |
    +----+--------+----+-------------+--------+
    6 rows in set (0.00 sec)

# MySQL内连接(INNER JOIN) #

要形成一个INNER JOIN连接子句，需要一个称为连接谓词的条件。 INNER JOIN需要两个连接的表中的行具有匹配的列值。 INNER JOIN通过组合基于连接谓词的两个连接表的列值来创建结果集。  
要连接两个表，INNER JOIN将第一个表中的每一行与第二个表中的每一行进行比较，以找到满足连接谓词的行对。每当通过匹配非NULL值来满足连接谓词时，两个表中每个匹配的行对的列值将包含在结果集中(可以简单地理解为两个表的交集).

当没有连接谓词的存在的时候INNER JOIN 得到的结果即是CROSS JOIN。

mysql> select * from s_brand_category inner join s_product_brand;
    +----+--------+----+-------------+--------+
    | id | name   | id | category_id | name   |
    +----+--------+----+-------------+--------+
    |  1 | 手机   |  1 |           1 | 小米   |
    |  2 | 电脑   |  1 |           1 | 小米   |
    |  1 | 手机   |  2 |           1 | 华为   |
    |  2 | 电脑   |  2 |           1 | 华为   |
    |  1 | 手机   |  3 |           2 | 联想   |
    |  2 | 电脑   |  3 |           2 | 联想   |
    +----+--------+----+-------------+--------+
    6 rows in set (0.00 sec)
    
    mysql> select * from s_brand_category as c inner join s_product_brand  as b on c.id = b.category_id;
    +----+--------+----+-------------+--------+
    | id | name   | id | category_id | name   |
    +----+--------+----+-------------+--------+
    |  1 | 手机   |  1 |           1 | 小米   |
    |  1 | 手机   |  2 |           1 | 华为   |
    |  2 | 电脑   |  3 |           2 | 联想   |
    +----+--------+----+-------------+--------+
    3 rows in set (0.00 sec)

# MySQL左连接(LEFT JOIN) #

类似于INNER JOIN，LEFT JOIN也需要连接谓词。当使用LEFT JOIN连接两个表时，介绍了左表和右表的概念。

与INNER JOIN不同，LEFT JOIN返回左表中的所有行，包括满足连接谓词的行。 对于不匹配连接谓词的行，右表中的列将使用NULL值显示在结果集中。  
不同于Inner Join,Left Join 必须有连接谓词ON的存在。

# 先插入一条种类不存在的产品记录
    insert into s_product_brand(`category_id`,`name`) values(3,'特步');

mysql> select * from s_product_brand as b left join s_brand_category as c  on c.id = b.category_id;
    +----+-------------+--------+------+--------+
    | id | category_id | name   | id   | name   |
    +----+-------------+--------+------+--------+
    |  1 |           1 | 小米   |    1 | 手机   |
    |  2 |           1 | 华为   |    1 | 手机   |
    |  3 |           2 | 联想   |    2 | 电脑   |
    |  4 |           3 | 特步   | NULL | NULL   |
    +----+-------------+--------+------+--------+
    4 rows in set (0.00 sec)
    
    mysql> select * from s_brand_category as c left join s_product_brand  as b on c.id = b.category_id;
    +----+--------+------+-------------+--------+
    | id | name   | id   | category_id | name   |
    +----+--------+------+-------------+--------+
    |  1 | 手机   |    1 |           1 | 小米   |
    |  1 | 手机   |    2 |           1 | 华为   |
    |  2 | 电脑   |    3 |           2 | 联想   |
    +----+--------+------+-------------+--------+
    3 rows in set (0.00 sec)

# MySQL右连接(RIGHT JOIN) #

右连接(RIGHT JOIN)类似于左连接(LEFT JOIN)，除了表的处理是相反的。使用RIGHT JOIN，右表格(t2)中的每一行将显示在结果集中。 对于右表中没有左表(t1)中的匹配行的行，左表(t1)中的列会显示NULL。

不同于Inner Join,Right Join 必须有连接谓词ON的存在。

mysql> select * from s_product_brand as b right join s_brand_category as c  on c.id = b.category_id;
    +------+-------------+--------+----+--------+
    | id   | category_id | name   | id | name   |
    +------+-------------+--------+----+--------+
    |    1 |           1 | 小米   |  1 | 手机   |
    |    2 |           1 | 华为   |  1 | 手机   |
    |    3 |           2 | 联想   |  2 | 电脑   |
    +------+-------------+--------+----+--------+
    3 rows in set (0.00 sec)
    
    mysql> select * from s_brand_category as c right join s_product_brand  as b on c.id = b.category_id;
    +------+--------+----+-------------+--------+
    | id   | name   | id | category_id | name   |
    +------+--------+----+-------------+--------+
    |    1 | 手机   |  1 |           1 | 小米   |
    |    1 | 手机   |  2 |           1 | 华为   |
    |    2 | 电脑   |  3 |           2 | 联想   |
    | NULL | NULL   |  4 |           3 | 特步   |
    +------+--------+----+-------------+--------+

# ON 与Where #

这里，我们先注意在左右连接查询中 on和where未知顺序不同导致的查询结果的不同。

mysql> select * from s_product_brand as b left join s_brand_category as c  on  b.category_id = c.id  and  c.id = 1;
    +----+-------------+--------+------+--------+
    | id | category_id | name   | id   | name   |
    +----+-------------+--------+------+--------+
    |  1 |           1 | 小米   |    1 | 手机   |
    |  2 |           1 | 华为   |    1 | 手机   |
    |  3 |           2 | 联想   | NULL | NULL   |
    |  4 |           3 | 特步   | NULL | NULL   |
    +----+-------------+--------+------+--------+
    4 rows in set (0.00 sec)
    
    mysql> select * from s_product_brand as b left join s_brand_category as c  on  b.category_id = c.id  where  c.id = 1;
    +----+-------------+--------+------+--------+
    | id | category_id | name   | id   | name   |
    +----+-------------+--------+------+--------+
    |  1 |           1 | 小米   |    1 | 手机   |
    |  2 |           1 | 华为   |    1 | 手机   |
    +----+-------------+--------+------+--------+
    2 rows in set (0.00 sec)

我们会发现两个查询存在差异。

为什么会存在差异，其实这和on与where查询顺序有关。

首先，我们需要知道：  
标准的 SQL 解析顺序为:

1.  FROM 子句 组装来自不同数据源的数据
2.  WHERE 子句 基于指定的条件对记录进行筛选
3.  GROUP BY 子句 将数据划分为多个分组
4.  使用聚合函数进行计算
5.  使用HAVING子句筛选分组
6.  计算所有的表达式
7.  使用ORDER BY对结果集进行排序

标准的 SQL 执行顺序为:

1.  FROM：对FROM子句中前两个表执行笛卡尔积生成虚拟表vt1
2.  ON: 对vt1表应用ON筛选器只有满足 join\_condition 为真的行才被插入vt2
3.  OUTER(join)：如果指定了 OUTER JOIN保留表(preserved table)中未找到的行将行作为外部行添加到vt2，生成t3，如果from包含两个以上表，则对上一个联结生成的结果表和下一个表重复执行步骤和步骤直接结束。
4.  WHERE：对vt3应用 WHERE 筛选器只有使 where\_condition 为true的行才被插入vt4
5.  GROUP BY：按GROUP BY子句中的列列表对vt4中的行分组生成vt5
6.  CUBE|ROLLUP：把超组(supergroups)插入vt6，生成vt6
7.  HAVING：对vt6应用HAVING筛选器只有使 having\_condition 为true的组才插入vt7
8.  SELECT：处理select列表产生vt8
9.  DISTINCT：将重复的行从vt8中去除产生vt9
10. ORDER BY：将vt9的行按order by子句中的列列表排序生成一个游标vc10

所以这里的根本原因在于  
先on条件筛选表行再join

而对于where是对 join之后结果做再次筛选。

ON与where的使用一定要注意场所：

*  ON后面的筛选条件主要是针对的是关联表【而对于主表刷选条件不适用】。
 *  对于主表的筛选条件应放在where后面，不应该放在ON后面
 *  对于关联表我们要区分对待。如果是要条件查询后才连接应该把查询件放置于ON后。如果是想再连接完毕后才筛选就应把条件放置于where后面
 *  对于关联表我们其实可以先做子查询再做join

mysql> select * from s_product_brand as b left join (select * from s_brand_category where id = 1) as c  on  b.category_id = c.id  ;
    +----+-------------+--------+------+--------+
    | id | category_id | name   | id   | name   |
    +----+-------------+--------+------+--------+
    |  1 |           1 | 小米   |    1 | 手机   |
    |  2 |           1 | 华为   |    1 | 手机   |
    |  3 |           2 | 联想   | NULL | NULL   |
    |  4 |           3 | 特步   | NULL | NULL   |
    +----+-------------+--------+------+--------+
    4 rows in set (0.00 sec)
    
    mysql> select * from s_product_brand as b left join s_brand_category as c  on  b.category_id = c.id  and  c.id = 1;
    +----+-------------+--------+------+--------+
    | id | category_id | name   | id   | name   |
    +----+-------------+--------+------+--------+
    |  1 |           1 | 小米   |    1 | 手机   |
    |  2 |           1 | 华为   |    1 | 手机   |
    |  3 |           2 | 联想   | NULL | NULL   |
    |  4 |           3 | 特步   | NULL | NULL   |
    +----+-------------+--------+------+--------+
    4 rows in set (0.00 sec)

[sql joins]: /images/20220204/41c9ddf966954f3b955365f01ba5835c.png