ホーム » SQLチュートリアル » SQL サブクエリ

SQL サブクエリ

概要：このチュートリアルでは、SQL サブクエリと、サブクエリを使用して柔軟なSQLステートメントを作成する方法について学習します。

SQL サブクエリの基本

サンプルデータベースにある次のemployeesテーブルとdepartmentsテーブルについて考えてみましょう。

SQL Subquery: Sample tables

IDが1700の場所にあるすべての従業員を見つける必要があるとします。次の解決策を思いつくかもしれません。

まず、IDが1700の場所にあるすべての部署を見つけます。

SELECT 
    *
FROM
    departments
WHERE
    location_id = 1700;
Code language: SQL (Structured Query Language) (sql)
SQL Subquery - department list

次に、前のクエリの部署IDリストを使用して、場所1700に属するすべての従業員を見つけます。

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    department_id IN (1 , 3, 8, 10, 11)
ORDER BY first_name , last_name;Code language: SQL (Structured Query Language) (sql)
SQL Subquery - IN operator

この解決策には2つの問題があります。まず、場所1700に属する部署を確認するためにdepartmentsテーブルを調べました。しかし、元の質問は特定の部署を参照したものではなく、場所1700を参照していました。

データ量が少ないため、部署のリストを簡単に取得できます。ただし、データ量が多い実際のシステムでは、問題が発生する可能性があります。

もう1つの問題は、別の場所にある従業員を見つけたいときはいつでも、クエリを修正する必要があることです。

この問題に対するより良い解決策は、サブクエリを使用することです。定義上、サブクエリとは、SELECT、INSERT、UPDATE、またはDELETEステートメントなど、別のクエリ内にネストされたクエリです。このチュートリアルでは、SELECTステートメントで使用されるサブクエリに焦点を当てています。

この例では、上記の2つのクエリを次のように組み合わせて書き直すことができます。

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    department_id IN (SELECT 
            department_id
        FROM
            departments
        WHERE
            location_id = 1700)
ORDER BY first_name , last_name;Code language: SQL (Structured Query Language) (sql)

括弧内に配置されたクエリは、サブクエリと呼ばれます。内部クエリまたは内部SELECTとも呼ばれます。サブクエリを含むクエリは、外部クエリまたは外部SELECTと呼ばれます。

クエリを実行するには、最初にデータベースシステムがサブクエリを実行し、括弧内のサブクエリをその結果（場所1700にある部署IDの数）に置き換え、次に外部クエリを実行する必要があります。

サブクエリは、次のような多くの場所で使用できます。

  • INまたはNOT IN演算子を使用する場合
  • 比較演算子を使用する場合
  • EXISTSまたはNOT EXISTS演算子を使用する場合
  • ANYまたはALL演算子を使用する場合
  • FROM句で使用する場合
  • SELECT句で使用する場合

SQL サブクエリの例

サブクエリの使用例をいくつか見て、その仕組みを理解しましょう。

INまたはNOT IN演算子を使用したSQLサブクエリ

前の例では、サブクエリがIN演算子でどのように使用されたかを見てきました。次の例では、NOT IN演算子を使用したサブクエリを使用して、場所1700にいないすべての従業員を見つけます。

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    department_id NOT IN (SELECT 
            department_id
        FROM
            departments
        WHERE
            location_id = 1700)
ORDER BY first_name , last_name;Code language: SQL (Structured Query Language) (sql)
SQL Subquery - NOT IN operator

比較演算子を使用したSQLサブクエリ

次の構文は、サブクエリが比較演算子でどのように使用されるかを示しています。

comparison_operator (subquery)
Code language: SQL (Structured Query Language) (sql)

ここで、比較演算子は次の演算子のいずれかです。

  • 等しい (=)
  • より大きい (>)
  • より小さい (<)
  • 以上 (>=)
  • 以下 (<=)
  • 等しくない (!=) または (<>)

次の例では、最も高い給与を持つ従業員を見つけます。

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary = (SELECT 
            MAX(salary)
        FROM
            employees)
ORDER BY first_name , last_name;Code language: SQL (Structured Query Language) (sql)
SQL Subquery with the Equal operator

この例では、サブクエリはすべての従業員の最高給与を返し、外部クエリは給与が最高給与と等しい従業員を見つけます。

次のステートメントは、給与がすべての従業員の平均給与よりも高いすべての従業員を見つけます。

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary > (SELECT 
            AVG(salary)
        FROM
            employees);    
Code language: SQL (Structured Query Language) (sql)
SQL Subquery with greater than operator

この例では、最初にサブクエリがすべての従業員の平均給与を返します。次に、外部クエリはより大きい演算子を使用して、給与が平均よりも大きいすべての従業員を見つけます。

EXISTSまたはNOT EXISTS演算子を使用したSQLサブクエリ

EXISTS演算子は、サブクエリから返された行の存在を確認します。サブクエリに行が含まれている場合はtrueを返します。それ以外の場合はfalseを返します。

EXISTS演算子の構文は次のとおりです。

EXISTS (subquery )
Code language: SQL (Structured Query Language) (sql)

NOT EXISTS演算子はEXISTS演算子の反対です。

NOT EXISTS (subquery)
Code language: SQL (Structured Query Language) (sql)

