Oracle Exists运算符

精华

小牛编辑

173浏览

2023-03-14

在本教程中，您将学习如何使用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运算符来测试查询中是否存在行。