サブクエリ

サブクエリとは、上位のクエリ内にネストされたクエリを指します。上位のクエリは一般的に親クエリまたは外部クエリと呼ばれます。サブクエリの結果は「親クエリ」または「外部クエリ」に入力として渡されます。親クエリはこの値を計算に組み込み、最終的な出力を決定します。

SQL言語では、複数層のネストされたクエリが可能です。つまり、1つのサブクエリの中にさらに他のサブクエリをネストすることができます。また、サブクエリはSQL文の様々な句、例えば SELECT 文、FROM 文、WHERE 文などに記述できます。

サブクエリ

データベースにおいて、サブクエリは依存関係のあるサブクエリと依存関係のないサブクエリに分けられます。依存関係のあるサブクエリとは、そのサブクエリの実行が外部クエリの変数に依存しているため、通常は複数回計算されるものを指します。依存関係のないサブクエリとは、そのサブクエリの実行が外部クエリの変数に依存しないため、一般的には一度だけ計算すればよいものを指します。以下の例では、それぞれ依存関係のないサブクエリと依存関係のあるサブクエリを示しています。

• 依存関係のないサブクエリ：

  obclient> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);
  

• 依存関係のあるサブクエリ。サブクエリ内で外部クエリの変数 t1.b が使用されています：

  obclient> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM T2 WHERE t2.b = t1.b);
  

スカラーサブクエリ

単一の要素を返すサブクエリをスカラーサブクエリと呼び、その結果はスカラー演算子として使用でき、データ型、長さ、NULL を取り得るかどうかなど、スカラー演算子の基本的な特性を持ちます。例えば、以下のクエリが返す結果は、スカラー演算子として使用できます：

obclient> CREATE TABLE t1 (c1 INTEGER, c2 VARCHAR(20) NOT NULL);
obclient> INSERT INTO t1 VALUES (1, 'OceanBase');
obclient> SELECT (SELECT c2 FROM t1);
+---------------------+
| (SELECT c2 FROM t1) |
+---------------------+
| OceanBase           |
+---------------------+
1 row in set

上記のクエリは、テナントのデフォルト値に従って、VARCHAR 型、長さ 20 の値 OceanBase を返します。文字セットと照合順序はテナントのデフォルト値に従います。スカラーサブクエリで選択された値のNULL許容性はコピーされません。c2 列には NOT NULL 制約がありますが、サブクエリの結果がNULLの場合、結果は NULL を返します。

単一の要素を返すものの、スカラーサブクエリはすべての文で使用できるわけではありません。文がリテラルのみを許容する場合は、スカラーサブクエリで置き換えることはできません。

以下の例は、スカラーサブクエリをより深く理解するのに役立ちます。

このクエリが 2 を返すのは、テーブル t2 に t1 と同名の列 c1 が含まれているためです。

obclient> CREATE TABLE t1 (c1 INT);
obclient> INSERT INTO t1 VALUES (1);
obclient> CREATE TABLE t2 (c1 INT);
obclient> INSERT INTO t2 VALUES (2);
obclient> SELECT (SELECT c1 FROM t2) FROM t1;
+---------------------+
| (SELECT s1 FROM t2) |
+---------------------+
|                   2 |
+---------------------+
1 row in set

また、スカラーサブクエリは式の一部にもなり得ます。例えば：

obclient> CREATE TABLE t1 (c1 INTEGER, c2 VARCHAR(20) NOT NULL);
obclient> INSERT INTO t1 VALUES (1, 'OceanBase');
obclient> SELECT HEX((SELECT c2 FROM t1 WHERE c1=1)) FROM t1;
+-------------------------------------+
| HEX((SELECT c2 FROM t1 WHERE c1=1)) |
+-------------------------------------+
| 4F6365616E42617365                  |
+-------------------------------------+
1 row in set

サブクエリの比較

サブクエリの一般的な構文は以下のとおりです：

operand operator {ANY | SOME | ALL} (subquery)
operand LIKE (subquery)
operand {IN | NOT IN | EXISTS | NOT EXISTS} (subquery)

ここで、operand はサブクエリの結果と比較する演算子であり、operator は以下の演算子を使用できます：

