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

最新推荐文章于 2024-07-25 22:24:44 发布

原创最新推荐文章于 2024-07-25 22:24:44 发布 · 796 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

数据库专栏收录该内容

48 篇文章

订阅专栏

本文深入探讨了SQL中的LEFT JOIN和INNER JOIN操作。通过示例代码展示不同类型的JOIN如何处理数据表连接，以及ON和AND条件在JOIN操作中的影响。LEFT JOIN中，ON条件仅筛选从表数据，而INNER JOIN则在连接时直接应用筛选条件，等价于WHERE子句。理解这些概念对于优化SQL查询至关重要。

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