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 😎

6 thoughts on “Column qualification best practice

  1. Marco Gralike

    The use of aliases should be mandatory…(diminishes a lot of code errors)

  2. Beth

    I wholeheartedly agree! “select *” should be banned from all production code. Period.

    This was one of many “being a professional developer” lessons drummed into my brain by the senior dba at my first real database job. I remember thinking it was rather silly, until I lived through the first dot release of the product. Suddenly it all made sense….

  3. Pingback: Oracle Musings » Column Naming

  4. Laurent Schneider Post author

    > β€œselect *” should be banned from all production code.

    maybe with records it is ok
    declare r emp%ROWTYPE;
    begin
    select * into r
    from emp
    where ename='SCOTT';
    update emp
    set sal=r.sal+100
    where empno=r.empno;
    end;
    /

  5. Patrick Wolf

    For records SELECT * could be ok, but your example wouldn’t be a good one where I personally would use a record πŸ˜‰

    Because it just uses two attributes of that record. Waste of resources, especially if you consider you have some long VARCHAR2 columns in your table…

    Patrick

Comments are closed.