During my earlier work as a consultant and in my todays job as a technical supporter to the development team I encountered various SQL & PL/SQL antipatterns. I would like to share some of them with you.


Be creative…at least a little bit.

Another antipattern that I see over and over again, is the reuse of table aliases within the same statement … yes syntactically everything is fine, but it is a nightmare to read a query like this – besindes the fact, that table aliases like a,b,c are awful.

select e.first_name, e.last_name
  from employees e
 where e.job_id in (select a.job_id
                      from jobs a
                     where a.min_salary between 5000 and 7500
                        or a.max_salary between 7500 and 9000)
   and e.department_id in (select a.department_id
                             from departments a
                             join locations b on (b.location_id = a.location_id)
                            where b.country_id = 'UK')
   and e.manager_id in (select a.employee_id
                          from employees a
                         where a.hire_date < date '2005-01-01');

To me a table alias is not only a method to clarify the source of a column upon the SQL engine but also a way to ease reading the statement. Therefore the benefit to the reader is much higher than the higher effort it takes for the writer using longer aliases which are more meaningful.

select emp.first_name, emp.last_name
  from employees emp
 where emp.job_id in (select job.job_id
                        from jobs job
                       where job.min_salary between 5000 and 7500
                          or job.max_salary between 7500 and 9000)
   and emp.department_id in (select dpt.department_id
                               from departments dpt
                               join locations loc on (loc.location_id = dpt.location_id)
                              where loc.country_id = 'UK')
   and emp.manager_id in (select mgr.employee_id
                            from employees mgr
                           where mgr.hire_date < date '2005-01-01');

Conclusion

Knowing bad habits is the best way to avoid them.

Most of the examples I found are based on:

  • copy/paste coding
  • sticking to things that worked in the past (without questioning them)
  • ignoring new features

This (and the other posts on the anti-patterns) should be a help to get over them.