在数据中，子查询可以分成有依赖关系的子查询和没有依赖关系的子查询。有依赖关系的子查询是指该子查询的执行依赖了外部查询的’变量’，所以这种子查询通常会被计算多次。没有依赖关系的子查询是指该子查询的执行不依赖外部查询的’变量’, 这种子查询一般只需要计算一次。下图分别展示了一个没有依赖关系的子查询和一个有依赖关系的子查询。

    1. OceanBase (root@test)> create table t1(a int primary key, b int, c int);
    2. Query OK, 0 rows affected (0.70 sec)
    3. OceanBase (root@test)> create table t2(a int primary key, b int, c int);
    4. Query OK, 0 rows affected (0.92 sec)
    5. -- 没有依赖关系的子查询
    6. OceanBase (root@test)> select * from t1 where t1.a in (select t2.a from t2);
    7. Empty set (0.22 sec)
    8. -- 有依赖关系的子查询，子查询中用到了外层查询变量t1.b
    9. OceanBase (root@test)> select * from t1 where t1.a in (select t2.a from t2 where t2.b = t1.b);
    10. Empty set (0.05 sec)

    在Oceanbase中，子查询是通过subplan filter算子执行的。Subplan filter算子会遍历外层查询中的每一行，对于遍历的每一行都会去检查相应的子查询来判断是否满足条件，这有点类似于Nested Loop Join。为了高效地执行子查询，满足特定条件的子查询会被改写成连接语句, 这样优化器在选择连接算法的时候就可以有更多的选择(除了Nested Loop Join，还可以选择Hash Join和Merge Join)。在Oceanbase中，没有依赖关系的子查询都会被改写成连接语句。对于有依赖关系的子查询，只有满足特定条件才会被改写成连接语句。下图分别展示了一个被改写成连接语句的子查询和一个没有被改写成连接语句的子查询。

    1. OceanBase (root@test)> create table t1(a int primary key, b int, c int);
    2. Query OK, 0 rows affected (0.70 sec)
    3. OceanBase (root@test)> create table t2(a int primary key, b int, c int);
    4. Query OK, 0 rows affected (0.92 sec)
    5. -- 有依赖关系的子查询被改写成了semi-join，并且使用了hash semi-join来实现
    6. OceanBase (root@test)> explain select * from t1 where t1.a in (select t2.c from t2 where t2.b = t1.b);
    7. | =======================================
    8. |ID|OPERATOR      |NAME|EST. ROWS|COST|
    9. ---------------------------------------
    10. |0 |HASH SEMI JOIN|    |1        |2924|
    11. |1 | TABLE SCAN   |t1  |1000     |455 |
    12. |2 | TABLE SCAN   |t2  |1000     |455 |
    13. =======================================
    15. Outputs & filters:
    16. -------------------------------------
    17.   0 - output([t1.a], [t1.b], [t1.c]), filter(nil),
    18.       equal_conds([t1.a = t2.c], [t2.b = t1.b]), other_conds(nil)
    19.   1 - output([t1.b], [t1.a], [t1.c]), filter(nil),
    20.       access([t1.b], [t1.a], [t1.c]), partitions(p0)
    21.   2 - output([t2.b], [t2.c]), filter(nil),
    22.       access([t2.b], [t2.c]), partitions(p0)
    23. -- 有依赖关系的子查询不能被改写成semi-join，使用了subplan filter来实现
    24. OceanBase (root@test)> explain select * from t1 where t1.a > (select sum(t2.c) from t2 where t2.b = t1.b);
    25. |ID|OPERATOR        |NAME|EST. ROWS|COST  |
    26. -------------------------------------------
    27. |0 |SUBPLAN FILTER  |    |334      |207683|
    28. |1 | TABLE SCAN     |t1  |334      |176   |
    29. |2 | SCALAR GROUP BY|    |1        |623   |
    30. |3 |  TABLE SCAN    |t2  |2        |622   |
    31. ===========================================
    33. Outputs & filters:
    34. -------------------------------------
    35.   0 - output([t1.a], [t1.b], [t1.c]), filter([t1.a > subquery(1)]),
    36.       exec_params_([t1.b]), onetime_exprs_(nil), init_plan_idxs_(nil)
    37.   1 - output([t1.b], [t1.a], [t1.c]), filter(nil),
    38.       access([t1.b], [t1.a], [t1.c]), partitions(p0)
    39.   2 - output([T_FUN_SUM(t2.c)]), filter(nil),
    40.       group(nil), agg_func([T_FUN_SUM(t2.c)])
    41.   3 - output([t2.c]), filter([t2.b = ?]),
    42.       access([t2.b], [t2.c]), partitions(p0)