SQL选择与ON子句移动到查询结束 [英] SQL select with ON clause moved to end of query

问题描述

嗨


我有一个存储过程，我正在尝试理解哪些有一些sql（我使用的是sql server，所以tsql），它使用了一种不寻常的语法。或者至少我之前从未遇到过的语法。

我很困惑的特定部分是更新声明。此更新语句有一个where子句，其中包含一些条件，其中一个是NOT EXISTS（）。此NOT EXISTS（）中的子查询是一个select语句，它有四个连接到四个不同的表。其中一个连接的ON子句看起来与我通常期望的位置不合适。它已被移动到select语句的底部。希望下面的代码能够解决问题。



这是加入四个表时使用的普通语法

  -  ON子句的正常位置
从表1中选择* a 
全外连接表a b on a.id = b.id 
全外连接表3 c on b.id = c。 id 
全外连接Table4 d on c.id = d.id 

这是我在select语句中看到的语法

  - 第二个ON子句移动到查询结束
 select * from Table1 a 
 full outer join table2 b on a.id = b.id 
 full outer join表3 c 
全外连接表4 d on c.id = d.id 
 on b.id = c.id 

我想知道有什么区别？在这个模拟示例中，我得到了上述两个查询返回的相同结果，但我正在调查的实际存储过程确实显示返回结果的差异，具体取决于ON子句的位置。



任何帮助表示感谢。



问候，J

解决方案

它改变了加入的顺序。



这个：

 选择 * 来自表1 a 
 完整 外部  join 表2 b   a.id = b.id 
 完整 外部  join 表3 c   b.id = c.id 
 完整 外部 Ĵ oin 表4 d  on  c.id = d.id 

相当于

 选择 * 
  FROM  
（
（
表1 a 完整 外部  join 表2 b  on  a.id = b.id 
）
 完整 外部  join 表3 c  on  b.id = c.id 
） 
 完整 外部 加入表4 d   c.id = d.id 

当

 选择 * 来自表1 a 
 完整 < span class =code-keyword> outer   join 表2 b   a.id = b.id 
 完整 外部  join 表3 c 
 完整 外部  join 表4 d  on  c.id = d.id 
  on  b.id = c.id 

equals < pre lang =SQL> 选择 *
FROM
（
（
表1 a 完整 外部 join 表2 b on a.id = b.id
）
完整 外部 加入
（
表3 c 全 外部 join 表4 d c .id = d.id
） on b.id = c.id
）

这无关紧要当你进行内部连接时，在这种情况下，优化器也可以重新排序连接以提高效率。

外部连接不是这种情况。

Hi
I have a stored procedure that i'm trying to understand which has some sql (i'm using sql server, so tsql) which uses an unusual syntax. Or at least syntax that I have never come across before.
The particular section that i'm confused about is an update statement. This update statement has a where clause with a few conditions one of which is an NOT EXISTS(). The sub-query within this NOT EXISTS() is a select statement with four joins to four different tables. The ON clause of one of the joins looks to be out of place to where I would normally expect it to be. It has been moved to the bottom of the select statement. Hopefully the code below will communicate the issue.

This is the "normal" syntax used when joining four tables

-- Normal location for ON clauses 
select * from Table1 a
full outer join Table2 b on a.id=b.id 
full outer join Table3 c on b.id=c.id 
full outer join Table4 d on c.id=d.id 

This is the syntax I see in the select statement

-- Second ON clause moved to end of query
select * from Table1 a
full outer join Table2 b on a.id=b.id 
full outer join Table3 c 
full outer join Table4 d on c.id=d.id 
on b.id=c.id 

I'm wondering what the difference is? In this mock example I'm getting the same results returned for both queries above but the actual stored procedure i'm investigating does display a difference in the results returned depending on the location of the ON clause.

Any help appreciated.

Regards, J

解决方案

It changes the order of joining.

This:

select * from Table1 a
full outer join Table2 b on a.id=b.id 
full outer join Table3 c on b.id=c.id 
full outer join Table4 d on c.id=d.id 

is equivalent to

select *
FROM
(
    (
        Table1 a full outer join Table2 b on a.id=b.id
    )
    full outer join Table3 c on b.id=c.id
)
full outer join Table4 d on c.id=d.id 

While

select * from Table1 a
full outer join Table2 b on a.id=b.id 
full outer join Table3 c 
full outer join Table4 d on c.id=d.id 
on b.id=c.id 

equals

select *
FROM
(
    (
        Table1 a full outer join Table2 b on a.id=b.id
    )
    full outer join 
    (
        Table3 c full outer join Table4 d on c.id=d.id 
    ) on b.id=c.id
)

This doesn't matter when you are doing inner joins, and in that case the optimizer may also reorder the joins for efficiency.
This is not the case for outer joins.

这篇关于SQL选择与ON子句移动到查询结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！