SQL技巧：IN&EXISTS

在SQL查询语句的构建中，IN & NOT IN和EXISTS & NOT EXISTS是两种常见且重要的条件语句，它们在满足不同的查询需求和性能考量时发挥着重要作用。本文将详细介绍Oracle数据库中这两者的基本操作以及潜在的性能影响。

数据准备

--创建表1test
CREATE TABLE t_test
(
    id NUMBER not null primary key,
    name NVARCHAR2(60) not null,
    score NUMBER(4,0) NOT NULL,
    createtime TIMESTAMP (6) not null
)

--随机插入数据
 Insert into t_test
 select rownum,dbms_random.string('*',dbms_random.value(6,20)),dbms_random.value(0,20),sysdate from dual
 connect by level<=10000000
 
--创建表2
create table t_test2 as select * from t_test where 1=2

--随机插入数据
 Insert into t_test
 select rownum,dbms_random.string('*',dbms_random.value(6,20)),dbms_random.value(0,20),sysdate from dual
 connect by level<=100000

IN 关键字

IN 是一个强大的SQL操作符，允许我们在指定的一系列值中匹配数据。其基本语法如下：

SELECT column1, column2...
FROM tablename
WHERE column IN (value1, value2,...);

例如，如果我们想获取所有位于10, 20或30的员工记录，在t_test表上使用IN操作就很合适：

SELECT id,score 
FROM t_test 
WHERE id IN ('10', '20', '30');

在这个例子中，IN操作符检查ID列的每一个值是否与列表(’10’, ’20’, ’30’)中的任意一个值匹配。如果匹配，那么该行将被包含在结果集中。

例如，如果我们想获取所有位于表1的员工记录和表2的ID和score相同的数据，又该怎么去获取呢？

SELECT  id,score  
FROM t_test 
WHERE (id,score) IN (select id,score from t_test2 );

那么以上的例子使用EXISTS该怎么实现呢?

EXISTS 关键字

与 IN 操作不同的是，EXISTS 操作对一个子查询进行布尔测试。如果子查询返回至少一个记录， EXISTS 就返回TRUE，反之返回FALSE。在某些情况下，这可以有效地导向我们查找有（或没有）匹配项存在的记录。这种操作的一般语法如下：

SELECT column1, column2...
FROM tablename
WHERE EXISTS (subquery);

例如，如果我们想获取所有位于表1的员工记录和表2的ID和score相同的数据，又该怎么去获取呢？

SELECT id,score
FROM t_test
WHERE EXISTS (SELECT 1 FROM t_test2 WHERE t_test.id = t_test2.id and t_test.score = t_test2.score);

NOT IN 和 NOT EXISTS 同上大家可以自行设置，这里不做过多的赘述。

那么除了以上两种方法有没有其他的方式来解决这个问题呢？当然有的下面我们来看一下之前讲过的表关联inner join也是可以实现的：

SELECT id,score
FROM t_test
inner join t_test2 on t_test.id = t_test2.id and t_test.score = t_test2.score

性能比较

当涉及到性能比较时，很难给出绝对的结论哪个更快。具体选用哪一种方法取决于多种因素，包括但不限于查询结果集的大小、索引的设置和数据库版本。t_test表1千万条数据，t_test2表10万条数据。从下面执行计划可以看出来除了inner join写法利用了HASH JOIN,其他两个都是嵌套循环，效率其实时一样的。

IN 方法的执行计划：

EXISTS 方法的执行计划：

inner join 方法的执行计划：

使用IN的场景：将结果集预加载到内存中，在结果集不太大而且基础表行数更大的情境下，IN 通常更有效率。
使用EXISTS的场景：当子查询返回的结果集非常大时，EXISTS往往表现更好，尤其是与 NOT 同时使用时。这是因为 EXISTS 在找到第一个匹配的记录后便停止搜索，而 IN 则需要遍历整个列表。例如，如果你在扫描一个大表（数百万行），并且试图找到在一个小表中存在的行，那么使用 EXISTS 可能更高效。

结论

IN & NOT IN 和 EXISTS & NOT EXISTS 在Oracle数据库中都是非常重要的工具，理解它们的运作方式与适用场景，可以帮助我们进行更有效的查询设计。记住，在实现同样的功能时，两者并没有绝对的优劣之分，选择哪种方法应该根据你的特定需求、数据环境和性能测试结果。每一种SQL操作符都有其存在的价值，熟练掌握和灵活使用它们，才能真正提升我们的SQL运用水平。