0Pricing
Coding Interview Prep · レッスン

FROM句のサブクエリ（派生テーブル）

クエリを仮想テーブルとして包む方法と、エイリアスが必須である理由を学びます。

「FROM句のサブクエリ（派生テーブル）」はCoddyKit上の無料Coding Interview Prepレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはCoding Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Coding Interview Prepコースには全4レッスンが含まれています。

派生テーブルとは何か

FROM 句内のサブクエリは派生テーブル（またはインラインビュー）と呼ばれます。単一の値を返すのではなく、外側のクエリが実際のテーブルであるかのように扱える結果セット全体を返します。

  • 多数の行と多数の列を持つことができます。
  • 通常のテーブルと同じように、クエリ、結合、フィルタリングを行えます。

面接官は派生テーブルを使って、問題を段階に分けて解決できるかを確認します。

エイリアスは必須です

最大の落とし穴は、派生テーブルに必ずエイリアスを付けなければならないことです。エイリアスがないと、ほとんどのデータベースエンジンはクエリを拒否します。

  • MySQL: Every derived table must have its own alias。
  • Postgres: subquery in FROM must have an alias。

ここでは dept_avg という名前を付けているため、その名前で列を参照できます。

SELECT dept_avg.dept_id, dept_avg.avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS dept_avg;

派生テーブルで事前集約する理由

面接でよく出る問題に、各従業員を、その所属部門の平均給与とともに表示してくださいというものがあります。グループ化に関する問題が生じるため、詳細行と集約結果を直接混在させることはできません。

すっきりした方法は、派生テーブルで部門ごとの平均を計算してから、詳細行に結合することです。まず派生テーブルを部門ごとに 1 行へ集約します。

SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d ON e.dept_id = d.dept_id;

集約結果をフィルタリングする

派生テーブルを使うと、外側のクエリで HAVING を複雑に使わず、計算した集約結果をフィルタリングできます。平均給与が 60000 を超える部門だけを取得するとします。

内側で集約してから、外側で派生列に通常の WHERE を適用します。外側のクエリからは、avg_salary が通常の列として見えます。

SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d
WHERE avg_salary > 60000;

2 段階の集約

派生テーブルは、集約結果をさらに集約する必要がある場合に力を発揮します。面接でよく出る質問に、部門ごとの平均給与の平均はいくらですかというものがあります。

AVG(AVG(...)) のように直接入れ子にすることはできません。内側のクエリで部門ごとに 1 つの平均を生成し、外側のクエリでそれらを平均します。

SELECT AVG(avg_salary) AS avg_of_dept_avgs
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d;

計算列に名前を付ける

派生テーブル内の式を外側で参照したい場合は、エイリアスが必要です。内側の salary * 12 には、エイリアスを付けなければデータベースが割り当てた名前が付くため、その名前を当てにすることはできません。

計算列には必ずエイリアスを付けてください。エイリアスのない式を参照し、存在しない可能性のある列名を想定していると、面接官はすぐに気付きます。

SELECT name, annual_salary
FROM (
  SELECT name, salary * 12 AS annual_salary
  FROM employees
) AS yearly
WHERE annual_salary > 100000;

2 つの派生テーブルを結合する

複数の派生テーブルを互いに結合できます。ここでは、事前に集約した 2 つのサブクエリを結合し、各部門の従業員数と給与総額を比較しています。

各派生テーブルが 1 つの小さな問題に答え、結合によって最終的なレポートにまとめられます。このように段階に分けて考える力が、まさに中級レベルの面接で評価されます。

SELECT c.dept_id, c.headcount, p.payroll
FROM (
  SELECT dept_id, COUNT(*) AS headcount
  FROM employees GROUP BY dept_id
) AS c
JOIN (
  SELECT dept_id, SUM(salary) AS payroll
  FROM employees GROUP BY dept_id
) AS p ON c.dept_id = p.dept_id;

スコープ：外側のクエリから内側は見えません

重要なルールがあります。外側のクエリから参照できるのは、派生テーブルがSELECT リストで公開している列だけです。サブクエリ内でのみ使用されている列は、外側からは見えません。

内側のクエリが dept_id と avg_salary を選択している場合、salary や name は外側では使用できません。これらは集約によって消費されているためです。面接官はこのスコープの境界を確認します。

SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees GROUP BY dept_id
) AS d;

派生テーブルと CTE

派生テーブルと Common Table Expression（CTE）は、同じ実行計画になることがよくあります。面接官から、一方を選ぶ理由を尋ねられることがあります。

  • 派生テーブル：インラインで記述でき、1 回限りの用途に適しています。
  • CTE（WITH）：先頭で名前を付けるため読みやすく、複数回参照する場合は再利用できます。

深く入れ子になったロジックでは、CTE のパイプラインは上から下へ読み進められます。一方、派生テーブルは内側から外側へ読み進めます。

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees GROUP BY dept_id
)
SELECT * FROM dept_avg WHERE avg_salary > 60000;

LATERAL / 相関 FROM サブクエリ

通常、FROM 句のサブクエリから外側のクエリの行を参照することはできません。LATERAL（Postgres）や CROSS APPLY（SQL Server）を使うとこの制限が解除され、派生テーブルを外側の各行に対して実行できるようになります。

これにより、行ごとの top-N 取得が可能になります。このキーワードの存在を知っていることは、中級レベルの面接でもシニア相当の理解を示します。

SELECT d.dept_name, top_emp.name, top_emp.salary
FROM departments d
CROSS JOIN LATERAL (
  SELECT name, salary FROM employees e
  WHERE e.dept_id = d.id
  ORDER BY salary DESC LIMIT 1
) AS top_emp;

面接での要点

FROM句のサブクエリについて聞かれたら、次のように答えてください: 「派生テーブルとは、FROM句に置かれ、外側のクエリがテーブルのように利用する結果セットを返すサブクエリです。エイリアスが必須で、外側のクエリから見えるのは派生テーブルが選択した列だけです。また、JOINの前の事前集約や、集約結果のさらなる集約に適しています。」

LATERALを使うと外側の行を参照できることも付け加えれば、あらゆる観点を押さえた回答になります。

簡単な確認

FROM句のサブクエリに常に必要となる記述を選んでください。

まとめ

派生テーブル、ここで押さえましょう:

  • FROM句のサブクエリは仮想テーブルを返します — 多数の行と多数の列を持ちます。
  • 派生テーブルには必ずエイリアスが必要で、外側のクエリから見えるのは選択された列だけです。
  • JOINの前の事前集約、集約結果に対するフィルタリング、集約結果のさらなる集約に使用します。
  • CTEは読みやすい名前付きの代替手段です。LATERAL/CROSS APPLYを使うと外側の行を参照できます。

次は、IN、ANY、ALLを使った集合所属判定のサブクエリです。

よくある質問

「FROM句のサブクエリ（派生テーブル）」レッスンは無料ですか？

はい。「FROM句のサブクエリ（派生テーブル）」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Coding Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Coding Interview Prepコースには全4レッスンが含まれています。

「FROM句のサブクエリ（派生テーブル）」で何を学びますか？

クエリを仮想テーブルとして包む方法と、エイリアスが必須である理由を学びます。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

Coding Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのCoding Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。

「FROM句のサブクエリ（派生テーブル）」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このCoding Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのCoding Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. SELECTとWHEREのスカラーサブクエリ
  2. FROM句のサブクエリ（派生テーブル）
  3. IN、ANY、ALLのサブクエリ
  4. EXISTSとINのパフォーマンス
← Coding Interview Prepに戻る