ORA-00918: column ambiguously specified
ORA-00918: DEPT_ID: column ambiguously specified - appears in andIn a join, a column name exists in more than one table and the query doesn't say which one.
Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-15
When two joined tables both have a column such as dept_id or name, a bare dept_id could mean either one. Oracle refuses to guess and raises ORA-00918. Prefix the column with the table alias (e.dept_id), and give same-named columns different aliases in the SELECT list.
23ai puts the column name in the message, for example ORA-00918: DEPT_ID: column ambiguously specified - appears in and. In this version (23.26) the table names after appears in came out blank in all three languages, so check which tables have the column with user_tab_columns.
Causes and fixes
1Columns in a join SELECT without table aliases
SELECT emp_id, name, dept_id FROM emp e JOIN dept d ... failed on DEPT_ID. user_tab_columns showed that both DEPT_ID and NAME exist in EMP and DEPT. emp_id is only in EMP, so it alone was not a problem.
Prefixing every column with its alias and renaming the department name (d.name AS dept_name) returned all 3 rows: KIM SALES, LEE IT, PARK SALES.
Setup
CREATE TABLE dept (dept_id NUMBER PRIMARY KEY, name VARCHAR2(20));
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), dept_id NUMBER REFERENCES dept);
INSERT INTO dept VALUES (10, 'SALES');
INSERT INTO dept VALUES (20, 'IT');
INSERT INTO emp VALUES (1, 'KIM', 10);
INSERT INTO emp VALUES (2, 'LEE', 20);
INSERT INTO emp VALUES (3, 'PARK', 10);
COMMIT;Table created.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT emp_id, name, dept_id FROM emp e JOIN dept d ON e.dept_id = d.dept_id ORDER BY emp_id;SELECT emp_id, name, dept_id FROM emp e JOIN dept d ON e.dept_id = d.dept_id ORDER BY emp_id
*
ERROR at line 1:
ORA-00918: DEPT_ID: column ambiguously specified - appears in and
Help: https://docs.oracle.com/error-help/db/ora-00918/SELECT table_name, column_name FROM user_tab_columns WHERE column_name IN ('NAME', 'DEPT_ID') ORDER BY column_name, table_name;TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
DEPT DEPT_ID
EMP DEPT_ID
DEPT NAME
EMP NAME
4 rows selected.Always qualify columns in joins, even the ones that are unique today. The next scenario shows why.
SELECT e.emp_id, e.name, e.dept_id, d.name AS dept_name FROM emp e JOIN dept d ON e.dept_id = d.dept_id ORDER BY e.emp_id; EMP_ID NAME DEPT_ID DEPT_NAME
---------- ------------------------------ ---------- --------------------
1 KIM 10 SALES
2 LEE 20 IT
3 PARK 10 SALES
3 rows selected.2A query broke after the same column was added to another table
... WHERE created_at IS NULL worked and returned 3 rows while only EMP had created_at. After ALTER TABLE dept ADD (created_at DATE), the same query failed on CREATED_AT; user_tab_columns then showed the column in both tables. The SQL didn't change, a table did.
Writing WHERE e.created_at IS NULL brought back the same 3 rows.
Setup
CREATE TABLE dept (dept_id NUMBER PRIMARY KEY, name VARCHAR2(20));
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), dept_id NUMBER REFERENCES dept);
INSERT INTO dept VALUES (10, 'SALES');
INSERT INTO dept VALUES (20, 'IT');
INSERT INTO emp VALUES (1, 'KIM', 10);
INSERT INTO emp VALUES (2, 'LEE', 20);
INSERT INTO emp VALUES (3, 'PARK', 10);
COMMIT;Table created.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.ALTER TABLE emp ADD (created_at DATE);Table altered.SELECT e.emp_id, e.name, d.name AS dept_name FROM emp e JOIN dept d ON e.dept_id = d.dept_id WHERE created_at IS NULL ORDER BY e.emp_id; EMP_ID NAME DEPT_NAME
---------- ------------------------------ --------------------
1 KIM SALES
2 LEE IT
3 PARK SALES
3 rows selected.ALTER TABLE dept ADD (created_at DATE);Table altered.SELECT e.emp_id, e.name, d.name AS dept_name FROM emp e JOIN dept d ON e.dept_id = d.dept_id WHERE created_at IS NULL ORDER BY e.emp_id;SELECT e.emp_id, e.name, d.name AS dept_name FROM emp e JOIN dept d ON e.dept_id = d.dept_id WHERE created_at IS NULL ORDER BY e.emp_id
*
ERROR at line 1:
ORA-00918: CREATED_AT: column ambiguously specified - appears in and
Help: https://docs.oracle.com/error-help/db/ora-00918/SELECT table_name, column_name FROM user_tab_columns WHERE column_name = 'CREATED_AT' ORDER BY table_name;TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
DEPT CREATED_AT
EMP CREATED_AT
2 rows selected.SELECT e.emp_id, e.name, d.name AS dept_name FROM emp e JOIN dept d ON e.dept_id = d.dept_id WHERE e.created_at IS NULL ORDER BY e.emp_id; EMP_ID NAME DEPT_NAME
---------- ------------------------------ --------------------
1 KIM SALES
2 LEE IT
3 PARK SALES
3 rows selected.3e.*, d.* inside an inline view or subquery
SELECT * FROM (SELECT e.*, d.* FROM emp e JOIN dept d ...) WHERE ROWNUM <= 2 failed on NAME. Running the inner join alone worked, but its result had DEPT_ID and NAME twice each. That is allowed for a plain query, but once it becomes an inline view, every column needs a unique name so the outer query can reference it.
Listing the columns with distinct aliases (e.name AS emp_name, d.name AS dept_name, only one dept_id) returned the first 2 rows: KIM SALES, LEE IT.
Setup
CREATE TABLE dept (dept_id NUMBER PRIMARY KEY, name VARCHAR2(20));
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), dept_id NUMBER REFERENCES dept);
INSERT INTO dept VALUES (10, 'SALES');
INSERT INTO dept VALUES (20, 'IT');
INSERT INTO emp VALUES (1, 'KIM', 10);
INSERT INTO emp VALUES (2, 'LEE', 20);
INSERT INTO emp VALUES (3, 'PARK', 10);
COMMIT;Table created.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT * FROM (SELECT e.*, d.* FROM emp e JOIN dept d ON e.dept_id = d.dept_id ORDER BY e.emp_id) WHERE ROWNUM <= 2;SELECT * FROM (SELECT e.*, d.* FROM emp e JOIN dept d ON e.dept_id = d.dept_id ORDER BY e.emp_id) WHERE ROWNUM <= 2
*
ERROR at line 1:
ORA-00918: NAME: column ambiguously specified - appears in and
Help: https://docs.oracle.com/error-help/db/ora-00918/SELECT e.*, d.* FROM emp e JOIN dept d ON e.dept_id = d.dept_id ORDER BY e.emp_id; EMP_ID NAME DEPT_ID DEPT_ID NAME
---------- ------------------------------ ---------- ---------- ------------------------------
1 KIM 10 10 SALES
2 LEE 20 20 IT
3 PARK 10 10 SALES
3 rows selected.SELECT * FROM (SELECT e.emp_id, e.name AS emp_name, d.dept_id, d.name AS dept_name FROM emp e JOIN dept d ON e.dept_id = d.dept_id ORDER BY e.emp_id) WHERE ROWNUM <= 2; EMP_ID EMP_NAME DEPT_ID DEPT_NAME
---------- -------------------- ---------- --------------------
1 KIM 10 SALES
2 LEE 20 IT
2 rows selected.FAQ
What does ORA-00918 column ambiguously specified mean?
A column name you used exists in two or more tables of the join, and Oracle can't tell which one you mean. 23ai shows the column name at the start of the message.
How do I fix ORA-00918?
Prefix the column with the table alias, such as e.dept_id or d.name. If two tables have the same column name in the SELECT list, give them different aliases (d.name AS dept_name).
Why does ORA-00918 appear in a query that used to work?
Someone probably added a column with the same name to another table in the join. An unqualified column becomes ambiguous at that moment. Qualifying every column prevents this.
Why ORA-00918 when I use SELECT * on a join in a subquery?
e.*, d.* gives two columns with the same name (for example NAME). An inline view needs unique column names, so list the columns and alias the duplicates.
How we verify
Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.
lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free