Oracle Exists运算符

在本教程中,您将学习如何使用Oracle EXISTS运算符来测试行的存在。

Oracle Exists运算符

Oracle Exists运算符是返回truefalse的布尔运算符。EXISTS运算符通常与子查询一起使用来测试行的存在:

SELECT
    *
FROM
    table_name
    WHERE
        EXISTS(subquery);

如果子查询返回任何行,则EXISTS运算符返回true,否则返回false。 另外,当子查询返回第一行,EXISTS操作符终止子查询的处理。

Oracle EXISTS示例

下面来看看一些使用EXISTS运算符的例子,来理解它是如何工作的。

1. Oracle EXISTS带有SELECT语句的示例

请参阅示例数据库中的以下customersorders表,它们的ER图所下所示:

以下示例使用EXISTS运算符来查找所有有订单的客户。

SELECT
    name
FROM
    customers c
WHERE
    EXISTS (
        SELECT

        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

        FROM
            locations
        WHERE
            country_id = 'US'
            AND location_id = w.location_id
    );

对于每个仓库,子查询检查其位置是否在美国(US)。如果是,则WHERE子句中的EXISTS运算符返回true,使外部查询将字符串",USA"附加到仓库名称。否则,由于条件是WHERE子句为falseUPDATE语句将什么都不做。

以下查询语句查询仓库名称以验证更新:

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运算符来测试查询中是否存在行。


上一篇:Oracle子查询

下一篇:Oracle Not Exists运算符

关注微信小程序
程序员编程王-随时随地学编程

扫描二维码
程序员编程王

扫一扫关注最新编程教程