This topic describes the specifications for writing multi-table association statements in OceanBase Database.
In multi-table join queries, it is recommended to use aliases and to reference fields in the SELECT list using the alias in the database.table format.
Example:
select a.cid from deposit_account.tbluser a where ...In multi-table join scenarios, it is recommended to prioritize inner joins.
Note
OceanBase Database supports
LEFT JOIN,RIGHT JOIN, andFULL JOINsyntax for outer joins. However, in production environments, direct use of outer joins is not recommended.In multi-table join queries, the driving table should be the one with the smaller result set.
It is not recommended to write SQL statements with nested subqueries. Instead, rewrite them in the format of table sequential joins.
Cartesian products are not allowed in multi-table associations. Cartesian product scenarios are generally unreasonable and are usually caused by design flaws or missing join conditions, which can also lead to performance issues.
Ensure that the two fields in the table join conditions have the same collation type. Otherwise, it may be impossible to use the index correctly.
For redundant SQL multi-table queries, consider using CTEs for optimization and rewriting.