Oracle子查詢 - tw511教學網

在本教學中，您將了解有助於構建更多可讀查詢的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子查詢，它提供了一種替代方法來構建更具可讀性的查詢，而無需使用複雜的聯接或聯合。