This topic describes the writing specifications for multi-table join statements in OceanBase AI Database.
It is recommended to use aliases for multi-table join queries, and reference fields in the SELECT list using alias names in the database.table format.
Example
select a.cid from deposit_account.tbluser a where ...In multi-table join scenarios, it is recommended to prefer inner joins.
Note
OceanBase AI Database supports outer join syntaxes such as
LEFT JOIN,RIGHT JOIN, andFULL JOIN. However, in a production environment, it is not recommended to use outer joins directly.In queries with multiple table joins, the driver table should be a table with a smaller result set.
It is not recommended to write SQL statements with multiple levels of nested subqueries. Instead, it is recommended to rewrite them in the form of sequential table joins.
Cartesian product is prohibited in multi-table joins: Cartesian product scenarios are generally unreasonable, usually caused by design flaws or missing join conditions, and can easily lead to performance issues.
Ensure that the two fields in the table join condition have the same collation type; otherwise, the index may not be used correctly.
For redundant SQL queries involving multiple tables, consider using a CTE for optimization and rewriting.