问题描述
不考虑性能,我会从下面的查询 A 和 B 得到相同的结果吗?C 和 D 怎么样?
-- A选择 *从左加入 b在<blahblah>左连接 c在 <blahblan>--乙选择 *从左连接 c在<blahblah>左连接 b在 <blahblan>- C选择 *从 a 连接 b在<blahblah>加入c在 <blahblan>-- D选择 *从连接 c在<blahblah>加入乙在 <blahblan>
对于 INNER
连接,不,顺序无关紧要.查询将返回相同的结果,只要您将选择从 SELECT *
更改为 SELECT a.*, b.*, c.*
.
对于 (LEFT
, RIGHT
或 FULL
) OUTER
连接,是的,顺序很重要 - 和 (更新)事情要复杂得多.
首先,外连接不是可交换的,所以a LEFT JOIN b
和 b LEFT JOIN a
外连接也不是关联的,因此在您的示例中同时涉及(交换性和关联性)属性:
a LEFT JOIN bON b.ab_id = a.ab_id左连接 cON c.ac_id = a.ac_id
等价于:
a LEFT JOIN cON c.ac_id = a.ac_id左连接 bON b.ab_id = a.ab_id
但是:
a LEFT JOIN bON b.ab_id = a.ab_id左连接 cON c.ac_id = a.ac_idAND c.bc_id = b.bc_id
不等同于:
a LEFT JOIN cON c.ac_id = a.ac_id左连接 bON b.ab_id = a.ab_idAND b.bc_id = c.bc_id
另一个(希望更简单)关联性示例.把它想象成 (a LEFT JOIN b) LEFT JOIN c
:
a LEFT JOIN bON b.ab_id = a.ab_id -- AB 条件左连接 cON c.bc_id = b.bc_id -- BC 条件
这个等价于a LEFT JOIN (b LEFT JOIN c)
:
左连接b 左连接 cON c.bc_id = b.bc_id -- BC 条件ON b.ab_id = a.ab_id -- AB 条件
只是因为我们有不错的"ON
条件.ON b.ab_id = a.ab_id
和 c.bc_id = b.bc_id
都是相等性检查,不涉及 NULL
比较.>
您甚至可以使用其他运算符或更复杂的条件,例如:ON ax 或
ON ax = 7
或 ON ax LIKE bx
或 ON (ax, ay) = (bx, by)
并且这两个查询仍然是等效的.
但是,如果其中任何一个涉及 IS NULL
或与 COALESCE()
等空值相关的函数,例如条件是 b.ab_id IS NULL
,则两个查询不等价.
Disregarding performance, will I get the same result from query A and B below? How about C and D?
-- A
select *
from a left join b
on <blahblah>
left join c
on <blahblan>
-- B
select *
from a left join c
on <blahblah>
left join b
on <blahblan>
-- C
select *
from a join b
on <blahblah>
join c
on <blahblan>
-- D
select *
from a join c
on <blahblah>
join b
on <blahblan>
For INNER
joins, no, the order doesn't matter. The queries will return same results, as long as you change your selects from SELECT *
to SELECT a.*, b.*, c.*
.
For (LEFT
, RIGHT
or FULL
) OUTER
joins, yes, the order matters - and (updated) things are much more complicated.
First, outer joins are not commutative, so a LEFT JOIN b
is not the same as b LEFT JOIN a
Outer joins are not associative either, so in your examples which involve both (commutativity and associativity) properties:
a LEFT JOIN b
ON b.ab_id = a.ab_id
LEFT JOIN c
ON c.ac_id = a.ac_id
is equivalent to:
a LEFT JOIN c
ON c.ac_id = a.ac_id
LEFT JOIN b
ON b.ab_id = a.ab_id
but:
a LEFT JOIN b
ON b.ab_id = a.ab_id
LEFT JOIN c
ON c.ac_id = a.ac_id
AND c.bc_id = b.bc_id
is not equivalent to:
a LEFT JOIN c
ON c.ac_id = a.ac_id
LEFT JOIN b
ON b.ab_id = a.ab_id
AND b.bc_id = c.bc_id
Another (hopefully simpler) associativity example. Think of this as (a LEFT JOIN b) LEFT JOIN c
:
a LEFT JOIN b
ON b.ab_id = a.ab_id -- AB condition
LEFT JOIN c
ON c.bc_id = b.bc_id -- BC condition
This is equivalent to a LEFT JOIN (b LEFT JOIN c)
:
a LEFT JOIN
b LEFT JOIN c
ON c.bc_id = b.bc_id -- BC condition
ON b.ab_id = a.ab_id -- AB condition
only because we have "nice" ON
conditions. Both ON b.ab_id = a.ab_id
and c.bc_id = b.bc_id
are equality checks and do not involve NULL
comparisons.
You can even have conditions with other operators or more complex ones like: ON a.x <= b.x
or ON a.x = 7
or ON a.x LIKE b.x
or ON (a.x, a.y) = (b.x, b.y)
and the two queries would still be equivalent.
If however, any of these involved IS NULL
or a function that is related to nulls like COALESCE()
, for example if the condition was b.ab_id IS NULL
, then the two queries would not be equivalent.
这篇关于SQL 中的连接顺序重要吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!