Skip to content

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

Blocker

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
7
select last_name
      ,first_name
      ,department_name
  from employees
  join departments
 using (department_id)
 where extract(month from hire_date) = extract(month from sysdate);

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
7
8
select last_name
      ,first_name
  from employees
 where employee_id in (
          select employee_id
            from jobs
           where job_title like '%Manager%' -- NOSONAR: G-1050 literal is ok for a standalone query
       );

Example (better)

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

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
7
8
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%' -- NOSONAR: G-1050 literal is ok for a standalone query
       );