EXISTS(SELECT NULL) 探索...

今天之前的一个同事，问了一个NOT EXISTS(SELECT NULL FROM TABLE...)的问题，之前也没见过这样的语句，于是一脸懵逼的我在本地尝试了下这是个什么东西的。

这是业务的需求，没错，日本项目，看样子是满足一个什么条件以后，然后执行这块NOT EXISTS代码的。于是我在本地的数据库上找了个类似的表进行测试。

单表测试 select null

随便找了个省份表，以下是部分测试数据：

我们先来看看表中有多少条数据：

SELECT COUNT(*) FROM tprovince t;

然后，我们来看看 select null的话，会得到什么数据。

SELECT NULL FROM tprovince;

结果：

加个where条件试试：

SELECT NULL FROM tprovince WHERE tprovince.FID = '3';

结果：

也就是说，select null 返回的数据，就是表忠有多少行，就返回多少行列值为null的数据。

新增：那么如果查询一个表中不存在的数据会怎样呢？

SELECT NULL FROM tprovince WHERE tprovince.FID = '999999';

结果：

结合之前同事发的需求，然后加上 EXISTS和NOT EXISTS进行下测试：

EXISTS的测试

SELECT * FROM tprovince WHERE EXISTS(SELECT NULL FROM tprovince WHERE tprovince.FID = '3');

结果：

EXISTS中select null 不加条件的测试

SELECT * from tprovince WHERE EXISTS(SELECT NULL FROM tprovince)

结果：

从上面结果可知，两次的查询结果一样，都是查询出来了表中的所有数据。

那么我们把where条件改成一个表中不存在的数据试试。

SELECT * from tprovince WHERE EXISTS(SELECT NULL FROM tprovince WHERE tprovince.FID = '999999');

结果：

NOT EXISTS的测试

SELECT * from tprovince WHERE NOT EXISTS(SELECT NULL FROM tprovince WHERE tprovince.FID = '3');

结果：

NOT EXISTS中select null 不加条件的测试

SELECT * from tprovince WHERE NOT EXISTS(SELECT NULL FROM tprovince)

结果：

查询一个表中不存在的数据：

SELECT * from tprovince WHERE NOT EXISTS(SELECT NULL FROM tprovince WHERE tprovince.FID = '999999');

结果：

好吧，通过上面图中的结果，我们来进行一个总结：

① SELECT NULL FROM tprovince;

这段代码相当于tprovince表中有多少行数据，就返回多少行null数据。相当于 select 1 from tprovince 只不过是以null的形式表达出来。

② SELECT NULL FROM tprovince WHERE tprovince.FID = '3';

这段代码相当于加了一个满足FID=3的条件时，有多少行数据，就返回多少行null数据。

相当于 select 1 from tprovince where tprovince.FID = '3';  只不过是以null的形式表达出来。

• 新增：SELECT NULL FROM tprovince WHERE tprovince.FID = '999999';
• 查询表中不存在的数据时，虽然表示的也是返回的null但是其查询结果是没有的。这个和②的不同之处就是一个是有记录，一个是没记录的。
  

③ SELECT * from tprovince WHERE EXISTS(SELECT NULL FROM tprovince WHERE tprovince.FID = '3');

这段代码相当于 SELECT * from tprovince带有where条件的select null的结果是有数据的，EXISTS中的表达为TRUE，因此会查询出来表中所有的数据;

④ SELECT * from tprovince WHERE EXISTS(SELECT NULL FROM tprovince)

这段代码也相当于 SELECT * from tprovince不带where条件的select null是没有什么意义的，只要表中有数据，其EXISTS中的表达就会为TRUE;

• 新增：SELECT * from tprovince WHERE EXISTS(SELECT NULL FROM tprovince WHERE tprovince.FID = '999999');
• 当带where条件的select null查询了一个表中不存在的数据时，那么EXISTS的表达就为FALSE了，固select *不会查出表中的所有数据。
  

由图中结果可知，③、④两种用法在EXISTS的测试中，select null加不加where条件，对数据最后的结果没有什么影响的。其效果都相当于

SELECT * from tprovince

由图中结果可知，③、④两种用法在EXISTS的测试中，如果select null 查询出表中有需要的数据时，EXISTS的表达是为TRUE的。当select null 查询一个表中不存在的结果时，EXISTS的表达为FALSE，因此查询结果是没有记录的。所以，where条件对最后结果是有影响的。

⑤ SELECT * from tprovince WHERE NOT EXISTS(SELECT NULL FROM tprovince WHERE tprovince.FID = '3');

这段代码相当于 SELECT * from tprovince WHERE tprovince.FID = ‘一个表中不存在的值’

