ORA-00918: column ambiguously specified

ORA-00918: DEPT_ID: column ambiguously specified - appears in  and

In 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

  1. Columns in a join SELECT without table aliases
  2. A query broke after the same column was added to another table
  3. e.*, d.* inside an inline view or subquery

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
Setuprun as lab_a
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;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT emp_id, name, dept_id FROM emp e JOIN dept d ON e.dept_id = d.dept_id ORDER BY emp_id;
OUTPUT
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/
Checkrun as lab_a
SELECT table_name, column_name FROM user_tab_columns WHERE column_name IN ('NAME', 'DEPT_ID') ORDER BY column_name, table_name;
OUTPUT
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.

After the fixrun as lab_a
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;
OUTPUT
    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
Setuprun as lab_a
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;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Setuprun as lab_a
ALTER TABLE emp ADD (created_at DATE);
OUTPUT
Table altered.
Setuprun as lab_a
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;
OUTPUT
    EMP_ID NAME                           DEPT_NAME
---------- ------------------------------ --------------------
         1 KIM                            SALES
         2 LEE                            IT
         3 PARK                           SALES

3 rows selected.
Setuprun as lab_a
ALTER TABLE dept ADD (created_at DATE);
OUTPUT
Table altered.
Reproducerun as lab_a
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;
OUTPUT
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/
Checkrun as lab_a
SELECT table_name, column_name FROM user_tab_columns WHERE column_name = 'CREATED_AT' ORDER BY table_name;
OUTPUT
TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
DEPT                           CREATED_AT
EMP                            CREATED_AT

2 rows selected.
After the fixrun as lab_a
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;
OUTPUT
    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
Setuprun as lab_a
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;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
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;
OUTPUT
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/
Checkrun as lab_a
SELECT e.*, d.* FROM emp e JOIN dept d ON e.dept_id = d.dept_id ORDER BY e.emp_id;
OUTPUT
    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.
After the fixrun as lab_a
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;
OUTPUT
    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

Related errors