Updated on 2025-03-13 GMT+08:00

Join Query

  • [Rule] The nesting depth of multi-table join must be less than 8.

    If the join nesting is too deep, slow SQL statements may be generated. You need to optimize the join nesting at the service layer.

  • [Rule] Specify the join condition (ON) of each table to avoid Cartesian product.

    For example, in B database, JOIN is equivalent to CROSS JOIN and INNER JOIN. However, in the SQL standards, JOIN is equivalent to INNER JOIN only and must be used together with the ON condition.

  • [Rule] Specify the JOIN mode based on the SQL standards during join. Do not use the JOIN keyword directly. Instead, use CROSS JOIN, INNER JOIN, LEFT JOIN, or RIGHT JOIN.
  • [Rule] When multiple tables are joined for query, aliases must be added to the tables to ensure that the statement logic is clear and easy to maintain.
  • [Recommendation] Different columns have different comparison overheads. Use a column type with high efficiency for joined columns.
    • The comparison efficiency of the numeric type is much higher than that of the string type.
    • The comparison efficiency of integers is much higher than that of numeric and floating-point types.
  • [Recommendation] The joined columns must be of the same data type to avoid the impact of implicit type conversion on the execution efficiency.
  • [Recommendation] Do not use nested subqueries. Use table join if possible because subqueries will generate temporary tables, which greatly affects SQL performance.
  • [Recommendation] If a large number of NULL values exist in the joined columns, you are advised to add the IS NOT NULL condition to the WHERE condition to improve the execution efficiency.