oracle中not exists的用法是什么-猿码集

1. 简介

Oracle是一个广泛使用的关系型数据库管理系统，其提供了一系列强大的操作符和函数来查询和处理数据。在Oracle中，not exists是一种常用的操作符，用于判断一个查询结果是否为空。本文将详细介绍Oracle中not exists的用法。

2. not exists的语法

not exists操作符的基本语法如下：

SELECT ...
FROM ...
WHERE NOT EXISTS(subquery);

其中subquery是一个子查询，用于查询需要排除的数据。若子查询的结果为空，则not exists返回true，否则返回false。

2.1 示例

假设有两张表student和score，其结构如下所示：

CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
CREATE TABLE score (
    student_id INT,
    course VARCHAR(50),
    score INT
);
INSERT INTO student VALUES(1, 'Tom');
INSERT INTO student VALUES(2, 'Jerry');
INSERT INTO score VALUES(1, 'Math', 90);
INSERT INTO score VALUES(2, 'English', 80);
INSERT INTO score VALUES(2, 'Math', 85);

现在需要查询所有没有score信息的学生信息，可以使用not exists实现：

SELECT *
FROM student s
WHERE NOT EXISTS (
    SELECT * 
    FROM score sc 
    WHERE s.id = sc.student_id
);

查询结果如下：


+----+------+
| id | name |
+----+------+
|  1 | Tom  |
+----+------+

3. not exists和not in的区别

not exists和not in都是用于查询排除数据的操作符，它们都可以实现相同的查询效果。但是，它们之间仍然存在一些区别。

3.1 执行方式

not exists进行子查询时，当子查询返回结果非空时，Oracle仅仅查找到结果集的第一个行就会停止查询。而not in操作符则会扫描整个结果集，这就会使得not exists在处理大批量数据时能够更快地得到查询结果。

3.2 空值处理

not exists在处理空值时，不会将其视为一个特殊的值，而是将其视为普通的值对待；而not in则像普通比较运算符一样，无法识别空值。

3.3 示例

假设有两张表t1和t2：

CREATE TABLE t1 (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
CREATE TABLE t2 (
    t1_id INT PRIMARY KEY,
    value INT
);
INSERT INTO t1 VALUES(1, 'Tom');
INSERT INTO t1 VALUES(2, 'Jerry');
INSERT INTO t2 VALUES(1, 1);

现在需要查询所有没有关联t2表信息的t1表信息，可以使用not exists和not in实现：

-- 使用not exists
SELECT *
FROM t1
WHERE NOT EXISTS (
    SELECT * 
    FROM t2 
    WHERE t1.id = t2.t1_id
);
-- 使用not in
SELECT *
FROM t1
WHERE id NOT IN (
    SELECT t1_id 
    FROM t2 
);

查询结果均为：


+----+------+
| id | name |
+----+------+
|  2 | Jerry|
+----+------+

但是，当t2表中包含空值时，not exists和not in的区别就会体现出来。

INSERT INTO t2 VALUES(NULL, 2);

此时，使用not exists查询的结果不会受到空值影响，仍然只查询到id为2的t1信息；而使用not in查询的结果则会变成查询不到任何信息。

4. not exists的应用场景

not exists操作符可以应用于许多场景中，下面介绍几个常见的应用场景。

4.1 查询没有关联子表信息的主表信息

假设有两张表t1和t2，其中t1包含主表信息，t2包含子表信息。现在需要查询所有没有关联子表信息的主表信息，可以使用not exists实现，示例详见上文。

4.2 查询子表信息中与主表信息不一致的记录

假设有两张表t1和t2，其中t1包含主表信息，t2包含子表信息。现在需要查询子表信息中与主表信息不一致的记录，可以使用not exists实现：

SELECT *
FROM t2
WHERE NOT EXISTS (
    SELECT * 
    FROM t1 
    WHERE t1.id = t2.t1_id AND t1.name = t2.name
);

查询结果为：


+------+-------+
| t1_id| value |
+------+-------+
|    1 |     1 |
+------+-------+

4.3 查询存在相同值的数据

假设有一张表t，其中包含字段value。现在需要查询所有存在相同value值的记录，可以使用not exists实现：

SELECT *
FROM t t1
WHERE EXISTS(
    SELECT *
    FROM t t2
    WHERE t1.value = t2.value
        AND t1.rowid != t2.rowid
)
AND NOT EXISTS(
    SELECT *
    FROM t t3
    WHERE t3.value = t1.value
        AND t3.rowid != t1.rowid
        AND t3.rowid < t1.rowid
);

查询结果中，每个相同的value只会显示一次，且显示最先出现的记录。

5. 总结

not exists是Oracle中一个常用的操作符，可用于判断一个查询的结果是否为空，以及查询不存在于子查询中的数据。not exists和not in虽然可以实现相同的查询效果，但其在执行方式和空值处理方面仍存在一些差异，需要根据具体应用场景进行选择。not exists可以应用于许多场景中，如查询没有关联子表信息的主表信息、查询子表信息中与主表信息不一致的记录、查询存在相同值的数据等。

oracle中not exists的用法是什么