This topic describes how to write multi-table join statements in OceanBase Database.
In multi-table join queries, it is recommended to use aliases and to reference fields in the SELECT list by 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 use inner joins first.
Note
OceanBase Database supports outer join syntax such as
LEFT JOIN,RIGHT JOIN, andFULL JOIN. However, in production environments, we recommend that you do not directly use outer joins.In multi-table join queries, the driving table should be the table with the smaller result set.
We recommend that you avoid writing SQL statements that contain multiple levels of nested subqueries. Instead, you can rewrite the statements in the table join format.
Cartesian products are not allowed in multi-table joins. Cartesian products are usually caused by design defects or missing join conditions. Cartesian products can also cause performance issues.
Make sure that the two fields in the join condition have the same collation type. Otherwise, the database may fail to use the index.
For redundant SQL statements that contain multiple tables, we recommend that you use CTEs to optimize and rewrite the statements.