我应该对多个表上的复杂联接使用sql JOIN关键字吗? [英] Should I use the sql JOIN keyword for complex joins on multiple tables?

问题描述

我有以下要求:

select * 
    from tbA A, tbB B, tbC C, tbD D
where 
    A.ID=B.ID and B.ID2= C.ID2 and A.ID=D.ID and C.ID3=D.ID3 and B.ID4=D.ID4
and
    A.Foo='Foo'

我已经多次听说此连接语法已被弃用，我应该改用'JOIN'关键字.

I've heard several times that this join syntax is depreciated, and that I should use the 'JOIN' keyword instead.

在如此复杂的联​​接中(多个表在属于不同表的多个列上联接)，我该怎么做?您是否认为此最佳做法仍然适用于此?

How do I do that in such a complicated join (multiple tables joined on multiple columns belonging to different tables)? Do you think this best practice still applies here ?

推荐答案

这是一个品味问题，但我更喜欢JOIN关键字.它使逻辑更清晰，并且与它附带的LEFT OUTER JOIN语法更加一致.请注意，您还可以使用INNER JOIN，它与JOIN是同义的.

It's a matter of taste, but I like the JOIN keyword better. It makes the logic clearer and is more consistent with the LEFT OUTER JOIN syntax that goes with it. Note that you can also use INNER JOIN which is synonymous with JOIN.

语法是

   a JOIN b
    ON expression relating b to all of the tables before

b本身可以是一个联接.对于内部联接来说，这无关紧要，但是对于外部联接，您可以像这样控制联接的顺序:

b can be a join itself. For inner joins it doesn't matter, but for outer you can control the order of the joins like this:

select * from
   a left join
      d join c
      on d.i = c.i
   on a.k = d.k 

这里a左连接到d和c之间的内部连接.

Here a is left-joined to the inner join between d and c.

这是您的查询:

select * 
    from tbA A
    join tbB B on A.ID = B.ID
    join tbC C on B.ID2 = C.ID2
    join tbD D on A.ID = D.ID and C.ID3 = D.ID3 and B.ID4 = D.ID4
where 
    A.Foo='Foo'

这篇关于我应该对多个表上的复杂联接使用sql JOIN关键字吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！