Oracle子查詢


在本教學中,您將了解有助於構建更多可讀查詢的Oracle子查詢,並可在不使用複雜聯接或聯合的情況下編寫查詢。

Oracle子查詢簡介

子查詢是巢狀在另一個語句(如SELECTINSERTUPDATEDELETE)中的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_idorder_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子查詢,它提供了一種替代方法來構建更具可讀性的查詢,而無需使用複雜的聯接或聯合。