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 use inner joins in multi-table join scenarios.
Note
OceanBase Database supports outer join syntax such as
LEFT JOIN,RIGHT JOIN, andFULL JOIN. However, we do not recommend that you use outer joins in production environments.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 multi-table SQL query, we recommend that you use a common table expression (CTE) to rewrite the query.