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
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,nameorcreated_atin 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 BYthat appears twice in the select list from different tables.
How to fix it
-
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;
-
Give duplicate column names distinct aliases in subqueries.
Related errors
ORA-00904invalid identifier209SQL Server: Ambiguous column name1052MySQL: Column is ambiguous42702PostgreSQL: column reference is ambiguous
More syntax and sql statements errors
ORA-00933SQL command not properly endedORA-00936missing expressionORA-00907missing right parenthesisORA-00911invalid characterORA-00917missing commaORA-00923FROM keyword not found where expectedORA-00937not a single-group group functionORA-00979not a GROUP BY expression
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
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.