SQL子查询

作者：--    发布时间：2019-11-20

在本教程中，您将了解sql子查询以及如何使用子查询来形成灵活的sql语句。

1. sql子查询基本

请考虑示例数据库中的以下员工(employees)和部门(departments)表：

假设要查找位置id为1700的所有员工，可能会想出以下解决方案。

首先，找到位置id为1700的所有部门：

select 
    *
from
    departments
where
    location_id = 1700;

执行上面查询语句，得到以下结果：

+---------------+-----------------+-------------+
| department_id | department_name | location_id |
+---------------+-----------------+-------------+
|             1 | 管理            |        1700 |
|             3 | 采购            |        1700 |
|             9 | 行政人员        |        1700 |
|            10 | 财务            |        1700 |
|            11 | 会计            |        1700 |
+---------------+-----------------+-------------+
5 rows in set

其次，使用上一个查询的部门id列表，查找属于位置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;

执行上面示例代码，得到以下结果：

该解决方案有两个问题。 首先，查询departments表以检查哪个部门属于位置id为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;

放在括号内的查询称为子查询，它也称为内部查询或内部选择。 包含子查询的查询称为外部查询或外部选择。

要执行查询，首先，数据库系统必须执行子查询并将括号之间的子查询替换为其结果 - 位于位置id为1700的多个部门id - 然后执行外部查询。

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

• 使用in或not in运算符
• 比较运算符中
• 使用exists或not exists运算符
• 使用any或all运算符
• 在from子句中
• 在select子句中

2. sql子查询示例

下面我们举一些使用子查询来了解它们如何工作的例子。

2.1. 带有in或not in运算符的sql子查询

在前面的示例中，已经了解了子查询如何与in运算符一起使用。 以下示例使用带有not in运算符的子查询来查找未找到位置id为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;

执行上面查询语句，得到以下结果：

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         103 | alexander  | lee       |
|         193 | britney    | zhao      |
|         104 | bruce      | wong      |
|         179 | charles    | yang      |
|         105 | david      | liang     |
|         107 | diana      | chen      |
|         204 | hermann    | wu        |
|         126 | irene      | liu       |
|         177 | jack       | yang      |
|         145 | john       | liu       |
|         176 | jonathon   | yang      |
|         146 | karen      | liu       |
|         178 | kimberely  | yang      |
|         120 | matthew    | han       |
|         121 | max        | han       |
|         201 | michael    | zhou      |
|         122 | min        | liu       |
|         202 | pat        | zhou      |
|         192 | sarah      | yang      |
|         123 | shanta     | liu       |
|         203 | susan      | zhou      |
|         106 | valli      | chen      |
+-------------+------------+-----------+
22 rows in set

2.2. 带有比较运算符的sql子查询
以下语法说明了子查询如何与比较运算符一起使用：

comparison_operator (subquery)

比较运算符是这些运算符之一：

• 等于(=)
• 大于(>)
• 小于(<)
• 大于或等于(>=)
• 小于等于(<=)
• 不相等(!=)或(<>)

以下示例查找薪水最高的员工：

select 
    employee_id, first_name, last_name, salary
from
    employees
where
    salary = (select 
            max(salary)
        from
            employees)
order by first_name , last_name;

执行上面示例代码，得到以下结果：

order by first_name , last_name;
+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
|         100 | steven     | lee       | 24000  |
+-------------+------------+-----------+--------+
1 row in set

在此示例中，子查询返回所有员工的最高薪水，外部查询查找薪水等于最高员工的员工。

以下语句查询所有薪水都高于的平均薪水的员工：

select 
    employee_id, first_name, last_name, salary
from
    employees
where
    salary > (select 
            avg(salary)
        from
            employees);

执行上面查询语句，得到以下结果：

在此示例中，首先，子查询返回所有员工的平均工资。 然后，外部查询使用大于运算符来查找工资大于平均值的所有员工。

2.3. 带有exists或not exists运算符的sql子查询

exists运算符检查子查询返回的行是否存在。 如果子查询包含任何行，则返回true。 否则，它返回false。

exists运算符的语法如下：

existse  (subquery )

not exists运算符与exists运算符相反。

not exists (subquery)

以下示例查找至少有一名员工的薪水大于10000的所有部门：

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;

执行上面查询语句，得到以下结果：

同样，以下语句查找所有没有薪水大于10000的员工的部门：

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;

执行上面查询语句，得到以下结果：

2.4. 带有all运算符的sql子查询
子查询与all运算符一起使用时的语法如下：

comparison_operator all (subquery)

如果x大于子查询返回的每个值，则以下条件的计算结果为true。

x > all (subquery)

