G-3120
🆓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
from employees
where employee_id in (
select employee_id
from jobs
where job_title like '%Manager%'
); | Line | Column | Message |
|---|---|---|
| 5 | 18 | Missing table alias for employee_id. |
If the jobs table does not have an employee_id column, but the employees table does, this query will not raise an error. Instead, it will return all the rows from the employees table. This is because a subquery can access the columns of all its parent tables. This construct is known as a correlated subquery.
Compliant Solution - ★★★★★
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%'
); This query will return an error if the jobs table does not have an employee_id column. This is because the table alias has been added to the column to read the employee_id column from the jobs table.
Parameters
Use parameters to customize the rule to your needs.
| Parameter | Description | Default Value |
|---|---|---|
| NotQualifiableIdentifier | Identifiers that cannot be qualified with a table alias in a query. For example, pseudo columns or parameterless functions. | connect_by_iscycle, connect_by_isleaf, level, rownum, current_date, current_timestamp, dbtimezone, iteration_number, localtimestamp, ora_invoking_user, ora_invoking_userid, sessiontimezone, sysdate, systimestamp, uid, user, null, true, false |
References
- same as Trivadis G-3120
- same as plsql:TablesShouldBeAliasedCheck