Subquery - PolarDB - Alibaba Cloud ドキュメントセンター

PolarDB-X 1.0 は、ほとんどの MySQL サブクエリ構文をサポートしています。本トピックでは、クエリ失敗を引き起こす厳格な制限事項、APPLY 演算子（行単位実行）を用いて実行されるため大規模データセットで著しくパフォーマンスが低下するサブクエリパターン、およびサポートされるサブクエリの種類について説明します。

制限事項

以下のサブクエリパターンは PolarDB-X 1.0 でサポートされていません。

HAVING 句：サブクエリを HAVING 句内に記述することはできません。

-- サポートされていません
SELECT name, AVG(quantity)
FROM tb1
GROUP BY name
HAVING AVG(quantity) > 2 * (
   SELECT AVG(quantity)
   FROM tb2
);

JOIN ON 句：サブクエリを JOIN ON 句内に記述することはできません。

-- サポートされていません
SELECT * FROM tb1 p JOIN tb2 s ON (p.id = s.id AND p.quantity > ALL(SELECT quantity FROM tb3))

等号（=）の両辺に ROW サブクエリとスカラーサブクエリを同時に使用：ROW サブクエリとスカラーサブクエリを等号の両辺に同時に配置することはできません。
```
-- サポートされていません
SELECT * FROM tb1 WHERE ROW(id, name) = (SELECT id, name FROM tb2)
```

UPDATE SET 句：サブクエリを UPDATE SET 句内に記述することはできません。

-- サポートされていません
UPDATE t1 SET c1 = (SELECT c2 FROM t2 WHERE t1.c1 = t2.c1) LIMIT 10

パフォーマンスに関する考慮事項

PolarDB-X 1.0 では、一部のサブクエリは JOIN への変換が不可能であり、代わりに APPLY 演算子を使用して実行されます。APPLY 演算子は外部テーブルの各行に対してサブクエリを 1 回ずつ実行するため、大規模データセットではパフォーマンスが著しく低下します。以下に該当するパターンはこの動作をトリガーします。可能な場合は、これらのクエリを再設計してください。

OR 演算子とサブクエリの組み合わせ

WHERE 句内で OR とサブクエリを混在させると、OR 条件が結果セットを拡張する方式が JOIN のセマンティクスと互換しないため、PolarDB-X はサブクエリを JOIN に変換できません。

-- 効率的
SELECT * FROM tb1 WHERE id IN (SELECT id FROM tb2)
SELECT * FROM tb1 WHERE id IN (SELECT id FROM tb2) AND id > 3

-- 非効率的：OR により JOIN 変換が阻止されます
SELECT * FROM tb1 WHERE id IN (SELECT id FROM tb2) OR id > 3

関数内または非等価比較における相関項目

相関列が関数でラップされている場合、または = 以外の演算子で比較されている場合、PolarDB-X は JOIN を用いて相関を解決できません。関数または非等価演算子により、オプティマイザーがサブクエリを等結合に書き換えることが妨げられます。

-- 効率的
SELECT * FROM tb1 a WHERE id IN (SELECT id FROM tb2 b WHERE a.name = b.name)

-- 非効率的：相関列が関数内に含まれています
SELECT * FROM tb1 a WHERE id IN (SELECT id FROM tb2 b WHERE UPPER(a.name) = b.name)
SELECT * FROM tb1 a WHERE id IN (SELECT id FROM tb2 b WHERE a.decimal_test = ABS(b.decimal_test))

-- 非効率的：非等価比較演算子を使用しています
SELECT * FROM tb1 a WHERE id IN (SELECT id FROM tb2 b WHERE a.name != b.name)
SELECT * FROM tb1 a WHERE id IN (SELECT id FROM tb2 b WHERE a.name >= b.name)

サブクエリ内の OR による相関項目の接続

サブクエリ内の相関条件が他の条件と OR で接続されている場合、PolarDB-X はこれを JOIN に書き換えることができません。AND で接続された相関のみが等結合条件に変換可能です。

-- 効率的：相関条件に AND を使用
SELECT * FROM tb1 a WHERE id IN
  (SELECT id FROM tb2 b WHERE a.name = b.name AND b.date_test < '2015-12-02')

-- 非効率的：相関条件に OR を使用
SELECT * FROM tb1 a WHERE id IN
  (SELECT id FROM tb2 b WHERE a.name = b.name OR b.date_test < '2015-12-02')
SELECT * FROM tb1 a WHERE id IN
  (SELECT id FROM tb2 b WHERE a.name = b.name OR b.date_test = a.date_test)

相関項目を含むスカラーサブクエリ

外部クエリの列を参照するスカラーサブクエリは JOIN に変換できません。相関列がない場合、スカラーサブクエリは 1 回だけ実行されますが、相関列がある場合、外部クエリの各行に対して 1 回ずつ実行されます。

-- 効率的：外部テーブルとの相関はありません
SELECT * FROM tb1 a WHERE id > (SELECT id FROM tb2 b WHERE b.date_test < '2015-12-02')

-- 非効率的：スカラーサブクエリ内に相関項目があります
SELECT * FROM tb1 a WHERE id >
  (SELECT id FROM tb2 b WHERE a.name = b.name AND b.date_test < '2015-12-02')

相関レベルを跨ぐ相関項目

多段階サブクエリでは、各サブクエリの相関列は直近の外部クエリのみを参照できます。内部サブクエリの列が 2 段階以上上位のテーブルを参照する場合、相関が 1 レベルをスキップし、JOIN による解決ができなくなります。

-- 効率的：各サブクエリは直近の外部クエリのみと相関しています
SELECT * FROM tb1 a WHERE id IN (
  SELECT id FROM tb2 b
  WHERE a.name = b.name
    AND EXISTS (SELECT name FROM tb3 c WHERE b.address = c.address)
)

