The Curious Case of The Missing ORA-00904

“ORA-00904: invalid identifier” is a familiar error to Oracle developers. It occurs, for example, when you refer to a table column that does not exist.

What’s worse than an error that shows up when you don’t expect it? An error that does NOT show up when you totally expect it.

Here’s a puzzle for you to solve. You’re given two tables – EMPL and DEPT – in which column EMP_DEPT_ID of table EMPL references DEPT_ID of table DEPT.

SQL> desc dept
 Name              Null?    Type
 ----------------- -------- ------------
 DEPT_ID                    NUMBER(2)
 DEPT_NAME                  VARCHAR2(6)

SQL> desc empl
 Name              Null?    Type
 ----------------- -------- ------------
 EMP_ID                     NUMBER(2)
 EMP_NAME                   VARCHAR2(6)
 EMP_DEPT_ID                NUMBER(2)

Note that the foreign key column names in the two tables are not identical. The column is called DEPT_ID in table DEPT, EMP_DEPT_ID in table EMPL.

While writing a SQL to query from table EMPL, you might mistakenly refer to the column EMP_DEPT_ID as DEPT_ID.

-- Incorrect column name
select dept_id
from empl;

Column DEPT_ID does not exist in table EMPL. This SQL, when executed, will surely return the error “ORA-00904: invalid identifier”.

SQL> -- Incorrect column name
SQL> select dept_id
  2  from empl;
select dept_id
       *
ERROR at line 1:
ORA-00904: "DEPT_ID": invalid identifier

So far so good.

Here’s where things get odd. Put that wrong SQL with the invalid identifier in a nested subquery.

select *
from   dept
where dept_id in 
(
-- Incorrect column name
select dept_id
from empl
);

You expect the same error, don’t you? But no, Oracle runs it without a murmur.

SQL> select *
  2  from   dept
  3  where dept_id in
  4  (
  5  -- Incorrect column name
  6  select dept_id
  7  from empl
  8  );

   DEPT_ID DEPT_N
---------- ------
         1 Sales
         2 IT

No sign of ORA-00904. It’s almost as if Oracle understood that the Oracle developer meant EMP_DEPT_ID instead of DEPT_ID.

What’s going on here?