ラテラル派生テーブル

本記事では、ラテラル派生テーブル（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
• クエリでの集約関数の使用