主页 » 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

此解决方案有两个问题。首先，您已经查看了 departments 表来检查哪个部门属于位置 1700。然而，最初的问题并非针对任何特定部门；它是针对位置 1700 的。

由于数据量小，您可以轻松获取部门列表。然而，在具有高数据量量实的系统中，这可能是个问题。

另一个问题是，每当您想查找驻扎在不同位置的员工时，都必须修改查询。

对此问题的更好的解决方案是使用子查询。根据定义，子查询是嵌套在另一个SELECT、INSERT、UPDATE或DELETE语句中的查询。在本教程中，我们重点关注与SELECT语句一起使用的子查询。

在此示例中，您可以按照如下方式重写合并以上两个查询

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)

置于括号内的查询称为子查询。它也称为内部查询或内部选择。包含子查询的查询称为外部查询或外部选择。

要执行查询，首先，数据库系统必须执行子查询，并将括号内的子查询替换为子查询的结果 - 驻扎在位置 1700 的部门 ID 数量 - 然后执行外部查询。

您可以在许多地方使用子查询，例如

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 运算符检查子查询返回的行是否存在。如果子查询包含任何行，它返回真。否则，它返回假。

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 的所有部门

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 大于子查询返回的每个值，那么下列条件评估为真。

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

例如，假设子查询返回三个值，一、二、三。如果 x 大于 3，那么下列条件评估为真。

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 大于子查询返回的任何值，则下列条件评估为真。因此条件 x > SOME (1,2,3) 如果 x 大于 1，则评估为真。

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