子查询及高级运算符

子查询

在 SQL 中，子查询是一个查询语句嵌套在另一个查询语句中的查询。

通常在以下场景使用：

在 SELECT 子句中
在 FROM 子句中
使用 IN 或 NOT IN 运算符
比较运算符中
使用 EXISTS 或 NOT EXISTS 运算符
使用 ANY 或 ALL 运算符

另外要注意的一点，需要用括号括起来表示。譬如：

sql

SELECT customer_name, max(order_amount)
FROM orders
WHERE customer_name IN (
  SELECT customer_name 
  FROM orders
)
GROUP BY customer_name;

上述子查询，也可称作普通子查询（相对下节的关联子查询）。

因为该查询的两次 SELECT 语句，都是在同一张表 orders 中进行的。

关联子查询

关联子查询，也可称作外部子查询。

它通常用于将两个或多个表连接在一起，并返回一组符合特定条件的数据。

sql

SELECT customers.customer_name, orders.order_amount
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_amount > (SELECT AVG(order_amount) FROM orders)
ORDER BY customers.customer_name;

EXISTS运算符

EXISTS 运算符用于指定子查询以测试行的存在。

sql

EXISTS (subquery)

如果子查询包含任何行，则 EXISTS 运算符返回 true。否则它返回 false。

EXISTS 运算符在找到行后立即终止查询处理，因此，可以利用 EXISTS 运算符的此功能来提高查询性能。

sql

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    EXISTS(
        SELECT 1
        FROM
            dependents
        WHERE
            dependents.employee_id = employees.employee_id
    );

如果子查询返回 NULL，则 EXISTS 运算符仍返回结果集。

这是因为 EXISTS 运算符仅检查子查询返回的行的存在。

以下语句子查询返回 NULL，但 EXISTS 运算符仍然计算为 true，因此会返回 employees 表中的所有行：

sql

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    EXISTS( SELECT NULL)
ORDER BY first_name , last_name;

和 EXISTS 相对的是 NOT EXISTS，用法一致、意义相反。

ALL运算符

ALL 运算符，将单个值与子查询返回的一组值进行比较。

它在操作意义上相当于 JS 中的数组方法 every。

二者都是用来检测目标是否完全满足条件。

sql

WHERE column_name comparison_operator ALL (subquery)

譬如以下语句查找工资大于部门 ID 为 2 的员工最高工资的所有员工：

sql

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary > ALL (SELECT 
            salary
        FROM
            employees
        WHERE
            department_id = 2)
ORDER BY salary;

ANY运算符

ANY 运算符，也是将单个值与子查询返回的一组值进行比较。

它在操作意义上相当于 JS 中的数组方法 some。

二者都是用来检测目标是否任一满足条件。

sql

WHERE column_name comparison_operator ANY (subquery)

譬如以下语句查找工资大于部门 ID 为 2 的员工最低工资的所有员工：

sql

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary > ANY (SELECT 
            salary
        FROM
            employees
        WHERE
            department_id = 2)
ORDER BY salary;

UNION运算符

UNION 运算符，可用于将两个或多个 SELECT 结果集联合起来，从而合并成一个结果集。

总的来说，该运算符的使用需要满足以下条件：

UNION 运算符必须由两个或多个 SELECT 语句组成。
每个 SELECT 语句中的列数必须相同。
列中的数据类型必须兼容。

sql

SELECT column1, column2, column3, ...
FROM table1
UNION
SELECT column1, column2, column3, ...
FROM table2;

UNION 运算符将这两个 SELECT 语句的结果集组合成一个结果集，并去除重复的行。

如果想要包括重复的行，可以使用 UNION ALL 运算符。

sql

SELECT column1, column2, column3, ...
FROM table1
UNION ALL
SELECT column1, column2, column3, ...
FROM table2;

上述语句将返回一个包含所有行的结果集，包括重复的行。

INTERSECT运算符

UNION 运算符其实是计算两个或多个结果集之间的并集。

而 INTERSECT 运算符则是计算两个或多个结果集之间的交集。

sql

SELECT column1, column2, ...
FROM A
INTERSECT
SELECT column1, column2, ...
FROM B
INTERSECT
SELECT column1, column2, ...
FROM C;

MINUS运算符

MINUS 运算符用于从另一个结果集中减去一个结果集。

sql

SELECT
    id
FROM
    A 
MINUS 
SELECT
    id
FROM
    B;

GROUPING SETS运算符

假如，我们需要使用 GROUP BY 语句进行多个分组查询，并且将结果合并起来：

一种方式是使用 GROUP BY 结合 UNION ALL 语句：

sql

SELECT
    warehouse,
    product, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse,
    product
UNION ALL
SELECT
    warehouse, 
    null,
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse
UNION ALL
SELECT
    null,
    product, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    product
UNION ALL
SELECT
    null,
    null,
    SUM(quantity) qty
FROM
    inventory;

以上查询定义了 4 个分组集：

(warehouse, product)
(warehouse)
(product)
()

但这种查询有两个缺点：

代码冗余，可读性较差；
多次执行 SELECT 查询，影响性能。

因此 SQL 提供了 GROUPING SETS 。

sql

SELECT
    c1,
    c2,
    aggregate_function(c3)
FROM
    table
GROUP BY
    GROUPING SETS (
        (c1, c2),
        (c1),
        (c2),
        ()
    );

使用上述语法来重写一下例子：

sql

SELECT
    warehouse,
    product, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    GROUPING SETS(
        (warehouse,product),
        (warehouse),
        (product),
        ()
    );

ROLLUP运算符

ROLLUP 运算符用于在使用 GROUP BY 子句进行分组聚合时，生成聚合数据的汇总行。

sql

SELECT 
    c1, c2, aggregate_function(c3)
FROM
    table
GROUP BY ROLLUP (c1, c2);

以上语句，会生成以下 3 个分组集：

(c1, c2)
(c1)
()

在 MySql 中，语法略有不同：

sql

SELECT 
    c1, c2, aggregate_function(c3)
FROM
    table_name
GROUP BY c1, c2 WITH ROLLUP;

更多内容可以参考SQL ROLLUP

子查询 ​

关联子查询 ​

EXISTS运算符 ​

ALL运算符 ​

ANY运算符 ​

UNION运算符 ​

INTERSECT运算符 ​

MINUS运算符 ​

GROUPING SETS运算符 ​

ROLLUP运算符 ​

子查询