英文:
COLUMN IN SQL ORDER BY CLAUSE NOT PRESENT IN SELECT STATEMENT
问题
根据SQL执行顺序，ORDER BY 在SELECT语句之后执行。然而，我们可以使用在SELECT语句中不存在的列来对结果进行排序。这是否与执行顺序相矛盾？在SELECT语句之后使用了一个不存在的列进行排序？期待获得解释。
英文:
As per the SQL order of execution, ORDER BY gets executed at the end, after the select statement. However, we are able to order by the result using a column that is not present in the SELECT statement. Isn't that contradictory to the order of execution? A column that doesn't exist after the SELECT Statement is being used to order by?
Looking to get an explanation
答案1
得分: 1
如果您在ORDER BY中使用未在SELECT中提及的内容，查询规划器基本上会将其作为虚拟列放入SELECT中。这就是为什么按照未在SELECT中选择的表达式（列或公式）进行排序能够工作的原因。
请记住，SQL是一种声明性语言，可能是我们大多数程序员使用的唯一声明性语言。我们告诉SQL我们想要什么，而不是如何获得它。查询规划器需要确定以下两点：
a）我们所要求的是否合法且可能。
b）如何获得它。
我们程序员通过其他编程语言受过过程式思维的训练。我们不会告诉计算机我们想要什么，而是告诉它如何获得它。但这种训练对SQL并不真正有用。（对于优化查询来说是有用的，但优化始于查看由EXPLAIN显示的查询计划。）
英文:
If you ORDER BY something that isn't mentioned in SELECT, the query planner basically puts it into the SELECT as a phantom column. That's why ordering by a non-SELECTed expression (column or formula) works.
Remember, SQL is a declarative language, probably the only declarative language most of we programmers use. We tell SQL what we want, not how to get it. It's up to the query planner to figure out
a) whether what we asked for is well-formed and possible.
b) how to get it.
We programmers are trained, by other languages, to think procedurally. We don't tell the machine what we want, we tell it how to get it.
But that training isn't really useful for SQL. (It is useful for optimizing queries, but optimization starts with looking at the query plan revealed by EXPLAIN.)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论