Oracle子查询

精华

小牛编辑

179浏览

2023-03-14

在本教程中，您将了解有助于构建更多可读查询的Oracle子查询，并可在不使用复杂联接或联合的情况下编写查询。

Oracle子查询简介

子查询是嵌套在另一个语句(如SELECT，INSERT，UPDATE或DELETE)中的SELECT语句。通常，可以在任何使用表达式的地方使用子查询。

来看看下面这个子查询的例子。

以下查询使用MAX()函数从products表中返回产品的最高标价：

SELECT
    MAX( list_price )
FROM
    products;

执行上面查询语句，得到以下结果 -

要查询最贵的产品的详细信息，可在以下查询中使用上面的查询出的标价(8867.99)：

SELECT
    product_id, product_name, list_price
FROM
    products
WHERE
    list_price = 8867.99;

执行上面查询语句，得到以下结果 -

正如上面所看到的，我们需要分别执行两个查询来获取最贵的产品数据信息。通过使用子查询可以将第一个查询嵌套到第二个查询中，如以下查询所示：

SELECT
    product_id,  product_name, list_price
FROM
    products
WHERE
    list_price = (
        SELECT
            MAX( list_price )
        FROM
            products
    );

执行上面查询语句，得到以下结果 -

在此示例中，检索最高价格的查询是子查询，选择详细产品数据的查询是外部查询。子查询嵌套在外部查询中。请注意，子查询必须出现在括号内。

Oracle分两步评估(计算)上面的整个查询：

首先，执行子查询。
其次，在外部查询中使用子查询的结果作为条件查询数据。

嵌套在SELECT语句的FROM子句中的子查询称为内联视图。请注意，其他RDBMS(如MySQL和PostgreSQL)使用术语派生表 而不是内联视图。

嵌套在SELECT语句的WHERE子句中的子查询称为嵌套子查询。

子查询可以包含另一个子查询。Oracle允许在顶级查询的FROM子句中使用无限数量的子查询级别，并在WHERE子句中使用多达255个子查询级别。

Oracle子查询的优点

下面是子查询的主要优点：

提供一种替代方法来解决查询需要复杂联接和联合的数据。
使复杂的查询更具可读性。
允许以一种可以隔离每个部分的方式来构建复杂的查询。

Oracle子查询示例

1. Oracle SELECT子查询示例

以下声明按产品类别返回产品名称，标价和平均标价：

SELECT
    product_name,
    list_price,
    ROUND(
        (
            SELECT
                AVG( list_price )
            FROM
                products p1
            WHERE
                p1. category_id = p2.category_id
        ),
        2
    ) avg_list_price
FROM
    products p2
ORDER BY
    product_name;

执行上面查询语句，得到以下结果 -

在这个例子中，在SELECT子句中使用了一个子查询来获得同一分类的产品的平均标价。 Oracle为外部查询选择的每一行评估(计算)子查询。

这个子查询被称为相关的子查询，我们将在下一个教程中详细介绍。

2. Oracle FROM子句中的子查询示例

SELECT语句的FROM子句中的子查询被称为内联视图，它具有以下语法：

SELECT
 *
FROM
 (subquery) [AS] inline_view;

例如，以下语句返回最高金额的前10个订单：

SELECT
    order_id, order_value
FROM
    (
        SELECT
            order_id,
            SUM( quantity * unit_price ) order_value
        FROM
            order_items
        GROUP BY
            order_id
        ORDER BY
            order_value DESC
    )
WHERE
    rownum <= 10;

执行上面查询语句，得到以下结果 -

在这个查询语句中：

首先，子查询返回由order_value按降序排序的order_id和order_value的列表。
然后，外部查询检索列表顶部的前10行。

3. 带有比较运算符的子查询示例

使用比较运算符，即，>，>=，<，<=，<>，=的子查询通常包含聚合函数，因为集合函数返回可用于比较的单个值和外部查询。

例如，以下查询查找标价大于平均标价的产品。参考以下查询语句 -

SELECT
    product_id,
    product_name,
    list_price
FROM
    products
WHERE
    list_price > (
        SELECT
            AVG( list_price )
        FROM
            products
    )
ORDER BY
    product_name;

执行上面的查询语句，得到类似下面的结果 -

该查询的工作原理如下：

首先，子查询返回所有产品的平均标价。
其次，外部查询获取标价大于子查询返回的平均标价的产品。

4. 具有IN和NOT IN运算符的Oracle子查询

使用IN运算符的子查询通常返回零个或多个值的列表。子查询返回结果集后，外部查询使用它们作为匹配条件。

例如，以下查询查找2017年销售额高于100w的销售员，参考以下查询语句：

SELECT
    employee_id,
    first_name,
    last_name
FROM
    employees
WHERE
    employee_id IN(
        SELECT
            salesman_id
        FROM
            orders
        INNER JOIN order_items
                USING(order_id)
        WHERE
            status = 'Shipped'
        GROUP BY
            salesman_id,
            EXTRACT(
                YEAR
            FROM
                order_date
            )
        HAVING
            SUM( quantity * unit_price )  >= 1000000  
            AND EXTRACT(
                YEAR
            FROM
                order_date) = 2017
            AND salesman_id IS NOT NULL
    )
ORDER BY
    first_name,
    last_name;

执行上面查询语句，得到以下结果 -

Oracle分两步评估(计算)上面示例中的查询：

首先，子查询返回一个销售人员的销售额大于或等于100万的列表。
其次，外部查询使用销售员ID列表来查询雇员表中的数据。

以下语句查询所有2017年尚未下订单的客户：

SELECT
    name
FROM
    customers
WHERE
    customer_id NOT IN(
        SELECT
            customer_id
        FROM
            orders
        WHERE
            EXTRACT(
                YEAR
            FROM
                order_date) = 2017

    )
ORDER BY
    name;

执行上面查询语句，得到以下结果 -

在这个示例的查询中，

首先，子查询返回2017年下单一个或多个订单的客户的ID列表。
其次，外部查询返回带有不在子查询返回的列表中的ID的客户。

在本教程中，您已经了解了有关Oracle子查询，它提供了一种替代方法来构建更具可读性的查询，而无需使用复杂的联接或联合。