in/exists和not in/not exists语意探讨

最新推荐文章于 2021-04-07 19:37:39 发布

转载最新推荐文章于 2021-04-07 19:37:39 发布 · 76 阅读

本文通过实验对比了SQL中IN与EXISTS、NOT IN与NOT EXISTS在处理NULL值时的不同行为。实验展示了当内部表包含NULL值时，IN与EXISTS能够排除NULL并返回相同结果；NOT IN则会过滤掉外部表中的NULL值；而NOT EXISTS则不会过滤掉外部表中的NULL值。

http://www.xifenfei.com/2108.html

本篇只讨论in/exists和not in/not exists语意，不涉及这些写法的执行效率问题，至于效率问题请见:in/exists和not in/not exists执行效率
1、准备实验环境

   
         C:\Users\XIFENFEI>sqlplus chf_xff/xifenfei
        
         SQL*Plus: Release 11.2.0.1.0 Production on 星期六 12月 10 14:55:14 2011
        
         Copyright (c) 1982, 2010, Oracle.  All rights reserved.
        
         连接到:
        
         Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
        
         With the Partitioning, Oracle Label Security, OLAP, Data Mining,
        
         Oracle Database Vault and Real Application Testing options
        
         SQL> create table t1 (c1 number,c2 number);
        
         表已创建。
        
         SQL> create table t2 (c1 number,c2 number);
        
         表已创建。
        
         SQL> insert into t1 values (1,2);
        
         已创建 1 行。
        
         SQL> insert into t1 values (1,3);
        
         已创建 1 行。
        
         SQL> insert into t2 values (1,2);
        
         已创建 1 行。
        
         SQL> insert into t2 values (1,null);
        
         已创建 1 行。
        
         SQL> insert into t1 values (1,null);
        
         已创建 1 行。
        
         SQL> commit;
        
         提交完成。
        
         SQL> select * from t1;
        
                 C1         C2
        
         ---------- ----------
        
                  1          2
        
                  1          3
        
                  1
        
         SQL> select * from t2;
        
                 C1         C2
        
         ---------- ----------
        
                  1          2
        
                  1

Note:t1和t2表都有null，且t1比t2多一条记录

2、t2做内部表

   
         SQL> select * from t1 where c2 in (select c2 from t2 );
        
                 C1         C2
        
         ---------- ----------
        
                  1          2
        
         SQL> select * from t1 where exists (select c2 from t2 where t1.c2=t2.c2);
        
                 C1         C2
        
         ---------- ----------
        
                  1          2
        
         SQL> select * from t1 where c2 not in (select c2 from t2 );
        
         未选定行
        
         SQL> select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);
        
                 C1         C2
        
         ---------- ----------
        
                  1          3
        
                  1

3、t1为内部表

   
         SQL> select * from t2 where c2 in (select c2 from t1 );
        
                 C1         C2
        
         ---------- ----------
        
                  1          2
        
         SQL> select * from t2 where exists (select c2 from t1 where t1.c2=t2.c2);
        
                 C1         C2
        
         ---------- ----------
        
                  1          2
        
         SQL> select * from t2 where c2 not in (select c2 from t1 );
        
         未选定行
        
         SQL> select * from t2 where not exists (select 1 from t1 where t1.c2=t2.c2);
        
                 C1         C2
        
         ---------- ----------
        
                  1