让我们考虑SQL标准,按照此处规定的第7.9节<查询规范>:
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
<query specification> ::=
SELECT [ <set quantifier> ] <select list> <table expression>
[...]
<select list> ::=
<asterisk>
| <select sublist> [ { <comma> <select sublist> }... ]
[...]
Syntax Rules
1) Let T be the result of the <table expression>.
3) Case:
a) [...]
b) Otherwise, the <select list> "*" is equivalent to a <value
expression> sequence in which each <value expression> is a
<column reference> that references a column of T and each
column of T is referenced exactly once. The columns are ref-
erenced in the ascending sequence of their ordinal position
within T.
因此,换句话说,是的,SQL标准规定列应按其在T中的序号进行投影。请注意,当您的由涉及JOIN .. USING或NATURAL JOIN子句的多个表组成时,事情会变得有些棘手。然而,在选择一个简单表时,您可能可以假设顺序如预期那样。为了完整起见,表中的序号位置的含义在11.4 中进一步解释:
General Rules
5) [...] The ordinal position included
in the column descriptor is equal to the degree of T. [...]
接着在 11.11 <add column definition>
中(用于 ALTER TABLE
语句)
General Rules
4) [...] In particular, the degree of T
is increased by 1 and the ordinal position of that column is
equal to the new degree of T as specified in the General Rules
of Subclause 11.4, "<column definition>".
还有很多其他的SQL语句和子句依赖于在
ordinal positions
13.8 <insert statement>
(when omitting the `<insert column list>`)
20.2 <direct select statement: multiple rows>
(when `<sort specification>` contains an `<unsigned integer>`)
特别是Postgres,在标准方面非常兼容,所以如果你真的想要使用SELECT *
,那就去吧!
内部的的正式规范。以下是一些例子: