Multi-table join statements

This topic describes the requirements and suggestions on multi-table join statements in OceanBase Database.

• We recommend that you use aliases in multi-table join queries and specify the aliases for referencing fields in the SELECT list. The aliases are in the database name.table name format.

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

• We recommend that you do not use LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN in your production systems.

• In a query that involves multi-table join, we recommend that you select the table with the smallest result set as the driving table.

• Do not write SQL statements with nested subqueries. We recommend that you rewrite the SQL statements that sequentially join tables.

• Avoid the generation of the Cartesian product during multi-table join. Cartesian product is generally caused by design defects or missing JOIN conditions, which can result in performance issues.