G-3120
🆓Error
Always use table aliases when your SQL statement involves more than one source.
Reason
It is more human readable to use aliases instead of writing columns with no table information.
Especially when using subqueries the omission of table aliases may end in unexpected behavior and result.
Examples
Non-Compliant Example
select last_name
,first_name
,department_name
from employees
join departments
using (department_id)
where extract(month from hire_date) = extract(month from sysdate);Issues
| Line | Column | Message |
|---|---|---|
| 1 | 8 | Missing table alias for last_name. |
| 2 | 8 | Missing table alias for first_name. |
| 3 | 8 | Missing table alias for department_name. |
Explanation
If the jobs table has no employee_id column and employees has one this query will not raise an error but return all rows of the employees table as a subquery is allowed to access columns of all its parent tables - this construct is known as correlated subquery.
select last_name
,first_name
from employees
where employee_id in (
select employee_id
from jobs
where job_title like '%Manager%'
); Compliant Solution - ★★★☆☆
select e.last_name
,e.first_name
,d.department_name
from employees e
join departments d
on (e.department_id = d.department_id)
where extract(month from e.hire_date) = extract(month from sysdate); Compliant Solution - ★★★★★
select emp.last_name
,emp.first_name
,dept.department_name
from employees emp
join departments dept
on (emp.department_id = dept.department_id)
where extract(month from emp.hire_date) = extract(month from sysdate);Explanation
Using meaningful aliases improves the readability of your code.
If the jobs table has no employee_id column this query will return an error due to the directive (given by adding the table alias to the column) to read the employee_id column from the jobs table.
select emp.last_name
,emp.first_name
from employees emp
where emp.employee_id in (
select job.employee_id
from jobs job
where job.job_title like '%Manager%'
);References
- same as Trivadis G-3120
- same as plsql:TablesShouldBeAliasedCheck