ORA-00918: column ambiguously defined in SELECT *

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.

Leave a Comment