This topic describes the writing specifications of multi-table join statements in OceanBase Database.
We recommend that you use aliases for multi-table join queries and reference fields in the SELECT list and tables in the database using aliases. The database and table names must be separated with a dot (.)
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, the driving table must be the one with the smaller result set.
We recommend that you rewrite nested multi-layer subqueries into table sequential join statements.
You must avoid Cartesian joins in multi-table joins: Cartesian joins are generally unreasonable and are usually caused by design flaws or the omission of join conditions, and can cause performance issues.
Make sure that the two fields in the join condition use the same collation type. Otherwise, the system may fail to correctly use indexes.
We recommend that you optimize and rewrite redundant multi-table queries by using common table expressions (CTEs).