このページは機械翻訳したものです。

13.2.11.9 ラテラル導出テーブル

導出テーブルは、通常、同じ FROM 句内の前述のテーブルのカラムを参照 (依存) することはできません。 MySQL 8.0.14 では、導出テーブルを横導出テーブルとして定義して、このような参照が許可されるように指定できます。

非ラテラル導出テーブルは、セクション13.2.11.8「導出テーブル」 で説明されている構文を使用して指定します。 ラテラル導出テーブルの構文は、導出テーブルの指定の前にキーワード LATERAL が指定されている点を除き、非ラテラル導出テーブルの構文と同じです。 LATERAL キーワードは、ラテラル導出テーブルとして使用される各テーブルの前に指定する必要があります。

ラテラル導出テーブルには、次の制限事項があります:

• ラテラル導出テーブルは、カンマで区切られたテーブルのリストまたは結合指定 (JOIN, INNER JOIN, CROSS JOIN, LEFT [OUTER] JOIN または RIGHT [OUTER] JOIN) のいずれかで、FROM 句でのみ使用できます。

• ラテラル導出テーブルが結合句の右オペランドにあり、左オペランドへの参照が含まれている場合、結合操作は INNER JOIN、CROSS JOIN または LEFT [OUTER] JOIN である必要があります。

  テーブルが左オペランドにあり、右オペランドへの参照が含まれている場合、結合操作は INNER JOIN、CROSS JOIN または RIGHT [OUTER] JOIN である必要があります。

• ラテラル導出テーブルが集計関数を参照する場合、関数集計クエリーを、ラテラル導出テーブルが発生する FROM 句を所有するクエリーにすることはできません。

• SQL 標準に従って、テーブル関数には暗黙的な LATERAL があるため、8.0.14 より前の MySQL 8.0 バージョンと同様に動作します。 ただし、標準に従って、LATERAL ワードは暗黙的であっても JSON_TABLE() の前には許可されません。

次の説明では、潜在的導出テーブルによって、非潜在的導出テーブルで実行できない特定の SQL 操作や、より効率的な回避策を必要とする特定の SQL 操作がどのように行われるかを示します。

この問題を解決するとします: 営業部隊内の個人のテーブル (各行に販売部隊のメンバーが記述されている) と、すべての売上のテーブル (各行に販売が記述されている) があるとします: 営業担当、顧客、金額、日付) は、各営業担当の最大販売の規模と顧客を決定します。 この問題には 2 つの方法があります。

問題を解決する最初のアプローチ: 各営業担当について、最大販売サイズを計算し、この最大値を指定した顧客も検索します。 MySQL では、次のように実行できます:

SELECT
  salesperson.name,
  -- find maximum sale size for this salesperson
  (SELECT MAX(amount) AS amount
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id)
  AS amount,
  -- find customer for this maximum size
  (SELECT customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    AND all_sales.amount =
         -- find maximum size, again
         (SELECT MAX(amount) AS amount
           FROM all_sales
           WHERE all_sales.salesperson_id = salesperson.id))
  AS customer_name
FROM
  salesperson;

このクエリーでは、営業担当ごとに最大サイズが 2 回 (最初のサブクエリーで 1 回、2 回目で) 計算されるため、非効率的です。

次の変更されたクエリーに示すように、営業担当ごとに最大数を計算し、それを導出テーブルで「「キャッシュ」」することで、効率性向上を試みることができます:

SELECT
  salesperson.name,
  max_sale.amount,
  max_sale_customer.customer_name
FROM
  salesperson,
  -- calculate maximum size, cache it in transient derived table max_sale
  (SELECT MAX(amount) AS amount
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id)
  AS max_sale,
  -- find customer, reusing cached maximum size
  (SELECT customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    AND all_sales.amount =
        -- the cached maximum size
        max_sale.amount)
  AS max_sale_customer;

ただし、導出テーブルは同じ FROM 句の他のテーブルに依存できないため、SQL-92 ではクエリーは無効です。 導出テーブルは、クエリー期間中は一定である必要があり、他の FROM 句テーブルのカラムへの参照は含まれません。 前述のとおり、クエリーでは次のエラーが生成されます:

ERROR 1054 (42S22): Unknown column 'salesperson.id' in 'where clause'

SQL:1999 では、導出テーブルの前に LATERAL キーワード (「「この導出テーブルは左側の前のテーブルに依存しています」」を意味する) がある場合、クエリーは有効になります:

SELECT
  salesperson.name,
  max_sale.amount,
  max_sale_customer.customer_name
FROM
  salesperson,
  -- calculate maximum size, cache it in transient derived table max_sale
  LATERAL
  (SELECT MAX(amount) AS amount
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id)
  AS max_sale,
  -- find customer, reusing cached maximum size
  LATERAL
  (SELECT customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    AND all_sales.amount =
        -- the cached maximum size
        max_sale.amount)
  AS max_sale_customer;

ラテラル導出テーブルは定数である必要はなく、それが依存する前のテーブルの新しい行が最上位のクエリーによって処理されるたびに最新になります。

問題を解決するための第 2 のアプローチ: SELECT リストのサブクエリーが複数のカラムを返す可能性がある場合は、別の解決策を使用できます:

SELECT
  salesperson.name,
  -- find maximum size and customer at same time
  (SELECT amount, customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    ORDER BY amount DESC LIMIT 1)
FROM
  salesperson;

これは効率的ですが、不正です。 このようなサブクエリーは単一のカラムのみを返すことができるため、機能しません:

ERROR 1241 (21000): Operand should contain 1 column(s)

クエリーをリライトするには、導出テーブルから複数のカラムを選択します:

SELECT
  salesperson.name,
  max_sale.amount,
  max_sale.customer_name
FROM
  salesperson,
  -- find maximum size and customer at same time
  (SELECT amount, customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    ORDER BY amount DESC LIMIT 1)
  AS max_sale;

ただし、これも機能しません。 導出テーブルは salesperson テーブルに依存しているため、LATERAL なしで失敗します:

ERROR 1054 (42S22): Unknown column 'salesperson.id' in 'where clause'

LATERAL キーワードを追加すると、クエリーは有効になります:

SELECT
  salesperson.name,
  max_sale.amount,
  max_sale.customer_name
FROM
  salesperson,
  -- find maximum size and customer at same time
  LATERAL
  (SELECT amount, customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    ORDER BY amount DESC LIMIT 1)
  AS max_sale;

つまり、LATERAL は、前述の 2 つのアプローチにおけるすべての欠点に対する効率的なソリューションです。