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

时间：2020-05-15 18:10:05 收藏：0 阅读：35

建表：

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

结果

技术分享图片

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

SELECT * FROM tb1 LEFT JOIN tb2 ON tb1.a_id=tb2.a_id AND tb2.a_name <> ‘乔峰‘

结果：

技术分享图片

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

SELECT * FROM tb1 LEFT JOIN tb2 ON tb1.a_id=tb2.a_id AND tb1.a_name <> ‘猪八戒‘

结果：

技术分享图片

结论：

由以上对比，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

结果：

技术分享图片

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

SELECT * FROM tb1 inner JOIN tb2 ON tb1.a_id = tb2.a_id AND tb2.a_name <> ‘乔峰‘

结果：

技术分享图片

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

SELECT * FROM tb1 inner JOIN tb2 ON tb1.a_id = tb2.a_id AND tb1.a_name <> ‘猪八戒‘

结果：

技术分享图片

结论：从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的子步骤。

原文：https://www.cnblogs.com/yoyowin/p/12896122.html