サブクエリ

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

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 を返します。

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

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

このクエリは 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列以上を返すサブクエリと比較するために使用されます。行構築関数は、1列を返す列サブクエリと同時に使用することはできません。例えば、以下のクエリは構文エラーを返します：

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 は t1 の列ではなく t2 の列である必要があります。これは、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 |
  ============================================
  ...