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.