【ITニュース解説】What is the difference between subqueries, common table expressions (CTEs), and stored procedures?

システムエンジニアを目指す上で、リレーショナルデータベースの操作は避けて通れない重要なスキルだ。SQL（Structured Query Language）を使ってデータを効率的に管理・操作するために、いくつかの強力な仕組みが提供されている。その中でも、特に頻繁に使われるのが「サブクエリ」、「共通テーブル式（CTE）」、そして「ストアドプロシージャ」である。これらはどれもSQLの文法の一部であり、データを扱ったり加工したりする際に役立つが、それぞれ異なる特性と最適な用途を持っている。これらの違いを理解することは、より洗練されたSQLを書き、効率的なデータベース操作を行う上で非常に重要となる。

まず「サブクエリ」について解説する。サブクエリとは、簡単に言えば「クエリ（問い合わせ）の中に、さらに別のクエリを入れ子にして埋め込んだもの」を指す。この内側のクエリは、外側のメインクエリがデータを処理するために必要な一時的な結果を提供する。サブクエリは通常、括弧で囲まれ、SELECT句、WHERE句、FROM句など、さまざまなSQLの句の中で利用できる。 主な用途は、単一のクエリ内で、一時的な計算結果やフィルタリングの基準が必要な場合だ。例えば、ある条件を満たすデータの平均値を計算し、その平均値を使ってさらに別のデータを絞り込むといったケースでよく利用される。 サブクエリの具体的な特徴として、まず内側のサブクエリが実行され、その結果が外側のクエリによって利用されるという実行順序が挙げられる。また、外側のクエリの各行に依存して実行される「相関サブクエリ」と、外側のクエリとは独立して一度だけ実行される「非相関サブクエリ」の二種類がある。サブクエリは基本的に「一度使ったら終わり」という単一用途で、別のクエリで再利用することはできない。そのため、複雑なクエリで同じようなサブクエリを多用すると、繰り返し実行される可能性があり、データベースの性能に影響を与えることがある点には注意が必要だ。 例を挙げると、従業員テーブルから、平均給与よりも高い給与の従業員を探す場合を考えてみよう。

SELECT EmployeeID, FirstName, Salary FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

このSQL文では、WHERE Salary > (...)の括弧の中にある(SELECT AVG(Salary) FROM Employees)がサブクエリにあたる。このサブクエリはまずEmployeesテーブル全体のSalary（給与）の平均値を計算し、その一つの数値を返す。外側のクエリは、その平均値を使って「給与が平均給与よりも高い従業員」だけをフィルタリングして表示する。このように、サブクエリは外側のクエリが処理を進めるために、一時的に必要なデータを計算したり、取得したりする目的で使われるのである。

次に「共通テーブル式（CTE）」について説明する。CTEは「Common Table Expression」の略で、WITH句を使って定義する「一時的な名前付きの結果セット」のことだ。CTEは通常のテーブルと同じように扱うことができ、その名前を使ってメインクエリの中で何度も参照できる点が大きな特徴である。 CTEの主な用途は、複雑なクエリの可読性を高めたい場合や、中間的な結果を同一クエリ内で再利用したい場合に非常に有効だ。特に、自己参照的なデータ（例えば組織の階層構造）を扱う「再帰クエリ」を書く際にその力を発揮する。 特徴としては、サブクエリと比べてクエリが読みやすくなり、管理しやすくなる点が挙げられる。複雑な処理を小さな論理的なステップに分割して名前を付けられるため、クエリの意図が明確になるのだ。また、サブクエリと異なり、同じクエリの中で複数回参照できるため、中間結果を何度も計算する必要がなく、効率的である。再帰的なクエリをサポートする機能も持つが、サブクエリと同様に、そのクエリの実行中のみ存在する一時的なものであり、データベースに永続的に保存されるわけではない。 再び、平均給与よりも高い給与の従業員を探す例をCTEで見てみよう。

WITH AvgSalary AS ( SELECT AVG(Salary) AS AvgSal FROM Employees ) SELECT EmployeeID, FirstName, Salary FROM Employees, AvgSalary WHERE Salary > AvgSalary.AvgSal;

この例では、WITH AvgSalary AS (...)という部分がCTEだ。ここではAvgSalaryという名前を付け、その中でEmployeesテーブルの平均給与を計算している。そして、そのAvgSalaryという名前で定義した結果セットを、メインクエリのFROM Employees, AvgSalaryという部分で参照している。メインクエリは、EmployeesテーブルとAvgSalaryという一時的な結果セットを結合し、WHERE Salary > AvgSalary.AvgSalという条件で、給与が平均給与よりも高い従業員をフィルタリングする。この方法は、サブクエリの例と同じ結果を得られるが、平均給与を計算する部分がAvgSalaryという明確な名前で独立しているため、クエリの各部分の役割が分かりやすく、より読みやすい構造になっている。

最後に「ストアドプロシージャ」について説明する。ストアドプロシージャは、「データベースにあらかじめ保存（ストア）された、一連のSQL文のまとまり」のことだ。名前を付けて保存され、必要に応じて呼び出して実行できる。単なるSQL文の集まりだけでなく、外部から「引数（パラメータ）」を受け取ったり、「条件分岐（IF文）や繰り返し（WHILE文）といったプログラムのようなロジック」を含めたりできる点が、サブクエリやCTEとの決定的な違いである。 ストアドプロシージャの主な用途は、繰り返し実行される複雑な操作や、ビジネスロジックをデータベース側で一元的に管理したい場合だ。例えば、データの一括処理、レポート生成、セキュリティを考慮した特定のデータアクセスなどで非常に役立つ。 特徴として、まずデータベースに永続的に保存され、複数のセッションやアプリケーションから何度でも再利用できる点が挙げられる。これにより、同じ処理を複数の場所で書く手間を省き、コードの一貫性を保つことができる。次に、条件分岐やループ、エラー処理といった手続き型ロジックを組み込めるため、より複雑で動的な処理を実行できる。また、引数を受け取ることで、実行時に異なる値を渡して処理をカスタマイズすることが可能だ。さらに、ストアドプロシージャは事前にデータベースによってコンパイルされているため、実行時にSQL文を解析する手間が省かれ、性能が向上するというメリットがある。セキュリティ面でも、ユーザーに直接テーブルへのアクセス権限を与えるのではなく、ストアドプロシージャの実行権限だけを与えることで、ユーザーが実行できる操作の範囲を制御し、不正なデータ操作を防ぐことができる。 従業員の給与を更新するストアドプロシージャの例を見てみよう。

CREATE PROCEDURE UpdateEmployeeSalary @EmployeeID INT, @NewSalary DECIMAL(10, 2) AS BEGIN UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID; END; EXEC UpdateEmployeeSalary @EmployeeID = 101, @NewSalary = 75000.00;

この例は、UpdateEmployeeSalaryという名前のストアドプロシージャを作成している。このプロシージャは、@EmployeeID（従業員ID）と@NewSalary（新しい給与額）という二つの引数を受け取るように定義されている。BEGIN ... ENDの間に書かれたUPDATE文が、実際に従業員の給与を更新する処理だ。このプロシージャを一度作成すれば、EXEC UpdateEmployeeSalary @EmployeeID = 101, @NewSalary = 75000.00;のように、その名前と必要な引数を指定して、いつでも呼び出すことができる。例えば、特定の従業員の給与を更新したい場合、毎回複雑なUPDATE文を書く代わりに、このプロシージャを呼び出すだけで済む。これは、同じ処理を何度も繰り返し実行する場合に、コードの再利用性を高め、誤りを減らす上で非常に有効である。

これらの3つのツールは、それぞれ「一時的な計算やフィルタリング」「複雑なクエリの可読性向上」「繰り返し実行される複雑なビジネスロジックの管理」という異なる目的で使われる。サブクエリは、シンプルで単発の用途に適しており、手軽に一時的なデータを利用したい場合に便利だ。CTEは、同一クエリ内で中間結果を再利用し、複雑なクエリの構造を分かりやすくしたり、再帰的な処理を行ったりするのに役立つ。そしてストアドプロシージャは、データベースに永続的に保存され、複雑な処理やビジネスロジックを一元管理し、パフォーマンスとセキュリティを高めるのに最適だ。それぞれの特性を理解し、状況に応じて適切なツールを選ぶことが、効率的で保守性の高いSQLの記述と、堅牢なデータベース管理につながるだろう。