Rule: Specify Join Keys Instead of Using USING

Rule Code: ST07

Name: using

Overview

In SQL, joins between tables can be performed using either the ON clause or the USING clause. While USING can simplify syntax by reducing redundancy when the join key has the same name in both tables, it is considered a best practice to explicitly specify join keys using the ON clause. This is because ON provides more flexibility, clarity, and control over the join conditions, especially in complex queries. Explicitly specifying join keys ensures that the intent of the query is clear and minimizes potential confusion or ambiguity.

Explanation

Anti-pattern: Using USING in Join Conditions

The USING clause simplifies join conditions by allowing you to specify the common column once, but this can lead to ambiguity when working with more complex queries or when join keys have different names. Additionally, USING can make the query harder to read and understand, especially in cases where join logic involves multiple columns or aliasing.

Example of Using `USING` (Anti-pattern):

select *
  from orders
  join customers using (customer_id);

In this example, USING is applied to join the orders and customers tables based on the customer_id. While this syntax is more concise, it can lead to ambiguity and lacks the explicitness that is beneficial for maintainability.

Best Practice: Specify Join Keys Using ON

For greater clarity and flexibility, it is best to specify join keys explicitly using the ON clause. This makes the query more readable and maintainable, particularly in complex queries where control over the join logic is important.

Refactored Example with Explicit Join Keys (Best Practice):

select *
  from orders
  join customers
    on orders.customer_id = customers.customer_id;

In this refactored example, the ON clause is used to explicitly specify the join condition. This approach is clearer and more flexible, especially in cases where join keys have different names or when more complex logic is required.

Conclusion

Using the ON clause to specify join keys instead of USING ensures that SQL queries are more explicit, readable, and maintainable. Explicitly defining the join conditions provides greater flexibility and reduces ambiguity, making it easier for developers to understand and work with the query.

Groups:

• all

• structure