数据库左连接和右连接是可以相互转换的吧,不太确定理解对不对

2020-07-16 20:58:49 +08:00
 git00ll

这两种写法是等价的吗

select a.*, b.* from A a left join B b where a.bid = b.id

select a.*, b.* from B b right join A a where a.bid = b.id

2755 次点击
所在节点    MySQL
17 条回复
saulshao
2020-07-16 21:10:32 +08:00
这个是不是等价要取决于你的表设计,逻辑上是有差异的。
就你的 SQL 而言,左连接我记得 a 表中存在,但是 b 表中不存在的 id,会被选出。右连接则相反。
换句话说你可能会得到以下的一行数据:
(a.id,a.name,null,null,null)其中的 null 值都来自 b 表中你假设的字段。

我记得 MS SQL server books online 说得很清楚,关于这个概念。
saulshao
2020-07-16 21:12:39 +08:00
如果你的 a,b 两个表中的 id 都是唯一索引,等价这个说法是成立的,但是假如 a 表中的 id 对应的行比 b 表多,我记得左连接结果会以 a 表中的行为准,反之也成立
EminemW
2020-07-16 21:49:04 +08:00
*** a left join b ****会保留 a 表存在 但 b 表不存在的数据
qwertyzzz
2020-07-16 22:40:33 +08:00
新建 2 个表执行下?
no1xsyzy
2020-07-17 02:10:54 +08:00
@saulshao #2 慢点,你看下楼主的,在转换左右的同时 A B 位置也对调了……
所以是一样的。
no1xsyzy
2020-07-17 02:13:14 +08:00
输出结果是一样的
但中间的运行步骤是否一样就不清楚了。直觉上应该不会有不同。具体要 explain,微弱可能依赖于特定实现。
leoleoasd
2020-07-17 02:36:24 +08:00
leoleoasd
2020-07-17 02:36:37 +08:00
看上去, 除了语义, 没啥不同?
leoleoasd
2020-07-17 02:36:56 +08:00
"There are no performance that can be gained if you'll rearrange LEFT JOINs to RIGHT."
hemingyang
2020-07-17 08:57:04 +08:00
不是 用 on 吗 为啥 你写 where
liujavamail
2020-07-17 13:09:33 +08:00
所以 left join 或者 right join 只是查询数据范围大小不一样,但加了 where 的条件之后, 最后都是一样的结果, 可以这么理解吗?

就是如果 左表数据少,就用 left join, 右表数据少,就用 right join
saulshao
2020-07-17 15:50:23 +08:00
我以前似乎是左表数据多采用 left join.....反之用 right join...
但是假如你是真的要求交集,#11 的说法是正确的
zhangysh1995
2020-07-19 17:55:47 +08:00
@git00ll
一句回答:A LEFT JOIN B 和 B RIGHT JOIN A 是等价的。LEFT JOIN 保留左表所有行,RIGHT JOIN 保留右边所有行。
我自己刚写过一篇理解 JOIN 的文章,欢迎赏个脸 https://zhuanlan.zhihu.com/p/157249501

@hemingyang
WHERE 和 ON 在 JOIN 情况下没有什么区别。SQL Server 不太了解,在 MySQL 里面,ON 部分的条件也可以写 WHERE 里面,但是习惯是 ON 写 JOIN 条件,WHERE 写结果条件。文档如下:

The search_condition used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set.
JasonLaw
2020-07-19 22:40:24 +08:00
@zhangysh1995 #13

你说“WHERE 和 ON 在 JOIN 情况下没有什么区别”,你是说 inner join 吗?如果是的话,你说得没错,但是我想更加明确地说出,where 和 on 在 outer join 的表现是有区别的。

在“Database System Concepts 6th edition - Chapter 4 Intermediate SQL - 4.1 Join Expressions - 4.1.1 Join Conditions”中,说了“However, there are two good reasons for introducing the on condition. First, we shall see shortly that for a kind of join called an outer join, on conditions do behave in a manner different from where conditions. Second, an SQL query is often more readable by humans if the join condition is specified in the on clause and the rest of the conditions appear in the where clause.”。之后在“4.1.2 Outer Joins”中,它说“As we noted earlier, on and where behave differently for outer join. The reason for this is that outer join adds null-padded tuples only for those tuples that do not contribute to the result of the corresponding inner join. The on condition is part of the outer join specification, but a where clause is not.”。

我这里用例子演示一下 on 和 where 的区别吧。
create table t1(c1 int);
create table t2(c2 int);
insert into t1 values(1),(2),(3);
insert into t2 values(1);

select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c2; 的结果为
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
| 2 | NULL |
| 3 | NULL |
+------+------+

select t1.*, t2.* from t1 left join t2 on true where t1.c1 = t2.c2; 的结果为
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
JasonLaw
2020-07-19 22:51:55 +08:00
@no1xsyzy #6
@leoleoasd #9

虽然说 a left join b on a.bid = b.id 和 b right join a on a.bid = b.id 得到的结果是一样的,但是因为它们的 a 和 b 的顺序还是会造成处理的性能差异的,具体可以看看“Database System Concepts 6th edition - Chapter 12 Query Processing - 12.5 Join Operation”,因为内容是在太多了,在评论里讲不清楚。
zhangysh1995
2020-07-20 13:36:20 +08:00
@JasonLaw 我没有说 select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c2; 和 select t1.*, t2.* from t1 left join t2 on true where t1.c1 = t2.c2; 是一样的。很明显你给的两条并不是等价的。我指的是同一个条件放在 on 或者放在 where 是一样的,请不要曲解意思。

这两条是等价的。
select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c2;
select t1.*, t2.* from t1 left join t2 where t1.c1 = t2.c2;
JasonLaw
2020-07-20 14:08:20 +08:00
@zhangysh1995 你可以自己去执行一下,看看是不是等价的,如果是的话,麻烦分享一下语句,让我重现一下。

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/690715

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX