无论性能如何,我都会从以下查询A和B中都能得到同样的结果吗?C和D呢? 0 c, _5 H+ k' ?8 ` Z& _" }, ~-- Aselect *from a left join b on <strong> left join c on <strong>-- Bselect *from a left join c on <strong> left join b on <strong>-- Cselect *from a join b on <strong> join c on <strong>-- Dselect *from a join c on <strong> join b on <strong> / B* C' G3 e' l9 K, f2 M; ?解决方案: 2 _$ X4 g( H7 R/ u: B1 c1 R8 v
对于INNER连接,不,顺序无关紧要。只要你改变选择,查询将返回相同的结果SELECT *来SELECT a.*,b.*,c.*。' a5 N; r8 r$ u
对于(LEFT,RIGHT或FULL)OUTER连接,是的,顺序是有意义的-和( 更新 )事情要复杂得多。 8 Y) Y* m/ O( B8 W: f6 m首先,外部连接不可交换,因此a LEFT JOIN b与b LEFT JOIN a4 j) x! E! c1 y& J# |# c* R
外部连接不是相关的,所以在你的例子中同时涉及两个属性可交换性和相关性): 4 [' E4 R( r9 U" ra LEFT JOIN b ON b.ab_id = a.ab_id LEFT JOIN c ON c.ac_id = a.ac_id等效于 : % T6 f0 D! }! l" x. S, c- q! Ya LEFT JOIN c ON c.ac_id = a.ac_id LEFT JOIN b ON b.ab_id = a.ab_id但:0 _, b) L7 h& k. p
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不等同于 : & O, P# s% X9 a7 G! s% F1 Ga 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:3 Y+ g# q9 I: r u; n* C. _1 |& o
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):/ h; }0 A" p3 _+ a& j- I5 T
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比较。, G J* {. n# l8 ^7 d
你甚至可以与其他运算符或更复杂的运算符(例如:ON a.x 1 M. F$ V/ o4 n6 d! D/ a' X E
但是,如果其中任何一个涉及到IS NULL或与空值有关的函数COALESCE()(例如,如果条件是)b.ab_id ISNULL,这两个查询将不相等。