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 8-)

6 thoughts on “Column qualification best practice”

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

  2. 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. > “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;
    /
    

  4. 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

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>