Skip to content

G-3120: Always use table aliases when your SQL statement involves more than one source.

Major

Maintainability

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.

Example (bad)

1
2
3
4
5
6
select last_name
      ,first_name
      ,department_name
  from      employees  
       join departments using (department_id)
 where extract(month from hire_date) = extract(month from sysdate);

Example (better)

1
2
3
4
5
6
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);

Example (good)

Using meaningful aliases improves the readability of your code.

1
2
3
4
5
6
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);

Example Subquery (bad)

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.

1
2
3
4
5
6
select last_name
      ,first_name
  from employees
 where employee_id in (select employee_id
                         from jobs
                        where job_title like '%Manager%');

Example Subquery (good)

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.

1
2
3
4
5
6
select emp.last_name
      ,emp.first_name
  from employees emp
 where emp.employee_id in (select j.employee_id
                             from jobs j
                            where j.job_title like '%Manager%');