// Oracle error reference

ORA-00918: column ambiguously defined

A column name in a join exists in more than one of the tables, and the statement doesn't say which one. Qualify it with a table alias.

Error code
ORA-00918
Category
Syntax and SQL statements
Error message
column ambiguously defined

What it means

When several tables in a query have a column with the same name, Oracle needs to know which one you mean. ORA-00918 is raised when an unqualified reference could belong to more than one table.

Newer releases name the column and tables: column_name: column ambiguously specified - appears in table_name_1 and table_name_2.

Common causes

  • An unqualified id, name or created_at in a join where both tables have it.
  • SELECT * in a subquery that joins tables with the same column names, then referencing the column outside.
  • A column in ORDER BY that appears twice in the select list from different tables.

How to fix it

  1. Prefix every column in a join with its table alias.

    SELECT e.employee_id, d.department_name, e.manager_id
    FROM hr.employees e
    JOIN hr.departments d ON d.department_id = e.department_id;
  2. Give duplicate column names distinct aliases in subqueries.

Related errors

More syntax and sql statements errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.