📝

サブクエリの利用場面と具体例

2023/05/21に公開

SQL

tech

はじめに

今回はSQLの強力な機能である「サブクエリ」について説明します。具体的な利用場面、例を交えながら学んでいきます。
前半に、事前に必要な知識。後半に、サブクエリについて具体的に説明していきます。

事前知識

サブクエリを使用するためには、SQLの基本的な概念と操作について理解している必要があります。
具体的には以下の知識が必要となります。詳細については割愛します。

SQLの基本的な概念と操作

1. SQLの基本

SQLの基本的なコマンド（SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BYなど）を理解し、それらを使ってデータを抽出、フィルタリング、ソート、グループ化する方法を理解することが重要です。

2. テーブルとカラムの理解

データベースのテーブル構造、テーブル間の関連性、そして各テーブルが持つカラムのデータ型と意味を理解することが重要です。これにより、どのテーブルやカラムからデータを抽出すべきか、どのようにテーブルを結合すべきかを理解することができます。

3. 関数と集計

SQLで提供される関数（例えば、SUM(), COUNT(), AVG(), MAX(), MIN()など）の使用方法と、それらを使ってデータを集計する方法を理解することも必要です。

4. 結合（JOIN）

データベース内の複数のテーブルからデータを結合して取得する方法を理解することは、サブクエリを使った複雑なクエリを書く上で必要不可欠です。INNER JOIN, LEFT JOINなどの基本的な結合方法を理解することが求められます。

5. 論理演算子

SQLの論理演算子（AND, OR, NOT）を使って、より複雑なフィルタリング条件を作成する方法を理解することも重要です。

6. SQLの演算子

SQLにおける比較演算子（=, <>, <, >, <=, >=など）、算術演算子（+, -, *, /など）、そしてIN, BETWEEN, LIKE, IS NULLなどの特殊な演算子の使用方法を理解することが必要です。

7. データベースの設計と正規化

これは必須ではありませんが、データベースの設計原則と正規化について基本的な理解を持つと、データベースの構造をより深く理解し、より効率的なサブクエリを書くことができます。

参考データベース

今回はMySQL公式サンプルデータベースのemployee dataのデータをデータベースに入れて利用します。

テーブルは以下の通りです。

employees
current_dept_emp
departments
dept_emp
dept_emp_latest_date
dept_manager
employees
salaries
titles

サブクエリ

事前知識を踏まえて、具体的にサブクエリについての説明をしていきます。

サブクエリとは？

サブクエリとは、SQLクエリ内でさらに別のクエリを書くことを指します。()の中でSELECTを利用します。これは、一部の情報を取得するためのクエリが他の情報を取得するためのクエリの中にある、という形をとります。簡単に言うと、「クエリの中のクエリ」です。サブクエリは、問題をより詳細に解析したり、複雑な問題を解決するための強力なツールとなります。

サブクエリを利用するメリット

複雑な問題を分割する

サブクエリを使うと、大きな問題を小さなパーツに分割して考えることができます。個々の小さな問題（サブクエリ）を解決していくことで、最終的に大きな問題を解決します。

データの再利用

サブクエリの結果は、その場で一時的に作成されるテーブルのようなものと考えることができます。これを使って、他のクエリで必要なデータを簡単に取得することができます。

順序の制御

サブクエリは、特定の処理が他の処理よりも先に実行されるべき場合に役立ちます。SQLは基本的に順序が無視されるため、サブクエリを使って順序を制御することができます。

サブクエリが使える場面

以下の６つの場面でのサブクエリの使用例を提供します。

1. 複雑なフィルタリング

以下のクエリでは、全体の平均給与よりも給与が高い従業員の情報を取得しています。この場合、平均給与を計算するためのサブクエリを使用します。

SELECT emp_no, salary 
  FROM salaries
 WHERE salary > (
   SELECT AVG(salary) 
     FROM salaries
    );

2. 順序付けの制御

以下のクエリでは、各部署の最新の給与記録のみを取得します。この場合、サブクエリを使用して、各部署の最新の給与日付を先に取得し、その日付に対応する給与記録を取得します。

SELECT e.emp_no, e.first_name, e.last_name, s.salary, s.from_date
  FROM employees e
  JOIN salaries s ON e.emp_no = s.emp_no
 WHERE s.from_date = (
   SELECT MAX(from_date) 
     FROM salaries 
    WHERE emp_no = e.emp_no
   );

3. 集計結果の使用

以下のクエリでは、各部署の平均給与が全社の平均給与より高い部署を探します。この場合、サブクエリを使用して、全社の平均給与を計算します。

SELECT d.dept_no, AVG(s.salary) AS avg_salary
  FROM dept_emp de 
  JOIN departments d ON de.dept_no = d.dept_no
  JOIN salaries s ON de.emp_no = s.emp_no
 GROUP BY d.dept_no
 HAVING avg_salary > (
    SELECT AVG(salary) 
      FROM salaries);

4. 列の作成

以下のクエリでは、各従業員について、その人が現在働いている部署の数を計算します。この場合、サブクエリを使用して、各従業員の部署数を計算し、それを新しい列として表示します。

SELECT e.emp_no, e.first_name, e.last_name,
  (SELECT COUNT(*) 
     FROM dept_emp de 
    WHERE de.emp_no = e.emp_no) AS num_departments
FROM employees e;

5. 相関サブクエリ

相関サブクエリとは、メインクエリから取得したデータを用いてサブクエリが実行されるクエリのことを指します。つまり、メインクエリの各行ごとにサブクエリが実行されます。
　　以下のクエリでは、各従業員について、その人が受け取った最高の給与を計算します。この場合、相関サブクエリを使用して、各従業員の最高給与を計算します。

SELECT e.emp_no, e.first_name, e.last_name, 
  (SELECT MAX(s.salary) 
     FROM salaries s 
    WHERE s.emp_no = e.emp_no) AS max_salary
  FROM employees e;

6. INやEXISTS句との組み合わせ

以下のクエリでは、マネージャーであったことのある従業員の情報を取得します。この場合、サブクエリを使用して、マネージャーであったことのある従業員の一覧を取得し、その一覧に含まれる従業員の情報を取得します。

SELECT e.emp_no, e.first_name, e.last_name
  FROM employees e
 WHERE e.emp_no IN (
    SELECT dm.emp_no 
      FROM dept_manager dm);

それぞれの例は単純化されたものであり、実際の問題ではより複雑なサブクエリが必要となることがあります。しかし、これらの基本的なパターンを理解していれば、より複雑な問題にも対応できるようになります。

サブクエリを使用する上での注意点

パフォーマンスの影響

サブクエリは実行時間が増加する可能性があります。特に、大きなデータベースではサブクエリの実行に時間がかかることがあります。このため、サブクエリの使用は最小限に抑え、必要な場合のみ使用することが推奨されます。

複雑さの増加

サブクエリを多用すると、クエリ全体が複雑になり、理解やメンテナンスが難しくなる可能性があります。使わなくて済むなら使わないほうがよいです。

実行計画の理解

サブクエリはクエリの実行計画を複雑にする可能性があります。このため、実行計画を理解し、パフォーマンスを最適化するためには、サブクエリの動作をしっかりと理解することが重要です。

まとめ

以上、サブクエリについての簡単な紹介でした。この技術を使うことで、より複雑な問題を解決することができます。ただし、複雑なクエリはパフォーマンスに影響を及ぼす可能性がありますので、適切に使用することが重要です。