ORA-00918: column ambiguously defined in SELECT *

34
votes

Getting ORA-00918: column ambiguously defined: running this SQL:

SELECT *
FROM
  (SELECT DISTINCT(coaches.id),
    people.*,
    users.*,
    coaches.*
  FROM "COACHES"
  INNER JOIN people ON people.id = coaches.person_id
  INNER JOIN users ON coaches.person_id = users.person_id
  LEFT OUTER JOIN organizations_users ON organizations_users.user_id = users.id
) WHERE rownum <= 25

Any suggestions please?

3
sqloracleora-00918
Don't think this is your problem, but DISTINCT isn't a function; should be DISTINCT coaches.id, .... – Alex Poole
The DISTINCT in this example will work just fine, but is quite confusing. The distinct will still be on the whole row. The parenthesis go with the column name and effectively do nothing. This is the same as writing "select distinct people.*, (coaches.id), ...". It is bad practice to have the parenthesis though, since it does make it confusing to look at. – Craig
@APC - not sure if that was aimed at me or the OP; I suspect me. I do know distinct is across all columns, but my comment was rushed and unhelpful - seems my reading was rushed too as I was (as @Craig feared) confused by the parentheses. You're both right, obviously *8-) – Alex Poole

3 Answers

57
votes

A query's projection can only have one instance of a given name. As your WHERE clause shows, you have several tables with a column called ID. Because you are selecting * your projection will have several columns called ID. Or it would have were it not for the compiler hurling ORA-00918.

The solution is quite simple: you will have to expand the projection to explicitly select named columns. Then you can either leave out the duplicate columns, retaining just (say) COACHES.ID or use column aliases: coaches.id as COACHES_ID.

Perhaps that strikes you as a lot of typing, but it is the only way. If it is any comfort, SELECT * is regarded as bad practice in production code: explicitly named columns are much safer.

11
votes

You have multiple columns named the same thing in your inner query, so the error is raised in the outer query. If you get rid of the outer query, it should run, although still be confusing:

SELECT DISTINCT
    coaches.id,
    people.*,
    users.*,
    coaches.*
FROM "COACHES"
    INNER JOIN people ON people.id = coaches.person_id
    INNER JOIN users ON coaches.person_id = users.person_id
    LEFT OUTER JOIN organizations_users ON organizations_users.user_id = users.id
WHERE
    rownum <= 25

It would be much better (for readability and performance both) to specify exactly what fields you need from each of the tables instead of selecting them all anyways. Then if you really need two fields called the same thing from different tables, use column aliases to differentiate between them.

2
votes

You can also see this error when selecting for a union where corresponding columns can be null. 

select * from (select D.dept_no, D.nullable_comment
                  from dept D
       union
               select R.dept_no, NULL
                 from redundant_dept R
)

This apparently confuses the parser, a solution is to assign a column alias to the always null column.

select * from (select D.dept_no, D.comment
                  from dept D
       union
               select R.dept_no, NULL "nullable_comment"
                 from redundant_dept R
)

The alias does not have to be the same as the corresponding column, but the column heading in the result is driven by the first query from among the union members, so it's probably a good practice.