了解关系数据库的人应该都知道4种关系操作符,加、减、乘、除。具体到SQL语句中,前3种分别是union、except、笛卡尔积,但是除法没有具体的关键字,需要我们自己写SQL语句来实现,这就需要用到not exists了。
举个比较俗的例子,一个成绩表,3个字段 sid、cid,grade,分别表示学生ID,课程ID,课程成绩,表的数据如下:
1 | 101 | 67 |
1 | 102 | 78 |
1 | 103 | 68 |
2 | 101 | 89 |
2 | 102 | 68 |
3 | 102 | 78 |
3 | 103 | 90 |
查询学生ID,这个学生选修了sid为2的学生选修的所有课程。
这里我先不用SQL语句实现,而是用存储过程来实现:
create procedure findall
as
begin
declare @i int
declare @j int
declare @id2count int
declare @sidcount int
declare @sidtable table(id int identity(1,1),sid int)
declare @id2coursetable table(id int identity(1,1),cid int)
declare @result int
declare @sidtmp int
declare @cidtmp int
select @sidcount = count(sid) from grade where sid <> 2 group by sid
select @id2count = count(*) from grade where sid=2
insert into @sidtable(sid) select sid from grade where sid <> 2 group by sid
insert into @id2coursetable(cid) select cid from grade where sid =2
set @i=1
set @j=1
set @result=1
while @i < @sidcount+1
begin
select @sidtmp = sid from @sidtable where id=@i
while @j<@id2count+1
begin
select @cidtmp = cid from @id2coursetable where id=@j
select @result=count(*) from grade where sid = @sidtmp and cid = @cidtmp
if(@result = 0)
begin
break
end
set @j=@j+1
end
if(@result = 1)
begin
print @sidtmp
end
set @i=@i+1
end
end
as
begin
declare @i int
declare @j int
declare @id2count int
declare @sidcount int
declare @sidtable table(id int identity(1,1),sid int)
declare @id2coursetable table(id int identity(1,1),cid int)
declare @result int
declare @sidtmp int
declare @cidtmp int
select @sidcount = count(sid) from grade where sid <> 2 group by sid
select @id2count = count(*) from grade where sid=2
insert into @sidtable(sid) select sid from grade where sid <> 2 group by sid
insert into @id2coursetable(cid) select cid from grade where sid =2
set @i=1
set @j=1
set @result=1
while @i < @sidcount+1
begin
select @sidtmp = sid from @sidtable where id=@i
while @j<@id2count+1
begin
select @cidtmp = cid from @id2coursetable where id=@j
select @result=count(*) from grade where sid = @sidtmp and cid = @cidtmp
if(@result = 0)
begin
break
end
set @j=@j+1
end
if(@result = 1)
begin
print @sidtmp
end
set @i=@i+1
end
end
这个存储过程可以查找符合条件的学生,下面使用SQL语句实现相同的功能
select sid from grade as grade1 where sid <> 2 and not exists (select * from grade as grade2 where grade2.sid =2 and not exists (select * from grade as grade3 where grade3.sid = grade1.sid and grade3.cid = grade2.cid) ) group by sid
通过比较发现,SQL语句确实很强大啊,短短的几个语句就实现了复杂的嵌套循环。
这里的嵌套循环主要是通过两个子查询来实现的,SQL语句中的两个圆括号就相当于两层嵌套循环,最里面的圆括号相当于最里层的嵌套循环,外面的圆括号和外层的嵌套循环层层对应,not exists分别对用两层循环中的if语句。