sql学习中遇到not exists相关问题

数据库学习

EXISTS与NOT EXISTS

SQL中没有全程量词，但是可以把带有全称量词的谓词转换为等价的带有存在量词的谓词

如典型的学生数据库查询问题：

查询选修了全部课程的学生姓名

selcet Sname
from student
where not exists
	(select *
	from Course
	where not exists
		(select *
		from SC
		where Sno=Student.Sno
			and Cno=Course.Cno)
		)
	);

这个例子较为简单，理解起来会容易很多，刚开始我的理解是，从SC表中筛选出所有当前学生未选择的课程，如果不存在，则内层NOT EXISTS为true，最终返回一个课程列表，如果该课程列表为空则外层NOT EXISTS返回true，记录下该学生。这个理解大致没问题，但对于NOT EXISTS的一些运行细节理解并不到位。

如，现有供应商表S(SNO,SNAME,STATUS,CITY),
零件表P(PNO,PNAME,COLOR,WEIGHT)
项目表J(JNO,JNAME,CITY)
供应情况表SPJ(SNO,PNO,JNO,QTY)

求至少用了供应商S1所供应的全部零件的工程号JNO。

select JNO
from J
where not exists
	(
	select PNO
	from P 
	where  not exists
		(
		select *
		from SPJ
		where  PNO=first.PNO and JNO=J.JNO and SNO='S1'
		)
);

这是我最初的解法，但运行结果为空（实际这个问题SNO='S1’时，即使用了S1供应的全部零件的工程确实为空，但问题主要还在于我的解法，因为即使换为S2，运行依旧为空）。

遇到这种情况，我的第一反应是视图下一个断点先弄清整个NOT EXISTS结构的运行过程。网上了也找了找相关的sql下断点的操作，确实可以下断点，不过这是一整个语句并非多个语句，怎么下断点这个以后可以尝试。

在无法用断点调试的时候我分离了部分代码出来单独测试：

select distinct  PNO
from P first
where not exists
	(
	select *
	from SPJ
	where first.PNO=SPJ.PNO and SPJ.SNO='S1' 
	);

运行结果如图，得到的结果即所有非S1供应的零件。

但当我外套一个NOT EXISTS时试图得到所有S1供应的零件时却并不如意料那般顺利：

select second.PNO
from P second
where not exists
	(select distinct  PNO
	from P first
	where not exists
		(
		select *
		from SPJ
		where second.PNO=PNO and first.PNO=SPJ.PNO and SPJ.SNO='S1' 
		)
	);

运行结果如图，预期结果应为(P1,P2)，而实际结果却为空。

其实NOT EXISTS结构并不等同差运算。

如照上图嵌套，实际运行时，首先去second表中一个值，又因first.PNO=second.PNO=SPJ.PNO，则取值情况仅有6种(P1,P1,P1)…(P6,P6,P6)，而这6种零件存在有S1供应的零件，对于每一个second.PNO取值，内层NOT EXISTS返回并未全为false，即内层筛选的列表最终并非为空，存在可能，因此外层NOT EXISTS为false，任一second.PNO取值都不满足外层NOT EXISTS，最终结果为空。

回到最开始的问题

select JNO
from J
where not exists
	(
	select PNO
	from P 
	where  not exists
		(
		select *
		from SPJ
		where  PNO=first.PNO and JNO=J.JNO and SNO='S2'
		)
);

为什么返回值为空，因为内层返回列表为非空。
缺少对于零件的筛选，内层对每一个零件都进行了测试，实际仅需测试S2供应的零件即可，如每个零件都测试，不考虑其他，显然内层会有更大的可能为非空。
因此需要添加额外的语句先进行筛选，一番修改后

select JNO
from J
where not exists
	(
	select PNO
	from P 
	where PNO  in (select distinct PNO from SPJ where SNO='S2' )	
	and not exists
		(
		select *
		from SPJ
		where  PNO=P.PNO and JNO=J.JNO and SNO='S2'
		)
);

运行结果如图，经手动验算后可知为正确答案。

该问题并非如课本例题那样找出选修了全部课程的学生，而是用了全部S1供应零件的项目，虽都为全部，但后者的全部是有限制条件的即S1供应，因此应该先筛选出S1供应的零件然后再利用NOT EXISTS去实现全程代词。