ラテラル派生テーブル

本記事では、ラテラル派生テーブル（Lateral Derived Table）の基本概念、使用上の制限事項、および関連する例を紹介します。

ラテラル派生テーブルの概要

派生テーブル（Derived Table）とは、FROM 句内で使用されるサブクエリであり、このサブクエリによって生成された結果セットは一時的なテーブルとして外側のクエリで使用されます。派生テーブルは通常、データのグループ化や集計、または特定の条件を満たすデータセットを作成し、メインクエリでのさらなる処理を可能にするために使用されます。

一方、ラテラル派生テーブル（Lateral Derived Table）は、特殊なタイプの派生テーブルであり、LATERAL キーワードを使用することで、同一の FROM 句内で以前に定義された他のテーブルや派生テーブルのフィールドを参照できるようにします。これにより、派生テーブル内のサブクエリは、同一の FROM 句で定義されたテーブルに依存し、それらのテーブルの列値にアクセスすることが可能になります。

ラテラル派生テーブルの主な特徴は、FROM 句内で自身より前に定義されたテーブルの列を参照できる点です。通常の派生テーブルにはこの機能はありません。

ラテラル派生テーブル（LATERALキーワード）の制限

• LATERAL キーワードは FROM 句で使用する必要があります。カンマ区切りのテーブルリスト内、または結合式（JOIN、INNER JOIN、CROSS JOIN、LEFT [OUTER] JOIN、RIGHT [OUTER] JOIN など）内に記述できます。
• LATERAL が JOIN ステートメント内にあり、左側のテーブルのフィールドを参照する場合、許可される結合タイプは INNER JOIN、CROSS JOIN、および LEFT [OUTER] JOIN です。
• LATERAL が JOIN ステートメント内にあり、右側のテーブルのフィールドを参照する場合、有効な結合操作は INNER JOIN、CROSS JOIN、および RIGHT [OUTER] JOIN です。
• ラテラル派生テーブル内で集約関数を参照する場合、その集約関数はラテラル派生テーブルを含む FROM 句にある外部クエリに直接依存してはなりません。つまり、ラテラル派生テーブル内部では集約関数を使用して外部クエリの結果を直接参照することはできません。

LATERALキーワードの使用方法

SELECT select_list
FROM table_name1,
LATERAL (SELECT select_list
         FROM table_name2
         WHERE table_name2.col_name = table_name1.col_name) AS lateral_derived_table_name
[...];

パラメータ説明：

例

テストテーブルの作成とテストデータの追加

1. テーブル students を作成します。

   CREATE TABLE students (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(50) NOT NULL,
     age INT
   );
   

2. テーブル students に3件のデータを挿入します。

   INSERT INTO students (name, age)
     VALUES ('name1', 20),
     ('name2', 22),
     ('name3', 21);
   

3. テーブル scores を作成します。

   CREATE TABLE scores (
     id INT AUTO_INCREMENT PRIMARY KEY,
     student_id INT,
     subject VARCHAR(50) NOT NULL,
     score DECIMAL(5, 2),
     FOREIGN KEY (student_id) REFERENCES students(id)
   );
   

4. テーブル scores に9件のデータを挿入します。

   INSERT INTO scores (student_id, subject, score)
     VALUES (1, 'A', 86.5),
     (1, 'B', 90.0),
     (1, 'C', 91.5),
     (2, 'A', 86.0),
     (2, 'B', 92.0),
     (2, 'C', 89.5),
     (3, 'A', 93.0),
     (3, 'B', 92.5),
     (3, 'C', 85.0);
   

テストデータのクエリ

students と scores の2つのテーブルから、各学生の氏名、平均点、および最高点を取得します。

Q1：複数のサブクエリを使用して必要なデータを取得します。scores テーブルに対して2回の独立したグループ化と集計操作を行い、scores テーブルを2回スキャンして平均点と最高点をそれぞれ計算します。その後、WHERE 句を使用して結果を students テーブルと結合します。

SELECT st.name, sc.avg_score, scs.max_score
FROM students st,
  (SELECT student_id, AVG(score) avg_score
   FROM scores
   GROUP BY student_id) sc,
   (SELECT student_id, MAX(score) max_score
   FROM scores
   GROUP BY student_id) scs
WHERE sc.student_id = st.id
AND scs.student_id = st.id;

実行結果は次のとおりです：

+-------+-----------+-----------+
| name  | avg_score | max_score |
+-------+-----------+-----------+
| name1 | 89.333333 |     91.50 |
| name2 | 89.166667 |     92.00 |
| name3 | 90.166667 |     93.00 |
+-------+-----------+-----------+
3 rows in set

Q2：LATERAL キーワードを使用して必要なデータを取得します。LATERAL キーワード（横方向派生テーブル）を使用することで、1つのSQL文で各学生の平均点と最高点を同時に計算できます。また、各学生の ID に対して scores テーブルを1回だけスキャンします。

SELECT st.name, ld_tbl.avg_score, ld_tbl.max_score
FROM students st,
  LATERAL (SELECT AVG(score) avg_score, MAX(score) max_score
           FROM scores sc
           WHERE sc.student_id = st.id) ld_tbl;

実行結果は次のとおりです：

+-------+-----------+-----------+
| name  | avg_score | max_score |
+-------+-----------+-----------+
| name1 | 89.333333 |     91.50 |
| name2 | 89.166667 |     92.00 |
| name3 | 90.166667 |     93.00 |
+-------+-----------+-----------+
3 rows in set

Q2はQ1と比較して、大規模なデータセットを処理する際に高いパフォーマンスを発揮する可能性があります。これは、scores テーブルの重複スキャンを回避するためです。Q2の書き方はより明確で簡潔であり、理解しやすく、メンテナンスも容易です。

関連ドキュメント

• SELECT

• クエリでの集約関数の使用