Oracle Exists運算子

在本教學中，您將學習如何使用Oracle EXISTS運算子來測試行的存在。

Oracle Exists運算子是返回true或false的布林運算子。EXISTS運算子通常與子查詢一起使用來測試行的存在：

SELECT
    *
FROM
    table_name
    WHERE
        EXISTS(subquery);

如果子查詢返回任何行，則EXISTS運算子返回true，否則返回false。另外，當子查詢返回第一行，EXISTS操作符終止子查詢的處理。

Oracle EXISTS範例

下面來看看一些使用EXISTS運算子的例子，來理解它是如何工作的。

1. Oracle EXISTS帶有SELECT語句的範例

請參閱範例資料庫中的以下customers和orders表，它們的ER圖所下所示：

以下範例使用EXISTS運算子來查詢所有有訂單的客戶。

SELECT
    name
FROM
    customers c
WHERE
    EXISTS (
        SELECT
            1
        FROM
            orders
        WHERE
            customer_id = c.customer_id
    )
ORDER BY
    name;

執行上面查詢語句，得到以下結果 -

對於customers表中的每個客戶，子查詢檢查客戶是否出現在orders表上。如果是，則EXISTS操作符返回true並停止掃描orders表。否則，如果子查詢在orders表中找不到客戶資訊，則EXISTS操作符返回false。

如果WHERE子句使用EXISTS運算子來檢索使子查詢的客戶，則返回相關的行。

請注意，Oracle會忽略子查詢中的選擇列表，因此您可以使用任何列，字面值，表示式等。在上面的查詢中是使用了文字數位值： 1。

2. Oracle EXISTS帶有UPDATE語句範例

請參閱以下倉庫(warehouses)和位置(locations)表，它們之間的ER圖如下：

以下語句將更新位於美國的倉庫的名稱：

UPDATE
    warehouses w
SET
    warehouse_name = warehouse_name || ', USA'
WHERE
    EXISTS (
        SELECT
            1
        FROM
            locations
        WHERE
            country_id = 'US'
            AND location_id = w.location_id
    );

對於每個倉庫，子查詢檢查其位置是否在美國(US)。如果是，則WHERE子句中的EXISTS運算子返回true，使外部查詢將字串"，USA"附加到倉庫名稱。否則，由於條件是WHERE子句為false，UPDATE語句將什麼都不做。

以下查詢語句查詢倉庫名稱以驗證更新：

SELECT
    warehouse_name
FROM
    warehouses
INNER JOIN locations
    USING(location_id)
WHERE
    country_id = 'US';

執行上面查詢語句，得到以下結果 -

3. Oracle EXISTS與INSERT語句的例子

假設，我們需要向所有2016年訂單的客戶傳送特殊的讚賞郵件。為此，首先建立一個新錶來儲存客戶的資料：

drop table customers_2016;
CREATE TABLE customers_2016(
    company varchar2(255) NOT NULL,
    first_name varchar2(255) DEFAULT NULL,
    last_name varchar2(255) DEFAULT NULL,
    email varchar2(255) DEFAULT NULL,
    sent_email CHAR(1) DEFAULT 'N'
);

然後，將2016年有訂單的客戶資訊插入到customers_2016表中，參考以下查詢語句：

INSERT
    INTO
        customers_2016(
            company,
            first_name,
            last_name,
            email
        )SELECT
            name company,
            first_name,
            last_name,
            email
        FROM
            customers c
        INNER JOIN contacts ON
            contacts.customer_id = c.customer_id
        WHERE
            EXISTS(
                SELECT * FROM orders
                WHERE customer_id = c.customer_id AND EXTRACT(YEAR FROM order_date)='2016'
           )
        ORDER BY
            company;

執行上面查詢語句，然後查詢SELECT * FROM customers_2016;得到以下結果 -

4. Oracle EXISTS與IN

EXISTS操作符在子查詢返回第一行時停止掃描行，因為它可以確定結果，而IN操作符必須掃描子查詢返回的所有行以結束結果。

另外，IN子句不能與NULL值進行任何比較，但EXISTS子句可以將所有值與NULL值進行比較。例如，第一個語句不返回行記錄，而第二個語句返回customers表中的所有行：

-- 第一個語句
SELECT
    *
FROM
    customers
WHERE
    customer_id IN(NULL);

-- 第二個語句
SELECT
    *
FROM
    customers
WHERE
    EXISTS (
        SELECT
            NULL
        FROM
            dual
    );

通常，當子查詢的結果集較大時，EXISTS運算子比IN運算子更快。相比之下，當子查詢的結果集很小時，IN運算子比EXISTS運算子更快。

在本教學中，您已學習如何使用Oracle EXISTS運算子來測試查詢中是否存在行。