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

问题描述

不管性能，我会从下面的查询 A 和 B 中得到相同的结果吗?C和D呢?

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>  

推荐答案

对于 INNER 连接，不，顺序无所谓.只要您将选择从 SELECT * 更改为 SELECT a.*, b.*, c.*，查询将返回相同的结果.

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.*.

对于 (LEFT, RIGHT 或 FULL) OUTER 连接，是的，顺序很重要 - 和 (更新)事情要复杂得多.

For (LEFT, RIGHT or FULL) OUTER joins, yes, the order matters - and (updated) things are much more complicated.

首先，外连接是不可交换的，所以 a LEFT JOIN b 和 b LEFT JOIN a

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

等价于:

a LEFT JOIN c 
    ON c.ac_id = a.ac_id
  LEFT JOIN b
    ON b.ab_id = a.ab_id

但是:

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

不等同于:

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

<小时>

另一个(希望更简单)关联性示例.把它想象成 (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

这个等同于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

只是因为我们有不错的"ON 条件.ON b.ab_id = a.ab_id 和 c.bc_id = b.bc_id 都是相等检查，不涉及 NULL 比较.

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.

您甚至可以使用其他运算符或更复杂的运算符设置条件，例如:ON ax <= bx 或 ON ax = 7 或 ON ax LIKE bx 或 ON (ax, ay) = (bx, by) 两个查询仍然是等价的.

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.

但是，如果其中任何一个涉及 IS NULL 或与 COALESCE() 等空值相关的函数，例如条件是 b.ab_id IS NULL，那么这两个查询就不等价了.

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屋！