MySQL Subquery

摘要：在本教程中，我们将向您展示如何使用 MySQL 子查询编写复杂查询并解释相关子查询的概念。

MySQL子查询简介

MySQL 子查询是嵌套在另一个查询中的查询，例如SELECT 、 INSERT 、 UPDATE或DELETE 。此外，子查询可以嵌套在另一个子查询中。

MySQL 子查询称为内部查询，而包含子查询的查询称为外部查询。子查询可以在使用表达式的任何地方使用，并且必须用括号括起来。

例如，以下查询使用子查询返回在美国办公室工作的员工。

SELECT 
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            country = 'USA');Code language: SQL (Structured Query Language) (sql)

在这个例子中：

• 该子查询返回位于美国的办事处的所有办事处代码。
• 外部查询选择在子查询返回的结果集中办公室代码所在办公室工作的员工的姓氏和名字。

执行查询时，MySQL 首先评估子查询，并将子查询的结果用于外部查询。

在 WHERE 子句中使用 MySQL 子查询

我们将使用示例数据库中的payments表进行演示。

带比较运算符的 MySQL 子查询

您可以使用比较运算符（例如 =、>、<）将子查询返回的单个值与WHERE子句中的表达式进行比较。

例如，以下查询返回付款金额最高的客户。

SELECT 
    customerNumber, 
    checkNumber, 
    amount
FROM
    payments
WHERE
    amount = (SELECT MAX(amount) FROM payments);Code language: SQL (Structured Query Language) (sql)

试试看

除了=运算符之外，您还可以使用其他比较运算符，例如大于 ( > )、大于或等于 (>=) 小于 ( < ) 和小于或等于 (<=)。

例如，您可以使用子查询查找付款高于平均付款的客户：

SELECT 
    customerNumber, 
    checkNumber, 
    amount
FROM
    payments
WHERE
    amount > (SELECT 
            AVG(amount)
        FROM
            payments);Code language: SQL (Structured Query Language) (sql)

试试看

在这个例子中：

• 首先，使用子查询获取平均付款。
• 然后，选择大于外部查询中子查询返回的平均付款的付款。

带有IN和NOT IN运算符的 MySQL 子查询

如果子查询返回多个值，则可以在WHERE子句中使用其他运算符，例如IN或NOT IN运算符。

请参阅以下customers和orders表：

例如，您可以使用带有NOT IN运算符的子查询来查找未下订单的客户，如下所示：

SELECT 
    customerName
FROM
    customers
WHERE
    customerNumber NOT IN (SELECT DISTINCT
            customerNumber
        FROM
            orders);Code language: SQL (Structured Query Language) (sql)

试试看

FROM子句中的 MySQL 子查询

当您在FROM子句中使用子查询时，从子查询返回的结果集将用作临时表。该表称为派生表或具体化子查询。

以下子查询查找销售订单中的最大、最小和平均商品数：

SELECT 
    MAX(items), 
    MIN(items), 
    FLOOR(AVG(items))
FROM
    (SELECT 
        orderNumber, COUNT(orderNumber) AS items
    FROM
        orderdetails
    GROUP BY orderNumber) AS lineitems;Code language: SQL (Structured Query Language) (sql)

试试看

请注意， FLOOR()用于删除项目平均值中的小数位。

MySQL相关子查询

在前面的示例中，您注意到子查询是独立的。这意味着您可以将子查询作为独立查询执行，例如：

SELECT 
    orderNumber, 
    COUNT(orderNumber) AS items
FROM
    orderdetails
GROUP BY orderNumber;Code language: SQL (Structured Query Language) (sql)

与独立子查询不同，相关子查询是使用外部查询中的数据的子查询。换句话说，相关子查询依赖于外部查询。相关子查询针对外部查询中的每一行计算一次。

请参阅示例数据库中的以下products表：

以下示例使用相关子查询来选择购买价格大于每个产品中所有产品的平均购买价格的产品 生产线。

SELECT 
    productname, 
    buyprice
FROM
    products p1
WHERE
    buyprice > (SELECT 
            AVG(buyprice)
        FROM
            products
        WHERE
            productline = p1.productline)Code language: SQL (Structured Query Language) (sql)

试试看

在此示例中，外部查询和相关子查询都引用相同的products表。因此，我们需要在外部查询中为products表使用表别名p1 。

与常规子查询不同，您不能像这样独立执行相关子查询。如果这样做，MySQL 将不知道 p1 表并会发出错误。

SELECT 
    AVG(buyprice)
FROM
    products
WHERE
    productline = p1.productline;

对于products （或 p1）表中的每一行，相关子查询需要执行一次以获得该行productline中所有产品的平均购买价格。

如果当前行的购买价格大于相关子查询返回的平均购买价格，则查询将该行包含在结果集中。

MySQL 子查询包含EXISTS和NOT EXISTS

当子查询与EXISTS或NOT EXISTS运算符一起使用时，子查询返回布尔值TRUE或FALSE 。以下查询说明了与EXISTS运算符一起使用的子查询：

SELECT 
    *
FROM
    table_name
WHERE
    EXISTS( subquery );Code language: SQL (Structured Query Language) (sql)

在上面的查询中，如果子查询返回任何行， EXISTS subquery返回TRUE ，否则返回FALSE 。

EXISTS和NOT EXISTS经常用在相关子查询中。

让我们看一下示例数据库中的orders和orderdetails表：

以下查询查找总价值大于 60K 的销售订单。

SELECT 
    orderNumber, 
    SUM(priceEach * quantityOrdered) total
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
GROUP BY orderNumber
HAVING SUM(priceEach * quantityOrdered) > 60000;Code language: SQL (Structured Query Language) (sql)

它返回 3 行，这意味着有 3 个销售订单，其总价值大于 60K。

您可以将上面的查询用作相关子查询，通过EXISTS运算符查找至少下过一份总价值大于 60K 的销售订单的客户：

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    EXISTS( SELECT 
            orderNumber, SUM(priceEach * quantityOrdered)
        FROM
            orderdetails
                INNER JOIN
            orders USING (orderNumber)
        WHERE
            customerNumber = customers.customerNumber
        GROUP BY orderNumber
        HAVING SUM(priceEach * quantityOrdered) > 60000);Code language: SQL (Structured Query Language) (sql)

概括

• 子查询是嵌套在另一个查询（或外部查询）中的查询。
• 相关子查询依赖于外部查询。