• 等しい（= または <=>）

• 大于（>）

• 小于（<）

• 大于等于（>=）

• 小于等于（<=）

• 等しくない（!= または <>）

• LIKE は文字列の比較に使用されます。

例えば、以下のサブクエリは、t1 の c1 列が t2 の最大値である c2 列と同じ値を持つ行を検索するために使用されます。このクエリは、t1 と t2 の結合に書き換えることはできません。

obclient> CREATE TABLE t1 (c1 INT);
obclient> INSERT INTO t1 VALUES (1);
obclient> CREATE TABLE t2 (c2 INT);
obclient> INSERT INTO t2 VALUES (2);
obclient> SELECT * FROM t1 WHERE c1 = (SELECT MAX(c2) FROM t2);

明らかに、直接比較に使用できるのはスカラーサブクエリの結果だけです。サブクエリが1列の値を返す場合は、集合を処理する必要があります。OceanBaseデータベースは、集合をスカラー値に集約するための以下の方法を提供しています：

• 集計関数：集合内のすべての値を1つの値に集計し、その値と演算子を比較します。

• ANY | SOME：集合内のすべての値と演算子を比較し、比較条件を満たす行が存在する場合は TRUE を返します。SOME は ANY のエイリアスです。

• ALL：集合内のすべての値と演算子を比較し、すべての行が比較条件を満たす場合は TRUE を返します。

• IN：演算子が集合に属する場合は TRUE を返します。IN は = ANY と等価です。

• NOT IN：演算子が集合に属さない場合は TRUE を返します。NOT IN は <> ALL と等価です。

• EXIST：サブクエリが行を返す場合は TRUE を返します。

• NOT EXIST：サブクエリが行を返さない場合は TRUE を返します。

行サブクエリ

スカラーサブクエリや列サブクエリはスカラーまたは1列の値を返しますが、行サブクエリは単一の行を返すため、複数列の値を返すことができます。

例えば、以下のサブクエリは単一行のみを返します：

obclient> CREATE TABLE t1(id INTEGER PRIMARY KEY,c1 INTEGER,c2 INTEGER);
obclient> CREATE TABLE t2(id INTEGER PRIMARY KEY,c3 INTEGER,c4 INTEGER);
obclient> SELECT * FROM t1
 WHERE ROW(c1,c2) = (SELECT c3, c4 FROM t2 WHERE id = 10);

もし c3,c4 の値が t1 の任意の c1,c2 値のペアと等しい場合、条件を満たすすべての c1,c2 を返します。存在しない場合は空集合を返します。さらに、サブクエリで WHERE 条件が指定されていない場合、複数行を返す可能性があり、その際はクエリエラーが発生します。行サブクエリは単一行のみを返すことができます。

ROW(c1,c2) 式は (c1,c2) と略記できます。ROW() は行構築関数と呼ばれます。行構築関数とサブクエリが返す行は、同じ数の値を含む必要があります。行構築関数は、2列以上を返すサブクエリと比較するために使用されます。行構築関数は、単一列を返す列サブクエリと同時に使用することはできません。例えば、以下のクエリは構文エラーを返します：

obclient> SELECT * FROM t1 WHERE ROW(1) = (SELECT column1 FROM t2);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ') = (SELECT c1 FROM t2)' at line 1

行構築関数はオプティマイザー内で展開されます。例えば、以下の2つのSQLは等価です：

obclient> SELECT * FROM t1 WHERE (c1,c2) = (1,1);
obclient> SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1;

関連サブクエリ

サブクエリが親クエリ内の列を参照する場合、それを関連サブクエリと呼びます。例えば、以下のように、サブクエリの WHERE 条件で t1 の c2 列を参照している場合です：

obclient> SELECT * FROM t1 WHERE c1 IN (SELECT c3 FROM t2 WHERE t2.c4 = t1.c2);

このようなステートメントでは、クエリは内側から外側へと評価されます。例えば、以下の書き方では、x.c2 は t2 の列でなければならず、t1 の列ではありません。これは、SELECT c1 FROM t2 AS x ステートメントが t2 に対して先にリネームされ、SELECT c1 FROM t1 が外部クエリであるためです。

