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');
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.