why you should always use aliases in SQL (oracle)

let's create for this example following 2 tables (a and b):

create table a (org_id number, org_name varchar2(1)); 
insert into a values(1,'a'); 
insert into a values(2,'b'); 
insert into a values(3,'c');
commit;

create table b (name varchar2(1)); 
insert into b values('a'); 
insert into b values('b');
commit;

Now let's run this:

select * from a 
where a.org_id in (select org_id from b); 
-- returns all 3 rows from a 

select * from a 
where a.org_id not in (select org_id from b); 
-- returns 0 rows

The tricky part is, you might want the org_id in the subquery to be taken from b (which does not exist) and hence expect an INVALID IDENTIFIER error message as running only subquery:

select org_id from b 
        * 
ORA-00904: "ORG_ID": invalid identifier

But the initial query is correlated and actually the org_id in the subquery comes from outer table a and not from b.

That's why a good practice would be always to use aliases as prefixes to the columns.

In that way we can rewrite the query that will now raise an exception as expected:

select * from a 
where a.org_id not in (select b.org_id from b); 
-- ORA-00904: "B"."ORG_ID": invalid identifier