Rule: Use left join Instead of right join

Rule Code: CV08

Name: left_join

Overview

The SQL join operations allow combining records from two or more tables based on a related column. There are various types of joins, such as INNER join, left join, and right join. While both left join and right join can achieve the same results by reversing the table order, the best practice is to favor the use of left join over right join.

Explanation

Anti-pattern: Using right join

The use of right join in SQL is generally considered an anti-pattern for several reasons:

  • It reverses the typical left-to-right reading flow of SQL queries.

  • It can make queries less readable and harder to understand, especially in complex queries.

  • right join requires the reader to mentally reverse the order of tables to comprehend the results.

Example of ``right join`` usage (Anti-pattern):

select foo.col1,
       bar.col2
  from foo
 right join bar
    on foo.bar_id = bar.id;

Best Practice: Use left join Instead

It is recommended to use left join because it:

  • Aligns with the natural left-to-right flow of reading and writing queries.

  • Enhances readability and consistency in SQL code.

  • Is more commonly used and understood in practice.

Refactored query using ``left join`` (Best practice):

selec foo.col1,
      bar.col2
 from bar
 left join foo
   on foo.bar_id = bar.id;

Benefits of left join

  • The results and logic remain the same as right join but in a more intuitive manner.

  • Promotes better maintainability and reduces confusion for other developers reviewing the code.

Conclusion

While right join and left join are functionally equivalent when the table order is reversed, it is a best practice to use left join for better readability, consistency, and maintainability in SQL queries.

Groups: