ラテラル派生テーブル

本記事では、ラテラル派生テーブル（Lateral Derived Table）の基本概念、構文を紹介し、関連する例を示します。

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

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

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

ラテラル派生テーブルの主な特徴は、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 NUMBER PRIMARY KEY,
     name VARCHAR2(50) NOT NULL,
     age NUMBER
   );
   

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

   INSERT INTO students
     VALUES (1, 'name1', 20),
     (2, 'name2', 22),
     (3, 'name3', 21);
   

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

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

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

   INSERT INTO scores
     VALUES (1, 1, 'A', 86.5),
     (2, 1, 'B', 90.0),
     (3, 1, 'C', 91.5),
     (4, 2, 'A', 86.0),
     (5, 2, 'B', 92.0),
     (6, 2, 'C', 89.5),
     (7, 3, 'A', 93.0),
     (8, 3, 'B', 92.5),
     (9, 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.33333333333333333333333333333333333333 |      91.5 |
| name2 | 89.16666666666666666666666666666666666667 |        92 |
| name3 | 90.16666666666666666666666666666666666667 |        93 |
+-------+-------------------------------------------+-----------+
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.33333333333333333333333333333333333333 |      91.5 |
| name2 | 89.16666666666666666666666666666666666667 |        92 |
| name3 | 90.16666666666666666666666666666666666667 |        93 |
+-------+-------------------------------------------+-----------+
3 rows in set

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

関連ドキュメント

• SELECT

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