Multi-table join statements

This topic describes the requirements and suggestions on writing multi-table join statements in OceanBase Database.

• We recommend that you use aliases in multi-table join queries and specify the aliases for referencing fields in the SELECT list. The aliases are in the database name.table name format.

  Example: select a.cid from deposit_account.tbluser a where ...

• We recommend that you use inner joins in multi-table join scenarios.

  Note

  OceanBase Database supports outer join syntax such as LEFT JOIN, RIGHT JOIN, and FULL JOIN. However, we do not recommend that you use outer joins in production environments.

• In a query that involves a multi-table join, we recommend that you select the table with the smallest result set as the driving table.

• Do not write SQL statements with nested subqueries. We recommend that you rewrite such SQL statements into statements that sequentially join tables.

• Avoid the generation of a Cartesian product during a multi-table join. A Cartesian product is generally caused by defective logic design or omission of join conditions, which can result in performance issues.

• Make sure that the same collation type is used for the two fields in the join condition. Otherwise, indexes cannot be correctly used.

• For redundant multi-table SQL queries, we recommend that you use a common table expression (CTE) to optimize and rewrite the query.