Recently I managed to delete every row from a table, while I really only wanted to delete a subset.
What I did was something like this: delete from emp where ename in (select ename from dept)
This will delete all 14 rows from emp, while I meant to delete just a few rows. Of course this is a silly example for emp/dept, but it illustrates the point.
The reason is obvious: dept does not contain a column named ename, so "select ename from dept" will always return all rows (a bit like "select 1 from dual").

How to avoid this? Easy: either never make a silly mistake like this (easier said than done) OR always use table aliases (or the complete table name). Same goes for views of course.


Some examples: select count(*) as n from emp where ename in (select ename from dept) Rowcount: 14 - while you aimed for a subset of those 14.

select count(*) as n from emp where emp.ename in (select emp.ename from dept) Rowcount: 14 - if you write it like this, the error becomes a bit more obvious.

select count(*) as n from emp where ename in (select dname from dept) Rowcount: 0 - you probably meant to do something like this.

select count(*) as n from emp where emp.ename in (select dept.ename from dept) ORA-00904 "dept.ename: invalid identifier" - using table names gives you the exception you need here.

select count(*) as n from emp e where e.ename in (select d.ename from dept d) ORA-00904 "d.ename: invalid identifier" - same goes for table aliases, which are a bit nicer to read.


Related tip: always make lots of backups, one day you'll be glad to have them. I sure was :-)

On database 23c the example below returns an exception: ORA-00904 invalid identifier.
As expected, the double aliases are not accepted.

On database 21xe and 19c however it runs just fine - which I found out after I made a typo but still got a result.
The first part of the double alias is ignored, so iv_d.d.dname actually returns d.dname.

with iv_d as (select d0.deptno, d0.dname || ' (0)' as dname from dept d0) select e.empno ,d.deptno ,iv_d.d.dname -- returns the result from d ,d.iv_d.dname -- returns the result from iv_d from emp e left join dept d on d.deptno = e.deptno left join iv_d on iv_d.deptno = d.deptno

On database 23ai the example below returns an exception: ORA-00918: DNAME: column ambiguously specified - appears in DEPT and DEPT.
Of course this a weird example using emp/dept, but something like this did happen to me in real life.
Multiple uses of "dept d_mgr" are not accepted, which is fine because the query doesn't make sense.
If you comment out the first of the "dept d_mgr" joins, it works as expected.

On database 21xe (and older versions) this runs without errors - but the result is wrong, as you may expect.

select e.empno ,e.ename ,d.dname ,e.mgr as mgr_empno ,e_mgr.ename as mgr_ename ,d_mgr.dname as mgr_dname from emp e left join dept d on d.deptno = e.deptno left join emp e_mgr on e_mgr.empno = e.mgr left join dept d_mgr on d_mgr.deptno = e.deptno -- # -- # when this line is enabled in 21xe, this returns wrong results but no error -- # when this line is enabled in 23ai, this gives "ORA-00918: DNAME: column ambiguously specified - appears in DEPT and DEPT" left join dept d_mgr on d_mgr.deptno = e_mgr.deptno where e.job in ('PRESIDENT', 'MANAGER') order by e.mgr nulls first ,e.empno