sql cookbook实验记录（Fourth）

环境介绍：

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

目前可以下载到的最新版本啦！

最近csdn在搞什么付费专栏，其实不是很好理解，但是还是参与一下。我的初衷是凑个热闹。技术这个东西门槛本应该就敞开来，大家都学习学习。不会是武侠小说的绝世武功，永远只是男一号的自嗨。要是古代大大侠剑客有现在的开源精神，那么很多“武林悲剧”就可以避免了。好东西就在这里，爱学不学都是自己的事情。甚至屠龙宝刀也可以来个流水线制作，还支持定制化，印上自己的大名——狗剩的屠龙刀之类的。类比苹果的airpods，大家联想下。

接着昨天的话题。在oracle database中，minus是带有去除重复的功能的。

scott@ORCL>select deptno from dept
  minus
   select deptno from emp;  2    3  

    DEPTNO
----------
	40

不过这里的dept是主键列

select dbms_metadata.get_ddl('TABLE','DEPT') from dual;

所以这里看不出来吧

CONSTRAINT “PK_DEPT” PRIMARY KEY (“DEPTNO”)

我们自己造个数据吧：
for example:

create table dept_1 as select * from dept;
desc dept_1;
select * from dept_1;
insert into dept_1 values(40,'hdp','beijing')
commit;

数据如下

这个时候我们可以测下minus的去重复功能啦。
如下图

我们也可以自己开发一个

 select deptno from dept_1 where deptno not in (select deptno from emp);

但是这里别忘记了用distinct去重复

select distinct deptno from dept_1 where deptno not in (select deptno from emp);

    DEPTNO
----------
	40

NULL值与not in

首先创造数据

create table new_dept (deptno integer);
insert into new_dept values(10);
insert into new_dept values(50);
insert into new_dept values(null);
commit;
select * from new_dept;

这样我们就包含了一张带有null值得表,new_dept列了。

于是

scott@ORCL>select deptno from emp where deptno not in (select deptno from new_dept);

no rows selected

没有返回任何值，与事实不符合。

先看下 in 吧
改下下：

select deptno from dept where deptno  in (null,10,50);
select deptno from dept where deptno is null or deptno=10 or deptno=50;

这两条语句结果是等价的，in其实就是在做数学上的或运算，只要一个条件满足就满足。
但是not in和遇到null返回NULL值的问题怎么解决呢？为什么会产生NULL值呢。
还是涉及到bool运算。

这样not in遇到null值后，传出来的值只有两种可能行 not ture=False not null==NULL,所以not in遇到null后会没有返回值。这是个经典的问题哦。

Oracle帮我考虑了这个问题。使用关键字exsit，和关联子查询:

SELECT *
  FROM new_dept d
 WHERE NOT EXISTS
           (SELECT NULL
              FROM emp e
             WHERE e.deptno = d.deptno);

SELECT *
  FROM new_dept d
 WHERE EXISTS
           (SELECT NULL
              FROM emp e
             WHERE e.deptno = d.deptno);

需要注意的一点如下：

假设我想查询部门号不在deptno中的员工

SELECT *
  FROM emp e
 WHERE NOT EXISTS
           (SELECT NULL
              FROM dept d
             WHERE d.deptno = e.deptno); 

这里只要传入NULL值就可以了。