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