次の例では、給与が10,000を超える従業員が少なくとも1人いるすべての部署を見つけます。

SELECT 
    department_name
FROM
    departments d
WHERE
    EXISTS( SELECT 
            1
        FROM
            employees e
        WHERE
            salary > 10000
                AND e.department_id = d.department_id)
ORDER BY department_name; 
Code language: SQL (Structured Query Language) (sql)
SQL Subquery with the EXISTS operator

同様に、次のステートメントは、給与が10,000を超える従業員がいないすべての部署を見つけます。

SELECT 
    department_name
FROM
    departments d
WHERE
    NOT EXISTS( SELECT 
            1
        FROM
            employees e
        WHERE
            salary > 10000
                AND e.department_id = d.department_id)
ORDER BY department_name;  
Code language: SQL (Structured Query Language) (sql)

SQL Subquery with NOT EXISTS operator

ALL演算子を使用したSQLサブクエリ

ALL演算子と共に使用される場合のサブクエリの構文は次のとおりです。

comparison_operator ALL (subquery)
Code language: SQL (Structured Query Language) (sql)

xがサブクエリによって返されたすべての値よりも大きい場合、次の条件はtrueと評価されます。

x > ALL (subquery)
Code language: SQL (Structured Query Language) (sql)

たとえば、サブクエリが1、2、3の3つの値を返すとします。xが3より大きい場合、次の条件はtrueと評価されます。

x > ALL (1,2,3)
Code language: SQL (Structured Query Language) (sql)

次のクエリでは、GROUP BY句とMIN()関数を使用して、部署ごとの最低給与を見つけます。

SELECT 
    MIN(salary)
FROM
    employees
GROUP BY department_id
ORDER BY MIN(salary) DESC;
Code language: SQL (Structured Query Language) (sql)
SQL Subquery - min salary by department

次の例では、給与がすべての部署の最低給与よりも高いすべての従業員を見つけます。

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary >= ALL (SELECT 
            MIN(salary)
        FROM
            employees
        GROUP BY department_id)
ORDER BY first_name , last_name;Code language: SQL (Structured Query Language) (sql)
SQL Subquery with ALL operator example

ANY演算子を使用したSQLサブクエリ

次に、ANY演算子を使用したサブクエリの構文を示します。

comparison_operator ANY (subquery)
Code language: SQL (Structured Query Language) (sql)

たとえば、xがサブクエリによって返された任意の値よりも大きい場合、次の条件はtrueと評価されます。したがって、条件x > SOME (1,2,3)は、xが1より大きい場合にtrueと評価されます。

x > ANY (subquery)Code language: SQL (Structured Query Language) (sql)

SOME演算子はANY演算子の同義語であるため、同じように使用できることに注意してください。

次のクエリは、給与がすべての部署の最高給与以上であるすべての従業員を見つけます。

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary >= SOME (SELECT 
            MAX(salary)
        FROM
            employees
        GROUP BY department_id);
Code language: SQL (Structured Query Language) (sql)
SQL Subquery with SOME operator example

この例では、サブクエリは各部署の従業員の最高給与を見つけます。外部クエリはこれらの値を見て、どの従業員の給与が部署ごとの最高給与以上であるかを判断します。

FROM句のSQLサブクエリ

SELECTステートメントのFROM句でサブクエリを使用できます。次に例を示します。

SELECT 
    *
FROM
    (subquery) AS table_name
Code language: SQL (Structured Query Language) (sql)

この構文では、テーブルエイリアスは、FROM句のすべてのテーブルに名前が必要なため、必須です。

FROM句で指定されたサブクエリは、MySQLでは派生テーブル、Oracleではインラインビューと呼ばれます。

次のステートメントは、各部署の平均給与を返します。

SELECT 
    AVG(salary) average_salary
FROM
    employees
GROUP BY department_id;
Code language: SQL (Structured Query Language) (sql)
SQL Subquery - average salary by department

このクエリをFROM句のサブクエリとして使用して、部署の平均給与の平均を次のように計算できます。

SELECT 
    ROUND(AVG(average_salary), 0)
FROM
    (SELECT 
        AVG(salary) average_salary
    FROM
        employees
    GROUP BY department_id) department_salary;
Code language: SQL (Structured Query Language) (sql)
SQL Subquery - average of average salary by department

SELECT句のSQLサブクエリ

サブクエリは、SELECT句で式を使用できる場所ならどこでも使用できます。次の例では、すべての従業員の給与、平均給与、各従業員の給与と平均給与の差を見つけます。

SELECT 
    employee_id,
    first_name,
    last_name,
    salary,
    (SELECT 
            ROUND(AVG(salary), 0)
        FROM
            employees) average_salary,
    salary - (SELECT 
            ROUND(AVG(salary), 0)
        FROM
            employees) difference
FROM
    employees
ORDER BY first_name , last_name;Code language: SQL (Structured Query Language) (sql)
SQL Subquery in SELECT clause

これで、SQLサブクエリとは何か、およびサブクエリを使用して柔軟なSQLステートメントを作成する方法を理解できたはずです。

このチュートリアルは役に立ちましたか？
SQL CUBE
前へ
SQL CUBE
次へ
SQL 相関サブクエリ
SQL Correlated Subquery