This topic describes the requirements and suggestions on writing 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 joins, right outer joins, or full outer joins in your production systems.
In a query that involves a 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 such SQL statements into statements that sequentially join tables.
Avoid the generation of a Cartesian product during a multi-table join. A Cartesian product is generally caused by defective logic design or omission of join conditions, which can result in performance issues.
Make sure that the same collation type is used for the two fields in the join condition. Otherwise, indexes cannot be correctly used.
For a redundant SQL multi-table query, we recommend that you use a common table expression (CTE) to rewrite the query.