Why is selecting specified columns, and all, wrong

Question

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 animals is 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.

select is_parent, * from animals

This returns 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 animals table to get it to work ( select is_parent, a.* from animals ani), but why must Oracle need a table alias to be able to work out the select?

Answer 1

The use case for the alias.* format is as follows

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.

Answer 2

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.

Answer 3

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.

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.

Another for the "buy why!!!" bucket, along with why can't you group by an alias?

Answer 4

There is no merit in doing this in production code. We should explicitly name the columns we want rather than using the SELECT * construct.

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.

Answer 5

why must Oracle need a table alias to be able to work out the select

Teradata is requiring the same. As both are quite old (maybe better call it mature :-) DBMSes this might be historical reasons.

My usual explanation is: an unqualified * means everything/all columns and the parser/optimizer is simply confused because you request more than everything.

Answer 6

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.

Add an alias and the thing works.

sql>select a.geometrie.SDO_GTYPE from ngg_basiscomponent a;