例如，假设子查询返回三个值：1,2和3。 如果x大于3，则以下条件的计算结果为true。

x > all (1,2,3)

以下查询使用group by子句和min()函数按部门查找最低工资：

select 
    min(salary)
from
    employees
group by department_id
order by min(salary) desc;

执行上面查询语句，得到以下结果：

以下示例查找薪水大于每个部门最低薪水的所有员工：

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;

执行上面查询语句，得到以下结果：

2.5. 带有any运算符的sql子查询
以下是带有any运算符的子查询的语法：

comparison_operator any (subquery)

例如，如果x大于子查询返回的任何值，则以下条件的计算结果为true。 因此，如果x大于1，则条件x> some(1,2,3)的计算结果为true。

x > any (subquery)

请注意，some运算符是any运算符的同义词，因此可以互换使用它们。

以下查询查找薪水大于或等于每个部门的最高薪水的所有员工。

select 
    employee_id, first_name, last_name, salary
from
    employees
where
    salary >= some (select 
            max(salary)
        from
            employees
        group by department_id);

执行上面查询语句，得到以下结果：

+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
|         100 | steven     | lee       | 24000  |
|         101 | neena      | wong      | 17000  |
|         102 | lex        | liang     | 17000  |
|         103 | alexander  | lee       | 9000   |
|         104 | bruce      | wong      | 6000   |
|         105 | david      | liang     | 4800   |
|         106 | valli      | chen      | 4800   |
|         108 | nancy      | chen      | 12000  |
... ... 
|         200 | jennifer   | zhao      | 4400   |
|         201 | michael    | zhou      | 13000  |
|         202 | pat        | zhou      | 6000   |
|         203 | susan      | zhou      | 6500   |
|         204 | hermann    | wu        | 10000  |
|         205 | shelley    | wu        | 12000  |
|         206 | william    | wu        | 8300   |
+-------------+------------+-----------+--------+
31 rows in set

在此示例中，子查询查找每个部门中员工的最高薪水。 外部查询查看这些值并确定哪个员工的工资大于或等于按部门划分的任何最高工资。

2.7. from子句中的sql子查询

可以在select语句的from子句中使用子查询，如下所示：

select 
    *
from
    (subquery) as table_name

在此语法中，表别名是必需的，因为from子句中的所有表都必须具有名称。
请注意，from子句中指定的子查询在mysql中称为派生表，在oracle中称为内联视图。

以下语句返回每个部门的平均工资：

select 
    avg(salary) average_salary
from
    employees
group by department_id;

执行上面查询语句，得到以下结果：

可以将此查询用作from子句中的子查询，以计算部门平均工资的平均值，如下所示：

select 
    round(avg(average_salary), 0)
from
    (select 
        avg(salary) average_salary
    from
        employees
    group by department_id) department_salary;
+-------------------------------+
| round(avg(average_salary), 0) |
+-------------------------------+
| 8536                          |
+-------------------------------+
1 row in set

2.8. 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;

执行上面查询语句，得到以下结果 -

+-------------+------------+-----------+--------+----------------+------------+
| employee_id | first_name | last_name | salary | average_salary | difference |
+-------------+------------+-----------+--------+----------------+------------+
|         103 | alexander  | lee       | 9000   | 8060           | 940        |
|         115 | alexander  | su        | 3100   | 8060           | -4960      |
|         114 | avg        | su        | 11000  | 8060           | 2940       |
|         193 | britney    | zhao      | 3900   | 8060           | -4160      |
|         104 | bruce      | wong      | 6000   | 8060           | -2060      |
|         179 | charles    | yang      | 6200   | 8060           | -1860      |
|         109 | daniel     | chen      | 9000   | 8060           | 940        |
|         105 | david      | liang     | 4800   | 8060           | -3260      |
... ... 
|         192 | sarah      | yang      | 4000   | 8060           | -4060      |
|         123 | shanta     | liu       | 6500   | 8060           | -1560      |
|         205 | shelley    | wu        | 12000  | 8060           | 3940       |
|         116 | shelli     | zhang     | 2900   | 8060           | -5160      |
|         117 | sigal      | zhang     | 2800   | 8060           | -5260      |
|         100 | steven     | lee       | 24000  | 8060           | 15940      |
|         203 | susan      | zhou      | 6500   | 8060           | -1560      |
|         106 | valli      | chen      | 4800   | 8060           | -3260      |
|         206 | william    | wu        | 8300   | 8060           | 240        |
+-------------+------------+-----------+--------+----------------+------------+
40 rows in set

通过上面的学习，现在您应该了解sql子查询是什么，以及如何使用子查询来形成灵活的sql语句。