Oracle ORA-00918 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00918: Column Ambiguously Defined — Causes, Fixes & Prevention

ORA-00918 occurs when Oracle cannot determine which table a column belongs to in a query involving multiple tables with identically named columns. This error is thrown at parse time, meaning the query will not execute at all until the ambiguity is resolved. It most commonly appears in JOIN queries where column names are not prefixed with a table alias.

Top 3 Causes

1. Missing Table Alias in JOIN Queries

The most frequent cause. When two or more tables share a column name (e.g., ID, NAME, STATUS) and the SELECT clause does not specify which table the column comes from, Oracle raises ORA-00918.

-- ERROR: DEPT_ID exists in both tables
SELECT DEPT_ID, EMP_NAME, DEPT_NAME
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.DEPT_ID = d.DEPT_ID;

-- FIXED: Prefix each column with a table alias
SELECT e.DEPT_ID,
       e.EMP_NAME,
       d.DEPT_NAME
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.DEPT_ID = d.DEPT_ID;

2. Duplicate Column Names in Inline Views or Subqueries

When an inline view selects columns with the same name from different tables without aliasing them, the outer query cannot distinguish between them.

-- ERROR: NAME appears twice in the inline view
SELECT NAME, TOTAL_SAL
FROM (
    SELECT e.NAME, d.NAME, SUM(e.SALARY) AS TOTAL_SAL
    FROM EMPLOYEES e
    JOIN DEPARTMENTS d ON e.DEPT_ID = d.DEPT_ID
    GROUP BY e.NAME, d.NAME
);

-- FIXED: Use column aliases inside the inline view
SELECT EMP_NAME, DEPT_NAME, TOTAL_SAL
FROM (
    SELECT e.NAME  AS EMP_NAME,
           d.NAME  AS DEPT_NAME,
           SUM(e.SALARY) AS TOTAL_SAL
    FROM EMPLOYEES e
    JOIN DEPARTMENTS d ON e.DEPT_ID = d.DEPT_ID
    GROUP BY e.NAME, d.NAME
);

3. Using Table Aliases with USING or NATURAL JOIN

When JOIN ... USING(column) is used, Oracle treats the joined column as belonging to neither table exclusively. Prefixing it with a table alias causes ORA-00918 (or ORA-01748).

-- ERROR: Cannot use table alias with USING clause column
SELECT e.DEPT_ID, e.EMP_NAME, d.DEPT_NAME
FROM EMPLOYEES e
JOIN DEPARTMENTS d USING (DEPT_ID);

-- FIXED Option 1: Reference the USING column without any alias
SELECT DEPT_ID,
       e.EMP_NAME,
       d.DEPT_NAME
FROM EMPLOYEES e
JOIN DEPARTMENTS d USING (DEPT_ID);

-- FIXED Option 2 (Recommended): Switch to ON clause
SELECT e.DEPT_ID,
       e.EMP_NAME,
       d.DEPT_NAME
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.DEPT_ID = d.DEPT_ID;

Quick Fix Summary

Situation	Fix
JOIN with shared column names	Add table alias prefix to all columns
Inline view with duplicate names	Add unique column aliases inside subquery
USING clause column conflict	Remove alias prefix or switch to ON clause

Prevention Tips

Always alias every column in multi-table queries. Make it a team coding standard to prefix every column in any query containing a JOIN. This eliminates ORA-00918 entirely and makes queries easier to read and maintain.

-- Best practice: always use table aliases on all columns
SELECT e.EMP_ID,
       e.EMP_NAME,
       e.SALARY,
       d.DEPT_NAME,
       d.LOCATION_ID
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.DEPT_ID = d.DEPT_ID;

Avoid SELECT * in JOIN queries. Using SELECT * across joined tables almost guarantees ambiguity errors when tables share column names. Always explicitly list the columns you need, with table aliases attached.

-- Avoid this in JOINs
SELECT * FROM EMPLOYEES e JOIN DEPARTMENTS d ON e.DEPT_ID = d.DEPT_ID;

-- Do this instead
SELECT e.EMP_ID, e.EMP_NAME, d.DEPT_NAME
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.DEPT_ID = d.DEPT_ID;

Related Oracle Errors

ORA-00960 — Ambiguity in column naming in select list
ORA-01748 — Only simple column names allowed (common with USING/NATURAL JOIN)
ORA-00904 — Invalid identifier (often encountered while fixing ORA-00918)

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.