为什么在 Oracle SQL 中选择指定的列以及所有列都是错误的？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2315295/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is selecting specified columns, and all, wrong in Oracle SQL?
提问by glasnt
Say I have a select statement that goes..
假设我有一个选择语句
select * from animals
That gives a a query result of all the columns in the table.
这给出了表中所有列的查询结果。
Now, if the 42nd column of the table animalsis is_parent, and I want to return that in my results, just after gender, so I can see it more easily. But I also want all the other columns.
现在，如果表的第 42 列animals是is_parent，并且我想在我的结果中返回它，就在 之后gender，这样我就可以更容易地看到它。但我也想要所有其他列。
select is_parent, * from animals
This returns ORA-00936: missing expression.
这返回ORA-00936: missing expression.
The same statement will work fine in Sybase, and I know that you need to add a table alias to the animalstable to get it to work ( select is_parent, a.* from animals ani), but whymust Oracle need a table alias to be able to work out the select?
相同的语句在 Sybase 中也能正常工作，我知道您需要向表中添加表别名animals才能使其工作 ( select is_parent, a.* from animals ani)，但为什么Oracle 必须需要表别名才能计算出选择？
回答by Jim Hudson
Actually, it's easy to solve the original problem. You just have to qualify the *.
其实，原来的问题很容易解决。你只需要限定 *.
select is_parent, animals.* from animals;
should work just fine. Aliases for the table names also work.
应该工作得很好。表名的别名也有效。
回答by APC
There is no merit in doing this in production code. We should explicitly name the columns we want rather than using the SELECT * construct.
在生产代码中这样做没有任何好处。我们应该明确命名我们想要的列，而不是使用 SELECT * 构造。
As for ad hoc querying, get yourself an IDE - SQL Developer, TOAD, PL/SQL Developer, etc - which allows us to manipulate queries and result sets without needing extensions to SQL.
至于即席查询，为自己准备一个 IDE——SQL Developer、TOAD、PL/SQL Developer 等——它允许我们在不需要 SQL 扩展的情况下操作查询和结果集。
回答by Nick Pierpoint
Lots of good answers so far on why select *shouldn't be used and they're all perfectly correct. However, don't think any of them answer the original question on why the particular syntax fails.
到目前为止，有很多关于为什么select *不应该使用的好答案，而且它们都是完全正确的。但是，不要认为它们中的任何一个都回答了有关为什么特定语法失败的原始问题。
Sadly, I think the reason is... "because it doesn't".
可悲的是，我认为原因是......“因为它没有”。
I don't think it's anything to do with single-table vs. multi-table queries:
我认为这与单表与多表查询无关：
This works fine:
这工作正常：
select *
from
person p inner join user u on u.person_id = p.person_id
But this fails:
但这失败了：
select p.person_id, *
from
person p inner join user u on u.person_id = p.person_id
While this works:
虽然这有效：
select p.person_id, p.*, u.*
from
person p inner join user u on u.person_id = p.person_id
It might be some historical compatibility thing with 20-year old legacy code.
这可能是与 20 年历史遗留代码的历史兼容性问题。
Another for the "buy why!!!" bucket, along with why can't you group by an alias?
另一个是“买为什么！！！” 存储桶，以及为什么不能按别名分组？
回答by Rene
Good question, I've often wondered this myself but have then accepted it as one of those things...
好问题，我自己经常想知道这个问题，但后来接受了它作为其中之一......
Similar problem is this:
类似的问题是这样的：
sql>select geometrie.SDO_GTYPE from ngg_basiscomponent
ORA-00904: "GEOMETRIE"."SDO_GTYPE": invalid identifier
where geometrie is a column of type mdsys.sdo_geometry.
其中 geometrie 是 mdsys.sdo_geometry 类型的列。
Add an alias and the thing works.
添加一个别名，事情就可以工作了。
sql>select a.geometrie.SDO_GTYPE from ngg_basiscomponent a;
回答by dnoeth
whymust Oracle need a table alias to be able to work out the select
为什么Oracle 需要一个表别名才能计算出选择
Teradata is requiring the same. As both are quite old (maybe better call it mature:-) DBMSes this might be historical reasons.
Teradata 也有同样的要求。由于两者都相当古老（也许最好称其为成熟:-) DBMS，这可能是历史原因。
My usual explanation is: an unqualified *means everything/all columnsand the parser/optimizer is simply confused because you request more than everything.
我通常的解释是：不合格*意味着一切/所有列，解析器/优化器只是混淆，因为您要求的不仅仅是一切。
回答by jwenting
Select *in the real world is only dangerous when referring to columns by index number after retrieval rather than by name, the bigger problem is inefficiency when not all columns are required in the resultset (network traffic, cpu and memory load).
Of course if you're adding columns from other tables (as is the case in this example it can be dangerous as these tables may over time have columns with matching names, select *, xin that case would fail if a column x is added to the table that previously didn't have it.
Select *在现实世界中，只有在检索后按索引号而不是按名称引用列时才危险，当结果集中并非所有列（网络流量、cpu 和内存负载）都需要时，更大的问题是效率低下。当然，如果您从其他表中添加列（就像本例中的情况一样，这可能很危险，因为这些表可能会随着时间的推移具有名称匹配的列，select *, x在这种情况下，如果将列 x 添加到以前没有。
回答by Gary Myers
The use case for the alias.* format is as follows
alias.* 格式的用例如下
select parent.*, child.col
from parent join child on parent.parent_id = child.parent_id
That is, selecting all the columns from one table in a join, plus (optionally) one or more columns from other tables.
也就是说，从连接中的一个表中选择所有列，加上（可选）其他表中的一列或多列。
The fact that you can use it to select the same column twice is just a side-effect. There is no real point to selecting the same column twice and I don't think laziness is a real justification.
您可以使用它两次选择同一列的事实只是一个副作用。两次选择同一列没有任何意义，我认为懒惰不是真正的理由。
