奇怪/深奥的连接语法 [英] Strange / esoteric join syntax

问题描述

已向我提供了此旧SQL代码(表名已更改)以进行复制，并且JOIN语法不是我以前见过的，并且很难在Google上找到:

I've been provided this old SQL code (table names changed) to replicate, and the JOIN syntax isn't something I've seen before, and is proving hard to google:

select <stuff>

from A

inner join B
on A.ID = B.A_ID

inner join C  -- eh? No ON?

inner join D
ON C.C_ID = D.C_ID

ON B.C_ID = D.C_ID -- a second ON here? what?

当我看到代码时，我以为我将收到破损的代码，并且它将无法运行.

When I saw the code, I assumed I'd be sent broken code and it wouldn't run.

但是确实如此. (Sql Server 2012)

But it does. (Sql Server 2012)

它是做什么的?有没有更明智/更标准的写作方式?这是怎么回事?

What does it do? Is there a more sensible / standard way of writing it? What's happening here?

推荐答案

虽然不寻常，但这是完全有效的tsql.通常，当您对一组内部关联的相关表进行外部联接时，您会看到这种方法.更好的恕我直言，写这是:

While unusual, this is perfectly valid tsql. Typically you see this approach when you have an outer join to a set of related tables which are inner joined to one another. A better IMHO way to write this would be:

inner join B
   on A.ID = B.A_ID
inner join (C inner join D ON C.C_ID = D.C_ID) 
   ON B.C_ID = D.C_ID 

这使连接逻辑清晰明了-同时也对读者有所帮助.此外，它还使读者知道开发人员有意这样做.因此，让这成为不良编码的示例.评论不寻常的事情.解释一下.请某人定期检查您的代码，以帮助改善您的样式和用法.

This makes the join logic clear - and it also helps the reader. Additionally, it lets the reader know that the developer did this intentionally. So let this be an example of poor coding. Comment things that are unusual. Explain things. Have someone review your code periodically to help improve your style and usage.

您可以通过在from子句中重新排列表的顺序来以典型"样式编写此文件-但我想当前版本使用实际的表名更具逻辑意义.

And you could write this in a "typical" style by rearranging the order of tables in the from clause - but I'll guess that the current version makes more logical sense with the real table names.

这篇关于奇怪/深奥的连接语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！