查询出的结果是无记录的。

⑥ SELECT * from tprovince WHERE NOT EXISTS(SELECT NULL FROM tprovince)

这段代码也相当于 SELECT * from tprovince WHERE tprovince.FID = ‘一个表中不存在的值’

查询出的结果也是无记录的。

• 新增：SELECT * from tprovince WHERE NOT EXISTS(SELECT NULL FROM tprovince WHERE tprovince.FID = '999999');
• 查询表中不存在的数据时，在NOT EXISTS中，其判定结果就为TRUE了，所以查询出了所有的数据。
  

由图中结果可知，⑤、⑥两种用法在NOT EXISTS的测试中，select null加不加where条件，对数据最后的结果也没有什么影响的。都是返回一个空的数据集。其效果相当于

SELECT * from tprovince WHERE tprovince.FID = ‘一个表中不存在的值’

由图中结果可知，⑤、⑥两种用法在NOT EXISTS的测试中，如果select null 的数据在表中存在，那么其返回的就是一个空的数据，和EXISTS相反，如果select null的数据在表中不存在，那么就返回表中所有的数据，因为这个时候NOT EXISTS的判定结果是TRUE的。

多表测试select null

单表测试完了以后，考虑到有可能他们是几个表连接查询，于是进行了多表查询的操作的测试。

这次找了两张表titemcomment和titem，其中关联条件为titemcomment.FITEMID = titem.FID表进行测试，那么先看一看他们join以后的数据吧。

SELECT * FROM titemcomment tc JOIN titem t ON tc.FITEMID = t.FID;

查询结果：

由图中可以看到，总共有6条数据是有关联的。

那么我们选择一条 titemcomment的FITEMID为7ccf27e9-8466-11e5-9843-005056010101的数据作为select null from table 的where 条件吧。

然后进行上面单表查询时，几种不同的情况：

EXISTS的测试

SELECT * FROM titemcomment tc JOIN titem t ON tc.FITEMID = t.FID 
WHERE EXISTS(SELECT NULL FROM titemcomment tt WHERE tt.FITEMID = '7ccf27e9-8466-11e5-9843-005056010101');

查询结果：

EXISTS中select null不加条件的测试

SELECT * FROM titemcomment tc JOIN titem t ON tc.FITEMID = t.FID 
WHERE EXISTS(SELECT NULL FROM titemcomment tt);

查询结果：

EXISTS中select null不存在的数据

SELECT * FROM titemcomment tc JOIN titem t ON tc.FITEMID = t.FID 
WHERE EXISTS(SELECT NULL FROM titemcomment tt WHERE tt.FITEMID = '1234');

查询结果：

NOT EXISTS的测试

NOT EXISTS中select null 存在的数据的测试

SELECT * FROM titemcomment tc JOIN titem t ON tc.FITEMID = t.FID 
WHERE EXISTS(SELECT NULL FROM titemcomment tt WHERE tt.FITEMID = '7ccf27e9-8466-11e5-9843-005056010101');

查询结果：

NOT EXISTS中select null 不加条件的测试

SELECT * FROM titemcomment tc JOIN titem t ON tc.FITEMID = t.FID 
WHERE NOT EXISTS(SELECT NULL FROM titemcomment tt);

查询结果：

NOT EXISTS中select null 不存在的数据的测试

SELECT * FROM titemcomment tc JOIN titem t ON tc.FITEMID = t.FID 
WHERE NOT EXISTS(SELECT NULL FROM titemcomment tt WHERE tt.FITEMID = '1234');

查询结果：

通过上面的对比实验，发现：

select null from table其实和select 1 from table的结果类似，只不过一个返回的列值为null，一个为1；

select null 与 EXISTS 配合使用时，作为EXISTS的子条件去查询，SELECT * FROM TABLE WHERE EXISTS(SELECT NULL FROM TABLE1 where ....)只要TABLE1中有数据，那么其结果就是返回TABLE中的所有数据，与select null 的where条件无关如果select null 可以查询到数据，那么则会把TABLE里的数据全部返回，否则就返回空数据集。

select null 与NOT EXISTS 配合使用时，作为NOT EXISTS的子条件去查询，SELECT * FROM TABLE WHERE NOT EXISTS(SELECT NULL FROM TABLE1 where ....)只要TABLE1中有数据，那么其结果就相当于返回TABLE中不存在的数据，与select null 的where条件无关如果select null 可以查询到数据，那么则不会把TABLE的数据进行返回，如果select null 查询不到数据，就会把TABLE的所有数据进行返回。与EXISTS使用方法相反。

不过这种select null的写法也是奇葩。