SQL Academy · レッスン

相関サブクエリ

Q: 「相関サブクエリ」で何を学びますか？

外側の行に依存するサブクエリを学びます。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

外側の行に依存するサブクエリを学びます。

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

相関サブクエリとは

相関サブクエリ とは、外側（包含）クエリの列を参照するサブクエリです。1 回実行されて固定された結果を返す通常のサブクエリとは異なり、相関サブクエリは外側のクエリが処理する各行に対して 1 回ずつ評価されます。

そのため、行ごとの比較には強力ですが、単純なサブクエリよりも処理コストが高くなる場合があります。

通常のサブクエリと相関サブクエリ

主な違いは、通常のサブクエリには外側のクエリへの参照がなく、単独で実行できることです。相関サブクエリは外側の行に依存しており、サブクエリ内に外側のテーブルエイリアスが現れることで確認できます。

次の例では、内部の SELECT が外側のクエリにある e1.department_id を参照しており、相関が生じています。

-- Regular subquery (runs once)
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated subquery (runs once per outer row)
SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

サンプルテーブルの準備

このレッスンで使用する 2 つのテーブル、employees と departments を作成しましょう。これらのテーブルを使って、さまざまな状況で相関サブクエリを示す現実的なデータを用意します。

CREATE TABLE departments (
  id   INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employees (
  id            INT PRIMARY KEY,
  name          VARCHAR(50),
  department_id INT REFERENCES departments(id),
  salary        NUMERIC(10,2),
  hire_date     DATE
);

INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'HR');

INSERT INTO employees VALUES
  (1,'Alice',  1, 90000, '2020-03-01'),
  (2,'Bob',    1, 75000, '2021-06-15'),
  (3,'Carol',  2, 60000, '2019-01-10'),
  (4,'David',  2, 68000, '2022-09-01'),
  (5,'Eve',    3, 55000, '2020-07-20'),
  (6,'Frank',  1, 95000, '2018-11-05'),
  (7,'Grace',  3, 52000, '2023-02-28'),
  (8,'Henry',  2, 71000, '2021-04-12');

部門平均を上回る給与の従業員

相関サブクエリの典型的なユースケースは、自分の所属部門の平均給与を上回る給与の従業員をすべて見つけることです。内部クエリは、外側のクエリにある各従業員の行に対して部門平均を再計算します。

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
)
ORDER BY e1.department_id, e1.salary DESC;

SELECT での相関サブクエリの使用

相関サブクエリは WHERE 句に限らず、SELECT リストに記述して各行の値を計算することもできます。ここでは、1 つのクエリで各従業員の給与と、その従業員の部門の平均給与を取得します。

SELECT
  e.name,
  e.salary,
  (
    SELECT ROUND(AVG(e2.salary), 2)
    FROM employees e2
    WHERE e2.department_id = e.department_id
  ) AS dept_avg_salary
FROM employees e
ORDER BY e.department_id, e.name;

各部門で最も給与の高い従業員の検索

相関サブクエリを使用して、各部門の最高給与の従業員を見つけることができます。内部クエリは現在の行の部門における最高給与を検索し、外側のクエリはその給与と一致する行だけを残します。

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
)
ORDER BY e1.department_id;

相関サブクエリでの EXISTS

EXISTS 演算子は、相関サブクエリと組み合わせて頻繁に使用されます。内部クエリが 1 行以上を返す場合に TRUE を返します。ここでは、2021 年より前に少なくとも 1 人の従業員を採用したすべての部門を一覧表示します。

SELECT d.name AS department
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
    AND e.hire_date < '2021-01-01'
);

相関サブクエリでの NOT EXISTS

NOT EXISTS はその逆で、相関サブクエリが一致する行を 1 つも見つけなかった場合に TRUE を返します。従業員がいない部門のように、子レコードを持たない親レコードを検索する場合に便利です。

SELECT d.name AS department_without_employees
FROM departments d
WHERE NOT EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
);

UPDATE での相関サブクエリ

相関サブクエリは UPDATE 文の内部でも使用できます。次の例では、まず dept_avg 列を追加し、その後、相関サブクエリを使用して各従業員の所属部門の平均給与をその列に入力します。

ALTER TABLE employees ADD COLUMN dept_avg NUMERIC(10,2);

UPDATE employees e1
SET dept_avg = (
  SELECT ROUND(AVG(e2.salary), 2)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

SELECT name, salary, dept_avg FROM employees ORDER BY department_id, name;

DELETE での相関サブクエリ

相関サブクエリは DELETE 文でも使用でき、関連テーブルのデータに基づいて行を削除できます。次のクエリは、所属部門の平均給与の 60% 未満の給与しかない従業員を削除します。これはデータクリーンアップで使われるパターンです。

DELETE FROM employees e1
WHERE e1.salary < (
  SELECT AVG(e2.salary) * 0.60
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

SELECT name, salary, department_id FROM employees ORDER BY department_id;

パフォーマンスのヒント: 相関サブクエリと JOIN

相関サブクエリは外側の行ごとに 1 回実行されるため、大きなテーブルでは遅くなる可能性があります。多くの相関サブクエリは、パフォーマンスを向上させるために、派生テーブルまたは CTE との JOIN に書き換えられます。両方のパターンを理解し、読みやすさと実行計画に基づいて選択してください。

-- Correlated version (potentially slower)
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

-- Equivalent JOIN + derived table (often faster)
SELECT e.name, e.salary
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
) dept_avg ON dept_avg.department_id = e.department_id
WHERE e.salary > dept_avg.avg_sal;

理解度チェック

相関サブクエリの理解度を確認しましょう。

まとめ: 相関サブクエリ

このレッスンでは、相関サブクエリが外側のクエリの列を参照し、外側の各行に対して再評価されることを学びました。主なポイント：

SELECT、WHERE、UPDATE、DELETE で使用できます。
EXISTS / NOT EXISTS は相関サブクエリと自然に組み合わせて、関連する行の有無を確認できます。
表現力に優れていますが、処理が遅くなる可能性があります。パフォーマンスが重要な場合は JOIN への書き換えを検討してください。
サブクエリ内にある外側のテーブルエイリアスが、相関を生み出します。

よくある質問

「相関サブクエリ」レッスンは無料ですか？

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

「相関サブクエリ」で何を学びますか？

外側の行に依存するサブクエリを学びます。ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

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

「相関サブクエリ」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

← SQL Academyに戻る