Multi-table join specifications

This topic describes how to write multi-table join statements in OceanBase Database.

• In multi-table join queries, it is recommended to use aliases and to reference fields in the SELECT list using the alias in the database.table format.

  Example: select a.cid from deposit_account.tbluser a where ...

• In multi-table join scenarios, it is recommended to use inner joins first.

  Note

  OceanBase Database supports outer join syntax such as LEFT JOIN, RIGHT JOIN, and FULL JOIN. However, in production environments, it is not recommended to directly use outer joins.

• In multi-table join queries, the driving table should be the one with the smaller result set.

• It is not recommended to write SQL statements with multiple levels of nested subqueries. Instead, it is recommended to rewrite them in the format of sequential table joins.

• Cartesian products are not allowed in multi-table joins. Cartesian products are generally unreasonable and are usually caused by design defects or missing join conditions. They can also lead to performance issues.

• Ensure that the two fields in the join conditions have the same collation type. Otherwise, it may be impossible to use the index correctly.

• For redundant SQL queries involving multiple tables, it is recommended to consider using CTEs for optimization and rewriting.