Column qualification best practice

Lazyness at the development can have dramatic costs in production and maintenance. I want to summarize why and where you should always qualify your columns.

Why? when you select or modify data from a table, you must qualify the columns you are using so if the order of the column change, or if one column is added, renamed or removed, so long this column is not related to your query your code should keep working. A typical example is when the dba add a LAST_MODIFICATION_DATE and a trigger to automatically fill that column for auditing purpose. It should never make your application fail.

0) sample table

create table t1(x number, y number);
create table t2(a number, b number);
create table t3(x number, z number);

1) insert into table
bad:insert into t1 values(1,2);
good:insert into t1(x,y) values(1,2);

bad:insert into t1
select * from t2;
good:insert into t1(x,y)
select t2.a,t2.b from t2;

bad:insert all
when a>0 then into t1
select * from t2;
good:insert all
when a>0 then into t1(x,y)
select t2.a,t2.b from t2;

2) merge
bad:merge into t1
using t2 on (x=a)
when matched then
update set y=b
where (b>y)
delete
where (b>y)
when not matched then
insert values(a,b)
where (b>0);
good:merge into t1
using t2 on (t1.x=t2.a)
when matched then
update set y=t2.b
where (t2.b>t1.y)
delete
where (t2.b>t1.y)
when not matched then
insert (x,y) values(t2.a,t2.b)
where (t2.b>0);

Not prefixing the column here will bug as soon as a column a or b is added to table t1

2) joins

just never use natural join in production

bad:select *
from t1 natural join t3;
good:select x, t1.y, t3.z
from t1 join t3 using (x);

bad:select *
from t1,t2
where x=a;
good:select t1.x,t1.y,t2.a,t2.b
from t1,t2
where t1.x=t2.a;

It is not about good looking code, it is about data quality and stability 😎