This topic describes the specifications for writing multi-table join statements in OceanBase Database.
We recommend that you use aliases for multi-table join queries and refer to fields in the SELECT list and database. table format by using aliases.
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 SQL statements that contain nested multi-layer subqueries into table sequential join format.
Multi-table Cartesian join is not allowed. Generally, the occurrence of Cartesian join results from design flaws or the omission of join conditions, and causes performance issues.
Ensure that the two fields in the table join condition use the same collation type. Otherwise, the system may fail to correctly use indexes.
We recommend that you use common table expressions (CTEs) to optimize multi-table queries that return multiple columns.