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 faulty logic design or omission of JOIN conditions, which can result in performance issues.