join ... on.... [and] 的理解

系统管理员 2021-07-25 23:42 353阅读 0赞

复制代码  
CREATE TABLE `tb1` (  
`a_id` INT(11) NOT NULL PRIMARY KEY AUTO\_INCREMENT COMMENT ‘序号’,  
`a_name` VARCHAR(50) NOT NULL COMMENT ‘名字’  
)  
;

CREATE TABLE `tb2` (  
`a_id` INT(11) NOT NULL PRIMARY KEY AUTO\_INCREMENT COMMENT ‘序号’,  
`a_name` VARCHAR(50) NOT NULL COMMENT ‘名字’  
)  
;  
复制代码  
插入数据：

复制代码  
INSERT INTO `scrapy`.`tb1` (`a_id`, `a_name`) VALUES (‘001’, ‘孙悟空’);  
INSERT INTO `scrapy`.`tb1` (`a_id`, `a_name`) VALUES (‘002’, ‘猪八戒’);  
INSERT INTO `scrapy`.`tb1` (`a_id`, `a_name`) VALUES (‘003’, ‘玉皇大帝’);

INSERT INTO `scrapy`.`tb2` (`a_id`, `a_name`) VALUES (‘001’, ‘张君宝’);  
INSERT INTO `scrapy`.`tb2` (`a_id`, `a_name`) VALUES (‘002’, ‘乔峰’);  
INSERT INTO `scrapy`.`tb2` (`a_id`, `a_name`) VALUES (‘003’, ‘段正淳’);  
复制代码  
使用 left … join…on…，不带and

SELECT \* FROM tb1 LEFT JOIN tb2 ON tb1.a\_id = tb2.a\_id  
结果  
![在这里插入图片描述][961a699bd46802ac9a8b9f1cec8ede7a.png]

使用 left … join…on…，带and，并对从表施加条件

SELECT \* FROM tb1 LEFT JOIN tb2 ON tb1.a\_id=tb2.a\_id AND tb2.a\_name <> ‘乔峰’  
结果：  
![在这里插入图片描述][9e4a6f2c1c7ed10afd5f7bc35b8b0454.png]

使用 left … join…on…，带and，并对主表施加条件

SELECT \* FROM tb1 LEFT JOIN tb2 ON tb1.a\_id=tb2.a\_id AND tb1.a\_name <> ‘猪八戒’  
结果：  
![在这里插入图片描述][bd960f17adf570b125cc6cb416b9cbd0.png]

结论：

由以上对比，on后的条件仅是对从表进行条件筛选。对主表不起作用。这是由于sql的执行顺序决定的，先对两个表tb1和tb2进行 n×m 行的笛卡尔积计算 （SELECT \* FROM tb1,tb2），然后用on后边的条件进行过滤，再对主表补充完整。

\----------------------------------------------分割线---------------------------------------------------------------------

使用 inner join … on 不带 and

SELECT \* FROM tb1 inner JOIN tb2 ON tb1.a\_id = tb2.a\_id  
结果：  
![在这里插入图片描述][305351077a6acdde74c67702412dd576.png]

使用 inner join … on ，带 and 并对从表施加条件

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

SELECT \* FROM tb1 inner JOIN tb2 ON tb1.a\_id = tb2.a\_id AND tb2.a\_name <> ‘乔峰’

结果：  
![在这里插入图片描述][9b3ce7d34edb7474294b1c0f8c720a2a.png]

使用 inner join … on ，带 and 并对主表施加条件

SELECT \* FROM tb1 inner JOIN tb2 ON tb1.a\_id = tb2.a\_id AND tb1.a\_name <> ‘猪八戒’

结果：  
![在这里插入图片描述][a807ca86fa2df6fa27c6dc8213fc1e76.png]

结论：从sql执行顺序分析，对量表的笛卡尔积进行了on后的条件筛选。相较于left join没有补充主表的步骤。 故在inner join的on后添加and限制条件的话，等价于直接对笛卡尔积用where语句筛选（SELECT \* FROM tb1,tb2 WHERE tb1.a\_id = tb2.a\_id AND tb1.a\_name <> ‘猪八戒’）。

由此看：inner join 相当于left join的子步骤

[961a699bd46802ac9a8b9f1cec8ede7a.png]: /images/20210725/3b07329a3c774b5c83d9ccf687f3912d.png
[9e4a6f2c1c7ed10afd5f7bc35b8b0454.png]: /images/20210725/4739330114b74053a5bf82469d3ec5e4.png
[bd960f17adf570b125cc6cb416b9cbd0.png]: /images/20210725/e6fb953f110748de93f9716833426cda.png
[305351077a6acdde74c67702412dd576.png]: /images/20210725/79038ba484564f1ea843b653d965868f.png
[9eb744c511ae2c50ca536cb297b68452.png]: /images/20210725/208801586f1c4c25b7fa79ff40e5614e.png
[9b3ce7d34edb7474294b1c0f8c720a2a.png]: /images/20210725/3f9f3e9da1644457835cfabe11c6faf5.png
[a807ca86fa2df6fa27c6dc8213fc1e76.png]: /images/20210725/fae064f9fd8f4b3a94534e343d680a74.png