This topic describes the writing specifications of multi-table join statements in OceanBase Database.
We recommend that you use aliases for multi-table join statements and refer to fields by using aliases in the SELECT list. Database. table notation is also required.
Example:
select a.cid from deposit_account.tbluser a where ...We strongly recommend that you do not use outer joins, including left outer joins, right outer joins, and full outer joins, in production systems.
In multi-table join queries, we recommend that you select the table with a smaller result set as the driving table.
We recommend that you rewrite nested multi-layer subqueries into a format that connects tables in sequence.
A multi-table join statement must not return a Cartesian product. Generally, a Cartesian product is unreasonable and results from design flaws or the omission of join conditions, and causes performance issues.
Ensure that the two fields in the table join conditions use the same collation type. Otherwise, the system may not be able to use indexes correctly.
For redundant multi-table queries, we recommend that you use common table expressions (CTEs) for optimization and rewriting.