ラテラル派生テーブル

本記事では、ラテラル派生テーブルの基本概念、使用上の制限、関連する例を紹介します。

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

派生テーブル（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

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