Wednesday 18 February 2009

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

1 comment:

Tomasz Nazar said...

Wow! That is helpful, I believe not only Oracle is affected, MySql is too - I can confirm.
Thanks :)