Oracle All運算子

在本教學中，您將學習如何使用Oracle ALL運算子將值與列表或子查詢進行比較。

Oracle ALL操作符簡介

Oracle ALL操作符用於將值與子查詢返回的值列表或結果集進行比較。

以下顯示了與列表或子查詢一起使用的ALL運算子的語法：

operator ALL ( v1, v2, v3)

operator ALL ( subquery)

在這個語法中，

• ALL運算子前面必須有一個運算子，例如：=，!=,>，>=，<，<=，後跟一個列表或子查詢。
• 列表或子查詢必須用圓括號包圍。

使用ALL運算子將值與列表進行比較時，Oracle將初始條件擴充套件到列表的所有元素，並使用AND運算子將它們組合在一起，如下所示：

SELECT
    *
FROM
    table_name
WHERE
    c > ALL (
        v1,
        v2,
        v3
    );

--  以上語句轉換為 ALL 運算子後

SELECT
    *
FROM
    table_name
WHERE
    c > v1
    AND c > v2
    AND c > v3;

如果使用ALL運算子將值與子查詢返回的結果集進行比較，則Oracle執行兩步轉換，如下所示：

SELECT product_name,
       list_price
FROM products
WHERE list_price > ALL
    ( SELECT list_price
     FROM products
     WHERE category_id = 1 )
ORDER BY product_name;

-- 1st step: transformation that uses ANY

SELECT product_name,
       list_price
FROM products p1
WHERE NOT( p1.list_price <= ANY
            (SELECT list_price
             FROM products p2
             WHERE category_id = 1 ))
ORDER BY product_name; 

-- 2nd step: transformation that eliminates ANY
SELECT product_name,
       list_price
FROM products p1
WHERE NOT EXISTS
    (SELECT p2.list_price
     FROM products p2
     WHERE p2.category_id = 1
       AND p2.list_price >= p1.list_price )
ORDER BY product_name;

如果子查詢不返回行，則以下條件的計算結果為true：

operator ALL (subquery)

這意味著在WHERE子句中使用上述條件的查詢將返回所有行，以防子查詢返回任何行。

SELECT
    *
FROM
    table_name
WHERE
    col operator ALL(subquery);

Oracle ALL運算子範例

以下範例查詢每個類別中產品的平均標價：

SELECT
    ROUND( AVG( list_price ),2 ) avg_list_price
FROM
    products
GROUP BY
    category_id
ORDER BY
    avg_list_price DESC;

執行上面範例程式碼，得到以下結果 -

1. col> ALL(list)

如果col大於列表中的最大值，則表示式的計算結果為true。

例如，以下查詢查詢標價大於平均價格列表最大價格的產品：

SELECT
    product_name,
    list_price
FROM
    products
WHERE
    list_price > ALL(
        SELECT
            AVG( list_price )
        FROM
            products
        GROUP BY
            category_id
    )
ORDER BY
    list_price ASC;

執行上面範例程式碼，得到以下結果 -

2. col < ALL(list)

如果col小於列表中的最小值，則表示式的計算結果為true。

SELECT
    product_name,
    list_price
FROM
    products
WHERE
    list_price < ALL(
        SELECT
            AVG( list_price )
        FROM
            products
        GROUP BY
            category_id
    )
ORDER BY
    list_price DESC;

例如，以下查詢將查詢標價低於平均價格列表中最小價格的產品：

3. col> = ALL(list)

如果col大於或等於列表中的最大值，則表示式的計算結果為true。

以下語句返回列表價格大於或等於2200的CPU產品：

SELECT
    product_name,
    list_price
FROM
    products
WHERE
    list_price >= ALL(
        1000,
        1500,
        2200
    )
    AND category_id = 1
ORDER BY
    list_price DESC;

執行上面範例程式碼，得到以下結果 -

4. col <= ALL(list)

如果col小於或等於列表中的最小值，則表示式的計算結果為true。

以下語句返回標價小於或等於列表中最小值的977.99的CPU產品。

SELECT
    product_name,
    list_price
FROM
    products
WHERE
    list_price <= ALL(
        977.99,
        1000,
        2200
    )
    AND category_id = 1
ORDER BY
    list_price DESC;

執行上面範例程式碼，得到以下結果 -

5. col = ALL ( list)

如果col匹配列表中的所有值，則表示式的計算結果為true。

6. col！= ALL(list)

如果col不匹配列表中的任何值，則表示式的計算結果為true。

在本教學中，您已經學習如何使用Oracle ALL操作符將值與列表或子查詢進行比較。