WHERE子句中的MySQL子查询

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

具有比较运算符的MySQL子查询

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

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

SELECT 
    customerNumber, 
    checkNumber, 
    amount
FROM
    payments
WHERE
    amount = (SELECT MAX(amount) FROM payments);

除相等运算符外，还可以使用其他比较运算符，例如大于(>)，小于(<)。

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

SELECT 
    customerNumber, 
    checkNumber, 
    amount
FROM
    payments
WHERE
    amount > (SELECT 
            AVG(amount)
        FROM
            payments);

在此示例中：

• 首先，使用子查询通过AVG聚合函数计算平均付款。

• 然后，查询大于外部查询中子查询返回的平均付款的付款。

使用IN和NOT IN运算符的MySQL子查询

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

请参阅以下客户和订单表：

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

SELECT 
    customerName
FROM
    customers
WHERE
    customerNumber NOT IN (SELECT DISTINCT
            customerNumber
        FROM
            orders);

MySQL相关子查询

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

SELECT 
    orderNumber, 
    COUNT(orderNumber) AS items
FROM
    orderdetails
GROUP BY orderNumber;

与独立子查询不同，关联子查询是使用外部查询中的数据的子查询。
换句话说，相关的子查询取决于外部查询。
关联查询的外部查询中的每一行都会被评估一次。

在以下查询中，我们选择购买价格大于每个产品线中所有产品的平均购买价格的产品。

SELECT 
    productname, 
    buyprice
FROM
    products p1
WHERE
    buyprice > (SELECT 
            AVG(buyprice)
        FROM
            products
        WHERE
            productline = p1.productline)

内部查询针对每个产品系列执行，因为产品系列针对每一行进行了更改。
因此，平均购买价格也将发生变化。
外部查询仅从子查询中过滤购买价格大于每个产品系列平均购买价格的产品。

具有EXISTS和NOT EXISTS的MySQL子查询

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

SELECT 
    *
FROM
    table_name
WHERE
    EXISTS( subquery );

在上面的查询中，如果子查询返回任何行，则EXISTS子查询返回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;

它返回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);

在本教程中，我们向您展示了如何使用MySQL子查询和相关子查询构造更复杂的查询。

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

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

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

以下查询返回在美国办公室工作的员工。

SELECT 
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            country = 'USA');

在此示例中：

• 子查询返回位于美国的办事处的所有办事处代码。

• 外部查询选择在办公室工作的员工的姓氏和名字，这些员工的办公室代码在子查询返回的结果集中。

执行查询时，子查询将首先运行并返回结果集。
然后，此结果集用作外部查询的输入。

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;

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