Qualifying Column/Object names to set the right scope

Earlier today, I had a curious query that I knew was returning the wrong results.
(Fortunately, I could look at the output and say outright "this is wrong". Had I not been observant enough and not known the underlying data, I would have allowed that "wrong" query to return the wrong results and accepted them as "correct").

More than possible Oracle code and optimization bugs, the biggest reason for "wrong results" is improper referencing of objects. My query today was similar -- and it had been written by me !

Here is an example where two tables have a similar column name yet the wrong column is being referenced in the subquery. The subquery attempts "DEPTID" in the "EMP" table but there is no such column (the column name is really "E_DEPTID"). However, Oracle's name resolution is (successfully) able to resolve the "DEPTID" in that subquery to be that in the "DEPT" table. The query runs successfully but the results are wrong.

SQL>
SQL> create table DEPT (deptid number not null primary key, deptname varchar2(30));

Table created.

SQL> create table EMP (empid  number not null primary key, e_deptid number, ename varchar2(30));

Table created.

SQL>
SQL> insert into DEPT select rownum,
2  decode(rownum,1,'SALES',2,'ACCOUNTING',3,'MANUFACTURING',0,'NON-EXISTENT')
3  from dual connect by level less_than 4;

3 rows created. 

SQL> insert into EMP select rownum, mod(rownum,4)+1, dbms_random.string('X',25)
2  from dual connect by level less_than 41;

40 rows created.

SQL> commit;

Commit complete.

SQL> -- identify all the departments
SQL> select deptid, deptname from DEPT;

DEPTID DEPTNAME
---------- ------------------------------
     1 SALES
     2 ACCOUNTING
     3 MANUFACTURING

SQL> -- now deliberately ensure that there are no employees for the ACCOUNTING department
SQL> delete emp where e_deptid = 2;

10 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL> -- this query falsely returns rows !
SQL> -- there is no column called "deptid" in EMP
SQL> -- yet the query succeeds because Oracle resolved deptid to be the column in DEPT !
SQL> select distinct deptname from DEPT
2  where deptid in (select deptid from EMP);

DEPTNAME
------------------------------
ACCOUNTING
MANUFACTURING
SALES

SQL>
SQL> -- this is how the query would be best framed
SQL> -- we can now see that it fails
SQL> select distinct d.deptname from DEPT d
2  where d.deptid in (select e.deptid from EMP e);
where d.deptid in (select e.deptid from EMP e)
                      *
ERROR at line 2:
ORA-00904: "E"."DEPTID": invalid identifier


SQL>
SQL> -- this is the correct query
SQL> select distinct d.deptname from DEPT d
2  where d.deptid in (select e.e_deptid from EMP e);

DEPTNAME
------------------------------
MANUFACTURING
SALES

SQL>  

What would be good practice ? To properly qualify all column names, particularly when using
a. SubQueries
b. Joins
c. Table Aliases

Similar name resolution issues arise when objects and synonyms, private and public, exist with the same name. Thus, if "EMP" is not found as a table in the current schema, Oracle attempts to look for a private synonym called "EMP". If it can't find a private synonym, it looks for a public synonym.

In a development environment with multiple schemas present, when you are querying "EMP" make sure that you are querying "EMP" as a table in the current schema and not as a synonym to a table in some other schema !

.
.
.