How to Resolve 'ORA-00918: column ambiguously defined' in SELECT * Queries?

*Troubleshooting "ORA-00918: column ambiguously defined" in SELECT Queries**

The error "ORA-00918: column ambiguously defined" commonly arises when running SELECT * queries against multiple joined tables containing columns with identical names. Oracle's database engine cannot determine which column you intend to retrieve when multiple tables share a column name.

Illustrative Example and Solution

Consider this SQL query:

<code class="language-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</code>

This query produces the error because multiple tables ( coaches, people, users) contain a column named "id".

To correct this, avoid using SELECT *. Instead, explicitly specify the columns you need:

Method 1: Direct Column Selection

This method directly selects the desired columns by their fully qualified names (table.column):

<code class="language-sql">SELECT coaches.id,
       people.name,
       users.username,
       coaches.title
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</code>

Method 2: Using Column Aliases

This approach assigns unique aliases to columns with duplicate names. This makes the selected columns unambiguous:

<code class="language-sql">SELECT coaches.id AS coach_id,
       people.name AS person_name,
       users.username AS user_username,
       coaches.title AS coach_title
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</code>

By using either of these methods, the ambiguity is removed, and the query will execute successfully. Always prefer explicit column selection over SELECT * in queries involving joins to prevent this common error and improve query readability and maintainability.

The above is the detailed content of How to Resolve 'ORA-00918: column ambiguously defined' in SELECT * Queries?. For more information, please follow other related articles on the PHP Chinese website!