SQL子查询

广告位

在本教程中,您将了解SQL子查询以及如何使用子查询来形成灵活的SQL语句。 1. SQL子查询基本 请考虑中的…

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

1. SQL子查询基本

请考虑中的以下员工(employees)和部门(departments)表:

SQL子查询

假设要查找位置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;  

执行上面示例代码,得到以下结果:
SQL子查询

该解决方案有两个问题。 首先,查询departments表以检查哪个部门属于位置ID为1700

由于数据量较小,可以轻松获得部门列表。 但是,在具有大量数据的实际系统中,可能存在问题。

另一个问题是,只要想找到其它位置的员工,就必须修改查询。
更好的解决方案是使用子查询。 根据定义,子查询是嵌套在另一个查询中的查询,例如:,,或语句。 在本教程中,我们将重点介绍与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 – 然后执行外部查询。

可以在许多地方使用子查询,例如:

  • 使用INNOT IN运算符
  • 使用EXISTSNOT EXISTS运算符
  • 使用ANYALL运算符
  • 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);  

执行上面查询语句,得到以下结果:
SQL子查询

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

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;  

执行上面查询语句,得到以下结果:

SQL子查询

同样,以下语句查找所有没有薪水大于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;  

执行上面查询语句,得到以下结果:

SQL子查询

2.4. 带有ALL运算符的SQL子查询
子查询与ALL运算符一起使用时的语法如下:

comparison_operator ALL (subquery)  

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

x > ALL (subquery)  

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

x > ALL (1,2,3)  

以下查询使用子句和函数按部门查找最低工资:

SELECT       MIN(salary)  FROM      employees  GROUP BY department_id  ORDER BY MIN(salary) DESC;  

执行上面查询语句,得到以下结果:
SQL子查询

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

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;  

执行上面查询语句,得到以下结果:
SQL子查询

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;  

执行上面查询语句,得到以下结果:
SQL子查询

可以将此查询用作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语句。

  

洁姐我爱你

关于作者: 洁姐我爱你

为您推荐