G-3192
Avoid joining tables with the USING clause.
Reason
The using clause in SQL joins offers a concise syntax when two tables share join columns with the same name. However, this convenience comes at a cost in clarity, flexibility, maintainability, and reliability.
When a column is referenced in a using clause, it loses its table affiliation within that query. You can no longer qualify it with a table alias, and the DBMS will actively prevent you from doing so. This creates an inconsistency in how columns must be referenced throughout the query: some columns require a table alias to be unambiguous, while the join columns must remain unqualified. It is not longer possible to qualify all columns for clarity as suggested by rule G-3120. This inconsistency makes queries harder to read and reason about, as the rules for column referencing are no longer uniform.
The problem becomes more severe as queries grow in complexity. If a third table is introduced into the query and it happens to contain a column with the same name as one of the using clause columns, the query may break. Even worse, you may no longer be able to produce the correct result without rewriting the join condition by avoiding the using clause, which is a surprising and unwanted side effect.
From a maintainability perspective, the using clause creates fragile queries. The supposed brevity of the using clause therefore only delays the refactoring, often at a point where the context is less fresh and the risk of introducing errors is higher.
Using an explicit on clause with fully qualified column references is always unambiguous, consistent, and resilient to schema changes. It ensures that every column in the query can easily be traced back to its source without exception.
Example
Non-Compliant Example
select department_id, emp.last_name, mgr.last_name as mgr_last_name from employees emp join departments dept using (department_id) left join employees mgr on emp.manager_id = mgr.employee_id order by department_id, emp.last_name;
Issues
| Line | Column | Message |
|---|---|---|
| 3 | 25 | Join with USING clause. |
This query fails with ORA-00918: DEPARTMENT_ID: column ambiguously specified - appears in and. We cannot use the emp or dept aliases here. However, we could use the mgr table alias, which produces a different result, since managers and employees are not necessarily in the same department.
Compliant Solution - ★★★★★
select emp.department_id, emp.last_name, mgr.last_name as mgr_last_name from employees emp join departments dept on dept.department_id = emp.department_id left join employees mgr on emp.manager_id = mgr.employee_id order by department_id, last_name
By using the on clause to join departments, we can qualify the department_id column and produce the correct result.
References
- similar to dbLinter G-3120
It enforces a table alias, which is obviously impossible for join columns in a USING clause.
- similar to Oracle USING clause best practice
It elaborates on the consistency and limitations of the USING clause.