-- 非効率的：table c が table a と相関しており、table b をスキップしています
SELECT * FROM tb1 a WHERE id IN (
  SELECT id FROM tb2 b
  WHERE a.name = b.name
    AND EXISTS (SELECT name FROM tb3 c WHERE a.address = c.address)
)

説明非効率的な例では、table a と table b、および table b と table c が同一の相関レベルに属します。table a と table c の間の相関は相関レベルを跨いでおり、JOIN では解決できません。

相関項目が集約キーと一致しない GROUP BY サブクエリ

GROUP BY を含むサブクエリでは、各相関列は GROUP BY 句に必ず含まれる必要があります。含まれていない場合、集約キーと相関キーが不一致となるため、オプティマイザーはサブクエリを JOIN に書き換えることができません。

-- 効率的：相関項目 b.pk が GROUP BY 列 pk と一致しています
SELECT * FROM tb1 a WHERE EXISTS (
  SELECT pk FROM tb2 b
  WHERE a.pk = b.pk AND b.date_test = '2003-04-05'
  GROUP BY pk
)

-- 非効率的：相関項目 b.date_test が GROUP BY 列 pk と一致していません
SELECT * FROM tb1 a WHERE EXISTS (
  SELECT pk FROM tb2 b
  WHERE a.date_test = b.date_test AND b.date_test = '2003-04-05'
  GROUP BY pk
)

サポートされるサブクエリ

PolarDB-X 1.0 では、以下のサブクエリの種類がサポートされています。

サブクエリを使用した比較

比較演算子を使用して、値とサブクエリの結果を比較します。

構文：

非サブクエリオペランド 比較演算子 (サブクエリ)
-- 比較演算子： = > < >= <= <> != <=> LIKE

例：

SELECT * FROM tb1 WHERE 'a' = (SELECT column1 FROM t1)

説明サブクエリは比較演算子の右側に記述する必要があります。

ANY、ALL、IN/NOT IN、EXISTS/NOT EXISTS を用いたサブクエリ

構文：

オペランド 比較演算子 ANY (サブクエリ)
オペランド 比較演算子 ALL (サブクエリ)
オペランド IN (サブクエリ)
オペランド NOT IN (サブクエリ)
オペランド EXISTS (サブクエリ)
オペランド NOT EXISTS (サブクエリ)
-- 比較演算子： = > < >= <= <> !=

キーワード	動作
`ANY`	サブクエリが返す行のいずれかが式を満たす場合、`TRUE` を返します。
`ALL`	サブクエリが返すすべての行が式を満たす場合、`TRUE` を返します。
`IN`	`= ANY` と同等です。
`NOT IN`	`<> ALL` と同等です。
`EXISTS`	サブクエリが少なくとも 1 行を返す場合、`TRUE` を返します。
`NOT EXISTS`	サブクエリが少なくとも 1 行を返す場合、`FALSE` を返します。それ以外の場合は `TRUE` を返します。

例：

-- IN と = ANY は同等です
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

-- NOT IN と <> ALL は同等です
SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

-- EXISTS
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

説明 EXISTS は、サブクエリが NULL 行のみを返す場合でも TRUE を返します。

ROW サブクエリ

ROW サブクエリは、複数の列を同時にサブクエリの結果と比較します。

サポートされる演算子： = > < >= <= <> != <=>

例：

SELECT * FROM t1
  WHERE (col1, col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

SELECT * FROM t1
  WHERE ROW(col1, col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

上記の2つの文は等価です。t1 の行は、以下の条件を満たす場合にのみ返されます：

サブクエリが正確に 1 行のみを返す場合。複数行を返すとエラーが報告されます。
サブクエリによって返される値（col3、col4）は、プライマリテーブルの値（col1、col2）と等しくなります。

相関サブクエリ

相関サブクエリは、外部クエリの列を参照します。このサブクエリは、外部クエリの各行に対して再評価されます。

例：

SELECT * FROM t1
  WHERE column1 = ANY (SELECT column1 FROM t2
                       WHERE t2.column2 = t1.column2);

この例では、t2.column2 = t1.column2 が外部クエリの t1.column2 を参照しているため、これは相関サブクエリです。

派生テーブル（FROM 句内のサブクエリ）

派生テーブルとは、FROM 句内で使用されるサブクエリであり、エイリアスを指定する必要があります。

構文：

SELECT ... FROM (サブクエリ) [AS] tbl_name ...

制約事項：

派生テーブルには必ずエイリアスを指定する必要があります。
派生テーブルは、スカラー値、列、行、またはテーブルのいずれかを返すことができます。
派生テーブルは相関サブクエリであってはならず、外部クエリの列を参照してはなりません。

例：

テーブルを作成し、データを挿入します。

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1, '1', 1.0);
INSERT INTO t1 VALUES (2, '2', 2.0);

sb1 > 1 を満たす行をクエリします。結果は 2, '2', 4.0 です。

SELECT sb1, sb2, sb3
  FROM (SELECT s1 AS sb1, s2 AS sb2, s3 * 2 AS sb3 FROM t1) AS sb
  WHERE sb1 > 1;

集計関数は直接ネストできません。次の文はエラーを返します。

-- エラー：集計関数はネストできません
SELECT AVG(SUM(s1)) FROM t1 GROUP BY s1;

代わりに派生テーブルを使用して、グループ化された合計値の平均を計算します。結果は 1.5000 です。

SELECT AVG(sum_s1)
  FROM (SELECT SUM(s1) AS sum_s1 FROM t1 GROUP BY s1) AS t1;