临沂建设局网站官网,南京触屏网站开发,wordpress站点结构,网页怎么写SQL 支持查询语句的嵌套#xff0c;也就是在一个语句中包含其他的查询语句。嵌套子查询可以用于实现灵活复杂的查询语句。
本文比较五种主流数据库实现的子查询功能#xff0c;包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。
功能MySQLOracleSQL ServerPostgre…SQL 支持查询语句的嵌套也就是在一个语句中包含其他的查询语句。嵌套子查询可以用于实现灵活复杂的查询语句。
本文比较五种主流数据库实现的子查询功能包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。
功能MySQLOracleSQL ServerPostgreSQLSQLite标量子查询✔️✔️✔️✔️✔️行子查询✔️✔️❌✔️✔️表子查询✔️✔️✔️✔️✔️ALL✔️✔️✔️✔️❌ANY、SOME✔️✔️✔️✔️❌关联子查询✔️✔️✔️✔️✔️横向子查询✔️✔️✔️✔️❌EXISTS✔️✔️✔️✔️✔️
查询中的查询
假如我们想要知道哪些员工的月薪大于或等于所有员工的平均月薪应该如何编写查询语句显然我们首先需要得到所有员工的平均月薪这个可以使用 AVG 聚合函数实现
SELECT AVG(salary)
FROM employee;AVG(salary)
-----------
9832.00然后我们可以将该结果作为以下查询的过滤条件返回月薪大于或等于 9832 元的员工
SELECT emp_name AS 员工姓名, salary AS 月薪
FROM employee
WHERE salary 9832;查询返回的结果如下
员工姓名|月薪
-------|--------
刘备 |30000.00
关羽 |26000.00
张飞 |24000.00
诸葛亮 |24000.00
孙尚香 |12000.00
赵云 |15000.00
法正 |10000.00为了解决以上问题我们使用了两个查询语句。那么能不能在一个查询语句中直接返回最终的结果呢
SQL 提供了一种被称为嵌套子查询的功能可以帮助我们解决这个问题。例如
SELECT emp_name, salary
FROM employee
WHERE salary ( SELECT AVG(salary) FROM employee );以上示例中包含两个 SELECT 语句括号内的 SELECT 语句被称为子查询Subquery它的作用是返回员工的平均月薪。包含子查询的 SELECT 语句被称为外部查询Outer Query它的作用是返回月薪大于平均月薪的员工。 ℹ️根据定义子查询就是指嵌套在其他语句包括 SELECT、INSERT、UPDATE、DELETE 等中的 SELECT 语句。子查询也被称为内查询Inner Query或者嵌套查询Nested Query子查询必须位于括号之中。 SQL 语句中的子查询可以按照返回结果分为以下三种类型
标量子查询Scalar Subquery返回单个值一行一列的子查询。上面的示例就是一个标量子查询。行子查询Row Subquery返回单个记录一行多列的子查询。标量子查询是行子查询的一个特例。表子查询Table Subquery返回一个临时表多行多列的子查询。行子查询是表子查询的一个特例。
下面我们分别介绍这三种子查询的作用。
标量子查询
标量子查询返回单个结果值可以像常量一样被用于 SELECT、WHERE、GROUP BY、HAVING 以及 ORDER BY 等子句中。
例如以下查询用于计算员工月薪与平均月薪之间的差值
SELECT emp_name AS 员工姓名, salary AS 月薪,salary - (SELECT AVG(salary) FROM employee) AS 差值
FROM employee;其中外部查询的 SELECT 列表中包含一个标量子查询返回了员工的平均月薪。最终查询返回的结果如下
员工姓名|月薪 |差值
-------|--------|------------
刘备 |30000.00|20168.000000
关羽 |26000.00|16168.000000
张飞 |24000.00|14168.000000
...行子查询
行子查询返回一个一行多列的记录一般较少使用。例如以下语句用于查找所有与“孙乾”在同一个部门并且职位相同的员工
-- Oracle、MySQL、PostgreSQL 以及 SQLite
SELECT emp_name, dept_id, job_id
FROM employee
WHERE (dept_id, job_id) (SELECT dept_id, job_idFROM employee WHERE emp_name 孙乾)
AND emp_name ! 孙乾;其中外部查询的 WHERE 子句中包含一个行子查询返回了“孙乾”所在的部门编号和职位编号这两个值构成了一个记录。然后外部查询使用该记录作为条件进行数据过滤AND 运算符用于排除“孙乾”自己。查询返回的结果如下
emp_name|dept_id|job_id
--------|-------|------
庞统 | 5| 10
蒋琬 | 5| 10
黄权 | 5| 10
...⚠️Microsoft SQL Server 目前不支持行子查询。 表子查询
表子查询返回一个多行数据的结果集通常被用于 WHERE 条件或者 FROM 子句中。
WHERE 条件中的子查询
对于 WHERE 子句中的子查询外部查询的查询条件中不能使用比较运算符。例如
SELECT emp_name
FROM employee
WHERE job_id (SELECT job_id FROM employee WHERE dept_id 1);执行以上语句将会返回类似这样的错误信息子查询返回了多行数据。这是因为单个值外部查询条件中的 job_id与多个值子查询返回的多个 job_id的比较不能使用比较运算符、!、、、、 等。
对于返回多行数据的子查询我们可以使用 IN 或者 NOT IN 运算符进行比较。以上示例可以使用 IN 运算符改写如下
SELECT emp_name
FROM employee
WHERE job_id IN (SELECT job_id FROM employee WHERE dept_id 1);IN 运算符可以用于判断字段的值是否属于某个列表。我们在以上语句中通过子查询生成了一个列表查询返回的结果如下
emp_name
--------
刘备
关羽
张飞 该查询返回了“行政管理部”的全体员工。
ALL、ANY 运算符
ALL 运算符与比较运算符、!、、、、的组合分别表示等于、不等于、小于、小于或等于、大于、大于或等于子查询结果中的全部数据。例如以下语句查找比“研发部”全体员工更晚入职的员工信息
-- Oracle、MySQL、Microsoft SQL Server 以及 PostgreSQL
SELECT emp_name AS 员工姓名, hire_date AS 入职日期
FROM employee
WHERE hire_date ALL (SELECT e.hire_dateFROM employee eJOIN department d ON (d.dept_id e.dept_id)WHERE d.dept_name 研发部);其中子查询返回了“研发部”全体员工的入职日期ALL 运算符返回了比“研发部”全体员工更晚入职的员工。查询返回的结果如下
员工姓名|入职日期
-------|----------
法正 |2017-04-09
庞统 |2017-06-06
蒋琬 |2018-01-28
...“研发部”中最晚入职的是“马岱”入职日期为 2014 年 9 月 16 日。因此以上查询返回了在这个日期之后入职的员工。 ⚠️SQLite 目前不支持 ALL 运算符。 如果我们将以上查询中的 ALL 替换为 ALL 运算符
-- Oracle、MySQL、Microsoft SQL Server 以及 PostgreSQL
SELECT emp_name AS 员工姓名, hire_date AS 入职日期
FROM employee
WHERE hire_date ALL (SELECT e.hire_dateFROM employee eJOIN department d ON (d.dept_id e.dept_id)WHERE d.dept_name 研发部);该查询不会返回任何结果因为没有员工的入职日期等于“研发部”全体员工的入职日期。ALL 运算符相当于多个 AND 运算符的组合IN 运算符相当于多个 OR 运算符的组合。
对于 Oracle 而言ALL 运算符也可以直接与一个常量列表进行比较。例如
-- Oracle
SELECT emp_name AS 员工姓名, salary AS 入职日期
FROM employee
WHERE salary ALL (10000, 15000, 20000);该查询返回了月薪大于 20000 元的员工信息。
除 ALL 运算符外ANY 运算符与比较运算符、!、、、、的组合分别表示等于、不等于、小于、小于或等于、大于、大于或等于子查询结果中的任何数据。
我们可以将前面的 IN 运算符示例使用 ANY 运算符改写如下
-- Oracle、MySQL、Microsoft SQL Server 以及 PostgreSQL
SELECT emp_name
FROM employee
WHERE job_id ANY (SELECT job_id FROM employeeWHERE dept_id 1);查询条件中的ANY 运算符表示等于子查询中的任何结果。该查询同样返回了“行政管理部”的全体员工。 ⚠️SQLite 目前不支持 ANY 运算符。另外在其他数据库中我们也可以使用 SOME 关键字替代 ANY。 如果我们将以上查询中的 ANY 替换为 !ANY 运算符
-- Oracle、MySQL、Microsoft SQL Server 以及 PostgreSQL
SELECT emp_name
FROM employee
WHERE job_id !ANY (SELECT job_id FROM employee WHERE dept_id 1);查询返回的结果如下
emp_name
--------
刘备
关羽
张飞
... 该查询返回了所有的员工。这是因为 ANY 运算符相当于多个 OR 运算符的组合任何员工的 job_id 都至少不等于子查询返回的某个 job_id。
对于 Oracle 而言ANY 运算符也可以直接与一个常量列表进行比较。例如
-- Oracle
SELECT emp_name
FROM employee
WHERE job_id !ANY (1, 2, 2); -- 等价于 job_id ! 1 OR job_id ! 2“行政管理部”3 名员工的 job_id 分别为 1、2、2因此该查询同样返回了所有的员工。
FROM 子句中的子查询
FROM 子句中的子查询相当于一个临时表。例如
SELECT d.dept_name AS 部门名称,ds.avg_salary AS 平均月薪
FROM department d
LEFT JOIN (SELECT dept_id,AVG(salary) AS avg_salaryFROM employeeGROUP BY dept_id) ds
ON (d.dept_id ds.dept_id);其中JOIN 子句中的子查询相当于创建了一个临时表表名为 ds。它包含了部门的编号和平均月薪。然后我们将 department 和 ds 进行了左外连接。最终查询返回的结果如下
部门名称 |平均月薪
--------|------------
行政管理部|26666.666667
人力资源部|13166.666667
财务部 | 9000.000000
研发部 | 7577.777778
销售部 | 5012.500000
保卫部 | 左外连接确保查询结果中不会丢失“保卫部”的信息即使它目前还没有任何员工。 ℹ️不同数据库对于 FROM 子句中的子查询称呼不同。例如MySQL 称之为派生表Derived TableOracle 则称之为内联视图Inline View。 一般来说子查询可以像普通查询一样包含各种子句例如 JOIN、WHERE、GROUP BY 等甚至可以嵌套其他的子查询。但是需要注意不同数据库对于子查询中 ORDER BY 子句的处理方式存在差异。例如
-- MySQL、PostgreSQL 以及 SQLite
SELECT emp_name, salary
FROM employee
WHERE job_id IN (SELECT job_idFROM jobWHERE max_salary 20000ORDER BY job_id)
ORDER BY emp_name;其中子查询返回了最高月薪大于或等于 20000 元的职位同时使用了 ORDER BY 进行数据的排序。对于 MySQL、PostgreSQL 以及 SQLite以上语句可以正常返回查询结果Oracle和 Microsoft SQL Server 则会返回语法错误。
通常来说子查询中的排序没有实际意义不会影响到查询结果和显示顺序。只有外部查询中的 ORDER BY 子句能够决定最终结果的显示顺序。
关联子查询
我们在前面介绍的子查询与外部查询之间没有任何关联可以单独运行并返回结果它们都属于非关联子查询Non-correlated Subquery。此外SQL 中还有一类子查询它们在执行时需要使用外部查询中的字段值从而和外部查询产生关联。因此这类子查询也被称为关联子查询Correlated Subquery。
例如以下语句通过一个关联子查询返回了每个部门的平均月薪
SELECT d.dept_name AS 部门名称,(SELECT AVG(salary) AS avg_salaryFROM employeeWHERE dept_id d.dept_id) AS 平均月薪
FROM department d
ORDER BY d.dept_id;其中子查询的 WHERE 条件中使用了外部查询的部门编号字段d.dept_id从而与外部查询产生关联。我们在执行该查询时首先通过外部查询找出所有的部门数据然后依次将 d.dept_id 传递给子查询执行子查询返回每个部门的平均月薪。 ℹ️对于外部查询返回的每条记录关联子查询都会执行一次数据库可能会对此进行优化非关联子查询只会独立执行一次。 另外WHERE 子句中也可以使用关联子查询。例如以下查询返回了每个部门中最早入职的员工
SELECT d.dept_name AS 部门名称, o.emp_name AS 员工姓名, o.hire_date AS 入职日期
FROM employee o
JOIN department d ON (d.dept_id o.dept_id)
WHERE o.hire_date (SELECT MIN(i.hire_date)FROM employee iWHERE i.dept_id o.dept_id);我们执行该语句时数据库首先找到外部查询中的每个员工依次将 o.dept_id 字段传递给子查询子查询返回当前部门中第一个员工的入职日期然后将该日期传递给外部查询进行判断。查询返回的结果如下
部门名称 |员工姓名|入职日期
--------|-------|----------
行政管理部|刘备 |2000-01-01
行政管理部|关羽 |2000-01-01
行政管理部|张飞 |2000-01-01
人力资源部|诸葛亮 |2006-03-15
财务部 |孙尚香 |2002-08-08
财务部 |孙丫鬟 |2002-08-08
研发部 |赵云 |2005-12-19
销售部 |法正 |2017-04-09“行政管理部”返回了多名员工因为他们在同一天入职。
横向子查询
关联子查询可以使用外部查询中的字段但不能使用同一级别的其他查询或者表中的字段。以下是一个错误的示例
SELECT d.dept_name, t.max_salary
FROM department d
JOIN (SELECT MAX(e.salary) AS max_salaryFROM employee eWHERE e.dept_id d.dept_id) t;其中JOIN 子句中的子查询使用了 department 表中的字段。执行该查询将会返回一个错误信息。因为子查询内部无法使用非外部查询中的字段d.dept_id。
以上语句的原意是查找每个部门中的最高月薪。为了实现这个功能我们可以使用另一种子查询横向子查询LATERAL Subquery。横向子查询允许派生表使用它所在的 FROM 子句中左侧的其他查询或者表。例如
-- Oracle、MySQL 以及 PostgreSQL
SELECT d.dept_name 部门名称, t.max_salary AS 最高月薪
FROM department d
CROSS JOIN
LATERAL (SELECT MAX(e.salary) AS max_salaryFROM employee eWHERE e.dept_id d.dept_id) t;我们在 JOIN 之后指定了 LATERAL 关键字此时子查询可以使用左侧部门表中的字段。查询返回的结果如下
部门名称 |最高月薪
--------|--------
行政管理部|30000.00
人力资源部|24000.00
财务部 |12000.00
研发部 |15000.00
销售部 |10000.00
保卫部 | Oracle、MySQL 以及 PostgreSQL 提供了横向子查询。Microsoft SQL Server 提供了相同的功能但是实现的语法不同
-- Microsoft SQL Server 和 Oracle
SELECT d.dept_name 部门名称, t.max_salary AS 最高月薪
FROM department d
CROSS APPLY (SELECT MAX(e.salary) AS max_salaryFROM employee eWHERE e.dept_id d.dept_id) t;其中CROSS APPLY 是内连接查询的一种变体允许右侧的子查询使用 FROM 子句中左侧查询或者表中的字段。该查询返回的结果和上一个示例相同。
除 CROSS APPLY 外还有一个 OUTER APPLY它是左外连接的一种变体同样允许右侧的子查询使用 FROM 子句中左侧查询或者表中的字段。Microsoft SQL Server 和 Oracle 提供了这两种查询语法。 ⚠️SQLite 目前不支持横向子查询。 EXISTS 运算符
EXISTS 运算符用于判断子查询结果的存在性。只要子查询返回了任何结果就表示满足查询条件如果子查询没有返回任何结果就表示不满足查询条件。
例如以下查询返回了拥有女性员工的部门
SELECT d.dept_name AS 部门名称
FROM department d
WHERE EXISTS (SELECT 1FROM employee eWHERE e.sex 女AND e.dept_id d.dept_id)
ORDER BY dept_name;EXISTS 关键字之后是一个关联子查询首先通过外部查询找到 d.dept_id然后依次将每个 d.dept_id 传递给子查询判断该部门是否存在女性员工。子查询一旦找到女性员工就会立即返回结果而无须遍历所有的员工数据。查询返回的结果如下
部门名称
-------
研发部
财务部查询结果表明“研发部”和“财务部”拥有女性员工。 ℹ️EXISTS 运算符只判断结果的存在性因此子查询 SELECT 列表中的内容可以是任意字段或者表达式通常可以使用一个常量值。 另外NOT EXISTS 可以执行与 EXISTS 相反的操作。如果我们想要查找不存在女性员工的部门可以将上面的语句改写如下
SELECT d.dept_name AS 部门名称
FROM department d
WHERE NOT EXISTS (SELECT 1FROM employee eWHERE e.sex 女AND e.dept_id d.dept_id)
ORDER BY dept_name;查询返回的结果如下
部门名称
-----
人力资源部
保卫部
行政管理部
销售部虽然 [NOT] EXISTS 和 [NOT] IN 运算符都可以用于判断子查询返回的结果但是它们之间存在一个重要的区别[NOT] EXISTS 运算符只检查结果的存在性[NOT] IN 运算符需要比较实际的值是否相等。
当子查询的结果中只有 NULL 值时EXISTS 运算符仍然可以返回结果NOT EXISTS 运算符则不返回结果。但是此时 IN 和 NOT IN 运算符都不会返回结果因为任何数据和空值进行比较的结果均未知。例如以下语句用于查找没有女性员工的部门
SELECT d.dept_name
FROM department d
WHERE NOT EXISTS (SELECT NULLFROM employee eWHERE e.dept_id d.dept_idAND sex 女);对于存在女性员工的部门子查询中返回的数据为 NULLNOT EXISTS 运算符不会返回结果因此查询只会返回没有女性员工的部门。查询返回的结果如下
部门名称
---------
行政管理部
人力资源部
销售部
保卫部 下面我们使用 NOT IN 运算符实现以上查询
SELECT d.dept_name AS 部门名称
FROM department d
WHERE d.dept_id NOT IN (3, 4, NULL);财务部dept_id3和研发部dept_id4拥有女性员工我们特意在 NOT IN 运算符后面增加了一个 NULL 值。该查询返回的结果为空因为它等价于以下查询
SELECT d.dept_name AS 部门名称
FROM department d
WHERE d.dept_id ! 3
AND d.dept_id ! 4
AND d.dept_id ! NULL;查询条件中最后一项的结果为“未知”所以查询不会返回任何结果。
