联接顺序在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 a.x <= b.x或ON a.x = 7或ON a.x LIKE b.x或ON (a.x, a.y) = (b.x, b.y))一起使用，并且两个查询仍然相等.

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()之类的null相关的函数，例如，如果条件为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屋！