英文:
What is this "nested JOIN" syntax without a subquery called?
问题
在SQL Server(T-SQL)中,我可以连接到子查询,但我刚刚了解到我也可以连接到一个嵌套的JOIN表达式,而不是子查询。例如:
SELECT *
FROM dbo.Customer c
JOIN ( /* 注意没有SELECT */
dbo.Table2 t2
JOIN dbo.Table3 t3 ON t3.t2Id = t2.Id
) ON c.t2Id = t2.Id /* 注意没有别名 */
WHERE c.CustomerId = 1234;
这个"嵌套JOIN而没有子查询"的语法叫做什么,我可以在Microsoft文档中找到它在哪里?它在joins或subqueries的上下文中没有提到。
英文:
In SQL Server (T-SQL), I can JOIN to a subquery of course, but I just learned I can also JOIN to a nested JOIN expression that isn't a subquery. For example:
SELECT *
FROM dbo.Customer c
JOIN ( /* note no SELECT */
dbo.Table2 t2
JOIN dbo.Table3 t3 ON t3.t2Id = t2.Id
) ON c.t2Id = t2.Id /* note no alias */
WHERE c.CustomerId = 1234;
What is this "nested JOIN without subquery" syntax called and where can I find it in the Microsoft documentation? It isn't mentioned in the context of joins or of subqueries.
答案1
得分: 6
不是派生表。不是子查询。不是临时表。它的作用是逻辑上定义连接的执行顺序。在内连接中,它几乎没有什么用处。真正有用的地方是在外连接中使用。
一个示例用例可能是“所有订单左连接到缺货产品”,但中间有一个连接表。这可以编写为:
...
FROM Order O
LEFT JOIN (
OrderDetail OD
JOIN Product P
ON P.ProductId= OD.ProductId
AND P.IsBackOrdered = 1 -- 不是正确的设计,但这只是一个示例
)
ON OD.OrderId = O.OrderId
这允许在馈送到左连接之前合并和过滤订单详细信息和产品行。
括号实际上是可选的,但我更喜欢包括它们,以提高可读性(向读者暗示有一些复杂的操作正在进行)。
但至于“它叫什么?”我不认为它有一个特定的名称。我把它看作是“分组连接”或“无序连接”。这几乎就像是HP计算器的逆波兰表示法(RPN),在HP-35和后继型号的时代使用过。
英文:
Not a derived table. Not a subquery. Not a temp table. What it does is logically defines the order that the joins are performed. It has little utility with inner joins. Where it really comes in useful is when used with outer joins.
An example use case might be "all orders left join
to back-ordered products", but where there is a junction table in between. This can be coded as:
...
FROM Order O
LEFT JOIN (
OrderDetail OD
JOIN Product P
ON P.ProductId= OD.ProductId
AND P.IsBackOrdered = 1 -- Not proper design, but it's just an example
)
ON OD.OrderId = O.OrderId
This allows the order-detail and product rows to be combined and filtered before being fed to the LEFT JOIN.
The parenthesis are actually optional, but I prefer to include them to somewhat improve readability (giving the reader a hint that something tricky is going on).
But as to "what is it called?" I don't think it has a name. I think of it as grouped joins or out-of-order joins. It's almost like the Reverse Polish Notation (RPN) of HP calculators back in the days of the HP-35 and successors.
答案2
得分: 1
在Microsoft文档中可以找到它在FROM
子句中(正如@mustaccio在这个交叉站点重复中指出的)。
你的FROM
子句是
FROM dbo.Customer c
JOIN ( dbo.Table2 t2
JOIN dbo.Table3 t3
ON t3.t2Id = t2.Id )
ON c.t2Id = t2.Id
注意joined_table
的语法是
<joined_table> ::=
{
<table_source> <join_type> <table_source> ON <search_condition>
....
| [ ( ] <joined_table> [ ) ]
}
所以下面是一个有效的<joined_table>
dbo.Table2 t2
JOIN dbo.Table3 t3
ON t3.t2Id = t2.Id
上面的语法还显示了可以应用于<joined_table>
的可选括号,以生成另一个有效的<joined_table>
,因此这也是一个
( dbo.Table2 t2
JOIN dbo.Table3 t3
ON t3.t2Id = t2.Id )
并且正如在文档页面上更高处所示,<joined_table>
也是一个有效的<table_source>
。
所以你只是在做
SELECT *
FROM dbo.Customer c
JOIN <joined_table>
ON c.t2Id = t2.Id
并且,有点递归地,使用一个<joined_table>
作为一个<table_source>
来生成进一步的<joined_table>
。
英文:
> where can I find it in the Microsoft documentation?
In the FROM
clause (as pointed out by @mustaccio in this cross site dupe).
Your FROM
clause is
FROM dbo.Customer c
JOIN ( dbo.Table2 t2
JOIN dbo.Table3 t3
ON t3.t2Id = t2.Id )
ON c.t2Id = t2.Id
Note the grammar for joined_table
is
<joined_table> ::=
{
<table_source> <join_type> <table_source> ON <search_condition>
....
| [ ( ] <joined_table> [ ) ]
}
So the following is a valid <joined_table>
dbo.Table2 t2
JOIN dbo.Table3 t3
ON t3.t2Id = t2.Id
The above grammar also shows the optional parentheses that can be applied to a <joined_table>
to produce another valid <joined_table>
so this is also one
( dbo.Table2 t2
JOIN dbo.Table3 t3
ON t3.t2Id = t2.Id )
And also as shown further up in the grammar on that documentation page <joined_table>
is also a valid <table_source>
.
So you are just doing
SELECT *
FROM dbo.Customer c
JOIN <joined_table>
ON c.t2Id = t2.Id
and, somewhat recursively, using a <joined_table>
as a <table_source>
to produce a further <joined_table>
.
答案3
得分: 0
括号表示一个表达式,它们意味着你肯定希望服务器在外部连接表达式之前完成嵌套的连接表达式。对于这个查询,在这里一切都是内连接,与传统方式列出连接没有区别(只需注意如何编写ON
子句):
SELECT *
FROM dbo.Customer c
JOIN dbo.Table2 t2 ON c.t2Id = t2.Id
JOIN dbo.Table3 t3 on t3.t2Id = t2.Id
WHERE c.CustomerId = 1234;
以上查询将产生相同的结果,并且可以说更容易阅读和维护。
然而,在括号表达式中使用OUTER JOIN
是可能的,以一种比没有括号的查询更容易编写和理解的方式构建结果。
英文:
The parentheses are a table expression, and they mean you definitely want the server to complete that nested JOIN expression before the outer JOIN expression. For this query, where everything is an inner join, it's no different than listing the JOINs in the traditional way (just be careful of how you write the ON
clauses):
SELECT *
FROM dbo.Customer c
JOIN dbo.Table2 t2 ON c.t2Id = t2.Id
JOIN dbo.Table3 t3 on t3.t2Id = t2.Id
WHERE c.CustomerId = 1234;
The above query will give the same results, and is arguably much easier to read and maintain.
However, it's possible to use an OUTER JOIN
inside the parenthetical expression to build the results in ways that are easier to write and understand than a query without parentheses.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论