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

复制代码
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的子步骤