筛选包含这些信息的记录(数据库原理中的 除法)
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
--学生选课表
CREATE TABLE SC(
S# CHAR(2),--学生号
C# CHAR(2)--课程号
)
GO
INSERT INTO SC VALUES('S1', 'C1')
GO
INSERT INTO SC VALUES('S1', 'C2')
GO
INSERT INTO SC VALUES('S2', 'C1')
GO
INSERT INTO SC VALUES('S2', 'C3')
GO
INSERT INTO SC VALUES('S3', 'C1')
GO
INSERT INTO SC VALUES('S3', 'C2')
GO
INSERT INTO SC VALUES('S4', 'C1')
GO
INSERT INTO SC VALUES('S4', 'C2')
GO
INSERT INTO SC VALUES('S4', 'C3')
GO
--查询出 包含了S1同学所选课程的学生号
SELECT DISTINCT S#
FROM SC
WHERE S# NOT IN (SELECT t1.S#
FROM (SELECT S#
FROM SC) AS t1,
(SELECT C#
FROM SC
WHERE S# = 'S1') AS t2
WHERE NOT EXISTS(SELECT *
FROM SC AS a
WHERE a.S# = t1.S#
AND a.C# = t2.C#))
GO
DROP TABLE SC
GO
查询结果:S1,S3,S4