Why is selecting specified columns, and all, wrong in Oracle SQL?

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

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 3

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 *, x in that case would fail if a column x is added to the table that previously didn't have it.

Answer 4

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 5

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;

Answer 6

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.