WHERE子句中的逻辑处理顺序或SQL标准 [英] Logical Processing Order or SQL Standard in WHERE clause
问题描述
几天前，有人问我有关SELECT语句的逻辑处理顺序，更具体地说，是有关别名和where子句的信息，我不确定一个问题.如果我们有这样的查询:
SELECT name AS first_name
FROM people
WHERE first_name = 'Alan';
在WHERE子句中使用别名会产生错误的原因实际上是SELECT语句的逻辑处理顺序，或者是语法解析问题，或者可能是来自SQL标准的规则?
这是SQL标准的规则(这很复杂，因为它涉及到很多SQL用户可能不会想到的细节)./p>
该规则背后有两个原则.首先是该标准不强加操作顺序，除非在逻辑上必要时(例如，having子句必须在group by之后进行逻辑处理).这是SQL概念的基础，SQL是一种描述性语言，用于描述结果.任何特定的数据库引擎都可以确定其自己的执行路径.
第二个原则是避免歧义.这是作用域规则的用处，它定义了SQL编译器何时知道什么.
考虑以下语句:
select a as b, b as a, a + 1 as d
-----------------------^
from t
问题是:a+1是指哪个a，是表中的列a还是select中的列b(别名为a).根据标准，这是明确的.列别名在定义它们的select子句中未知.
这也扩展到了where子句，该子句在相同的范围内进行评估.考虑相同的示例:
select a as b, b as a, a + 1 as d
from t
where a > 100
where条件指的是哪个a?该标准是明确的. where子句不能理解select中的列别名.这是因为select是在where之后(逻辑上)求值的.所以，当你说:
select row_number() over (order by a) as seqnum
from t
where a > 100
返回的值从第一个a 之后的100开始.枚举不会首先发生，因为过滤后的行会获得被过滤掉的序列号.
I was asked couple days ago about logical processing order of the SELECT statement and more specifically about aliases and where clause and I'm not sure about one issue. If we have a query like:
SELECT name AS first_name
FROM people
WHERE first_name = 'Alan';
The reason why usage of aliases in WHERE clause will generate error is really logical processing order of the SELECT statement, or rather syntax parsing issue, or maybe is the rule from SQL standard?
It is the rule from the SQL standard (which is rather complicated because it goes into lots of details that users of SQL probably don't think about).
There are two principles behind the rule. The first is that the standard does not impose an ordering of operations, except when logically necessary (a having clause, for instance, has to logically processed after a group by). This is the basis of the notion at SQL is a descriptive language, where the results are described. Any particular database engine can determine its own execution paths.
The second principle is to avoid ambiguity. This is where scoping rules come in, that define what a SQL compiler knows when.
Consider the following statement:
select a as b, b as a, a + 1 as d
-----------------------^
from t
The question is: which a does a+1 refer to, the column a in the table or the column b (which is aliased as a) in the select. According to the standard this is unambiguous. Column aliases are not known in the select clause where they are defined.
This extends to the where clause as well, which is evaluated in the same scope. Consider the same example:
select a as b, b as a, a + 1 as d
from t
where a > 100
Which a does the where condition refer to? The standard is unambiguous. The where clause does not understand column aliases in the select. This is because the select is (logically) evaluated after the where. So, when you say:
select row_number() over (order by a) as seqnum
from t
where a > 100
The value returned starts with the first a after 100. The enumeration does not happen first, with filtered rows getting sequence numbers that are filtered out.
这篇关于WHERE子句中的逻辑处理顺序或SQL标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