obclient> SELECT c1 FROM t1 AS x
  WHERE x.c1 = (SELECT c1 FROM t2 AS x
    WHERE x.c1 = (SELECT c1 FROM t3
      WHERE x.c2 = t3.c1));

HAVING または ORDER BY 句内のサブクエリについても、OceanBaseデータベースは外部 SELECT リスト内で列名を検索します。

関連サブクエリ内の集約関数が外部参照を含む場合、その関数は外部参照のみを含む必要があり、他の関数や式の内部に含めることはできません。

派生テーブル

派生テーブルとは、クエリの FROM 句の範囲内でテーブルを生成する式のことです。例えば、SELECT ステートメントの FROM 句内のクエリが派生テーブルとなる場合があります：

SELECT ... FROM (subquery) [AS] table_name ...

FROM 句の派生テーブルにおいて、[AS] table_name はオプションのエイリアスではなく、サブクエリの結果セットに指定する必要がある名前です。派生テーブルのすべての列には、個別の名前を付ける必要があります。

派生テーブルは、段階的な計算によく使用されます。例えば、以下のクエリはグループ化後の各グループの平均値を計算します：

obclient> SELECT AVG(sum_c1)
FROM (SELECT SUM(c1) AS sum_cumn1
FROM t1 GROUP BY c1) AS t1;

派生テーブルは、スカラー、列、行、またはテーブルを返すことができます。

派生テーブルには以下の制限があります：

• 派生テーブルは関連サブクエリにすることはできません。

• 派生テーブルには、同一の SELECT ステートメント内の他のテーブルへの参照を含めることはできません。

• 派生テーブルには外部参照を含めることはできません。

ラテラル派生テーブル（Lateral Derived Table）

ラテラル派生テーブルとは、FROM 句内で、FROM 列挙において先行して定義されたテーブルや派生テーブルを参照できる特性を指します。

ラテラル派生テーブルは、特にサブクエリが外部クエリ内の列に依存する場合、サブクエリの使用においてより高い柔軟性と効率を提供します。OceanBaseデータベース（MySQLモード）V4.2.2バージョンから、この機能をサポートするために LATERAL キーワードが導入されました。

例：

SELECT *
FROM A, LATERAL (SELECT * FROM B WHERE B.col1 = A.col1) AS derived_table;

ラテラル派生テーブルの使用方法の詳細については、ラテラル派生テーブルを参照してください。

サブクエリの展開 (Subquery Unnesting)

サブクエリの展開は、データベースにおける最適化戦略の一つであり、一部のサブクエリを外部の親クエリ内に配置することで、実質的に特定のサブクエリを等価な複数テーブル結合操作に変換します。この戦略がもたらす明確な利点は、アクセスパス、結合方法、結合順序を効果的に活用できる可能性があり、クエリ文の階層を可能な限り削減できることです。以下に、サブクエリが結合文に書き換えられた展開の例を示します。

obclient> CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected

obclient> CREATE TABLE t2(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected

• 依存関係のないサブクエリ。

  obclient> EXPLAIN SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);
  | ======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST  |
  --------------------------------------
  |0 |MERGE JOIN |    |100001   |131664|
  |1 | TABLE SCAN|t1  |100000   |48372 |
  |2 | TABLE SCAN|t2  |100000   |41911 |
  ======================================
  ...
  

• 依存関係のあるサブクエリが展開され、結合文に書き換えられる。

  obclient> EXPLAIN SELECT * FROM t1 WHERE T1.A IN (SELECT T2.B FROM T2 WHERE T2.C = T1.C);
  | ===========================================
  |ID|OPERATOR        |NAME |EST. ROWS|COST  |
  --------------------------------------------
  |0 |MERGE JOIN      |     |9703     |215436|
  |1 | TABLE SCAN     |t1   |100000   |64066 |
  |2 | SORT           |     |10001    |129621|
  |3 |  SUBPLAN SCAN  |VIEW1|10001    |111242|
  |4 |   HASH DISTINCT|     |10001    |109862|
  |5 |    TABLE SCAN  |t2   |100000   |64066 |
  ============================================
  ...