SQL Interview Prep · レッスン

SELECTとWHEREのスカラーサブクエリ

Q: 「SELECTとWHEREのスカラーサブクエリ」で何を学びますか？

単一値を返すサブクエリと、複数行を返した場合のエラーを学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

単一値を返すサブクエリと、複数行を返した場合のエラーを学びます。

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

面接官がスカラーサブクエリで意味すること

スカラーサブクエリとは、厳密に1 行 1 列、つまり単一の値を返すクエリです。1 つの値に解決されるため、SQL ではリテラルを記述できるほぼすべての場所、つまり SELECT、WHERE、HAVING、さらには ORDER BY の中でも使用できます。

面接官は、1 行 1 列というルールを理解しているかを確認します。
典型的な落とし穴は、サブクエリが誤って複数行を返すケースです。

この定義を簡潔に説明できれば、最初のチェックポイントはすでに通過しています。

SELECT リスト内のスカラーサブクエリ

SELECT リストにスカラーサブクエリを置くと、計算された単一の値をすべての出力行に付加できます。ここでは、各従業員の横に全社平均給与を表示しています。

サブクエリ (SELECT AVG(salary) FROM employees) が実行され、テーブル全体が 1 つの数値に集約されます。その数値が各行で繰り返し表示されます。

SELECT
  name,
  salary,
  (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

WHERE 内のスカラーサブクエリ

同じ単一の値をフィルタリングの基準にすることもできます。面接で非常によく出る質問は、全社平均より多く稼いでいる人を全員見つけてくださいというものです。

サブクエリで平均値を一度計算し、外側のクエリの各行と比較します。これは相関サブクエリではありません。内側のクエリは外側の行に依存しないため、1 回だけ実行されます。

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

「複数行」のエラー

これは、面接官が予測できるか確認したいエラーです。サブクエリを = や > とともに使用しているにもかかわらず、複数行を返すと、データベースエンジンは次のエラーを発生させます。

Postgres: more than one row returned by a subquery used as an expression
MySQL: Subquery returns more than 1 row

次のクエリが失敗するのは、部門 5 に複数の従業員がいる可能性があり、サブクエリがスカラーではないためです。

SELECT name
FROM employees
WHERE salary = (SELECT salary FROM employees WHERE dept_id = 5);

サブクエリをスカラーにする方法

単一の値を保証する確実な方法は 2 つあります。

MAX、MIN、AVG などの集約関数を使用します。GROUP BY のない集約関数は常に 1 行を返します。
ORDER BY の後に LIMIT 1（Postgres/MySQL）または FETCH FIRST 1 ROW ONLY を使用します。

下の修正版では、部門 5 の給与の最高額を 1 つ取得しています。

SELECT name
FROM employees
WHERE salary = (
  SELECT MAX(salary) FROM employees WHERE dept_id = 5
);

スカラーサブクエリは行がない場合に NULL を返します

面接で問われやすい細かなポイントがあります。スカラーサブクエリに一致する行が0 行の場合、エラーにはならず、NULL を返します。その NULL は比較にも伝播します。

salary > NULL は true ではなく UNKNOWN と評価されるため、外側のクエリは行を返しません。ここでエラーを予想する候補者が多いのですが、正解はエラーを出さずに空の結果を返すことです。

SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees WHERE dept_id = 9999
);

実例：平均を上回る給与と平均との差

2 つの配置方法を組み合わせてみましょう。平均を上回る給与を得ている各従業員と、その給与が平均をどれだけ上回っているかを表示します。同じスカラーサブクエリが SELECT と WHERE に現れます。

面接官から、サブクエリが 2 回実行されるのか尋ねられることがあります。論理的には 2 回現れますが、優れたオプティマイザなら、相関のないサブクエリを 1 回だけ評価して再利用できます。

SELECT
  name,
  salary,
  salary - (SELECT AVG(salary) FROM employees) AS above_avg
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY above_avg DESC;

ORDER BY 内のスカラーサブクエリ

スカラーサブクエリは単なる値なので、ORDER BY でも使用できます。これは最もすっきりした方法とは限りませんが、使用可能だと理解しているかを面接官が確認することがあります。

ここでは、結合を使わずに別のテーブルから取得した値、つまり各部門の従業員数で部門を並べ替えています。

SELECT d.dept_name
FROM departments d
ORDER BY (
  SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.id
) DESC;

スカラーと相関の違いを理解する

先ほどの ORDER BY の例では、実は外側のクエリにある d.id を参照しています。これにより、外側の各行に対して 1 回ずつ実行される相関スカラーサブクエリになります。

面接官はこの違いをよく確認します。

非相関スカラーサブクエリ：自己完結しており、1 回実行されます。
相関スカラーサブクエリ：外側の行を参照し、行ごとに実行されます。

どちらもスカラー、つまり 1 つの値を返しますが、パフォーマンスは大きく異なります。

スカラーサブクエリを使わない場合

SELECT リスト内の相関スカラーサブクエリは便利ですが、行ごとに実行されるため、大きなテーブルでは遅くなることがあります。面接官は、次の代替手段を理解していることを期待します。

事前に集約した派生テーブルへの LEFT JOIN。
AVG(salary) OVER () のようなウィンドウ関数。

下のウィンドウ関数を使った形式では、別のサブクエリスキャンを行わずに、同じ全社平均の列を生成できます。

SELECT
  name,
  salary,
  AVG(salary) OVER () AS company_avg
FROM employees;

面接での決め台詞

スカラーサブクエリの定義を尋ねられたら、次のように答えてください。「スカラーサブクエリは 1 行 1 列を返すため、単一の値として扱われ、リテラルが許可される場所ならどこでも使用できます。複数行を返すとデータベースエンジンでエラーになり、行を返さない場合は NULL になります。」

この一文で、定義、エラーになるケース、NULL になる境界ケースという、面接官が確認したい 3 つのポイントをカバーできます。

確認問題

スカラーサブクエリの動作について理解度を確認しましょう。

まとめ

これでスカラーサブクエリを理解できました。

定義：1 行 1 列を返し、SELECT、WHERE、HAVING、ORDER BY でリテラルのように使用できます。
複数行を返した状態で = や > と組み合わせるとエラーになります。集約関数または LIMIT 1 でスカラーにします。
0 行の場合は NULL になり、フィルタリングでは行が何も返されなくなります。
相関スカラーサブクエリは行ごとに実行されます。パフォーマンスが重要な場合は、結合またはウィンドウ関数を優先してください。

次は FROM 句のサブクエリです。結果全体が仮想テーブルになります。

よくある質問

「SELECTとWHEREのスカラーサブクエリ」レッスンは無料ですか？

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

「SELECTとWHEREのスカラーサブクエリ」で何を学びますか？

単一値を返すサブクエリと、複数行を返した場合のエラーを学びます。ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「SELECTとWHEREのスカラーサブクエリ」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

← SQL Interview Prepに戻る