SQL 中的连接顺序重要吗? [英] Does the join order matter in SQL?

问题描述

不考虑性能，我会从下面的查询 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 <= bx 或 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 中的连接顺序重要吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！