In data, sub queries can be divided into dependent sub queries and non dependent sub queries. A dependent subquery means that the execution of the subquery depends on the 'variable' of the external query, so the subquery is usually calculated multiple times. A subquery without dependency means that the execution of the subquery does not depend on the 'variable' of the external query. This subquery generally only needs to be calculated once. The following figure shows a sub query without dependency and a sub query with dependency.

In Oceanbase, subqueries are executed through the subplan filter operator. The Subplan filter operator will traverse every row in the outer query, and for each row traversed, it will check the corresponding subquery to determine whether the conditions are met, which is somewhat similar to Nested Loop Join. In order to execute sub queries efficiently, sub queries that meet specific conditions will be rewritten into join statements, so that the optimizer can have more choices when selecting join algorithms (in addition to Nested Loop Join, you can also select Hash Join and Merge Join). In Oceanbase, subqueries without dependencies will be rewritten into join statements. For dependent subqueries, only when certain conditions are met will they be rewritten as join statements. The following figure shows a subquery that has not been rewritten as a join statement and a subquery that has not been rewritten as a join statement.