选择不在另一个表中的行，SQL Server查询

Question

主题表

CREATE TABLE [dbo].[BS_Subject](
     [SubjectID] [bigint] IDENTITY(1,1) NOT NULL,
     [DepartmentID] [bigint] NOT NULL,
     [SubjectName] [varchar](50) NOT NULL,
     [SubjectDescription] [varchar](100) NULL,
     [SubjectShortCode] [varchar](10) NOT NULL,
CONSTRAINT [PK_Subject] PRIMARY KEY CLUSTERED 
(
    [SubjectID] ASC
)

SubjectToClass表

CREATE TABLE [dbo].[BS_SubjectToClass](
[SubjectToClassID] [bigint] IDENTITY(1,1) NOT NULL,
[SubjectID] [bigint] NOT NULL,
[ClassID] [bigint] NOT NULL,
CONSTRAINT [PK_BS_SubjectToClass] PRIMARY KEY CLUSTERED 
(
[SubjectToClassID] ASC
)

我需要列出所有在Subject表中，不在指定班级的SubjectToClass表中的subjectid行。

我已经有了下面这段代码，但是无法进一步处理。

select Distinct(BS_Subject.SubjectID) DepartmentID, 
       SubjectName, SubjectDescription,    SubjectShortCode
from dbo.BS_Subject
where BS_Subject.SubjectID <> (
SELECT     Distinct(BS_Subject.SubjectID)
FROM         dbo.BS_Subject, dbo.BS_SubjectToClass
Where      BS_Subject.SubjectID = BS_SubjectToClass.SubjectID
And BS_SubjectToClass.ClassID = 2)

- Taufiq Abdur Rahman

---

可能需要重新措辞和重新格式化以使其有些意义。 - Preet Sangha

Answer 1

SELECT SubjectID, DepartmentID, SubjectName, SubjectDescription, SubjectShortCode
FROM BS_Subject 
WHERE NOT EXISTS 
(SELECT SubjectToClassID FROM BS_SubjectToClass WHERE 
BS_Subject.SubjectID = BS_SubjectToClass.SubjectID
AND BS_SubjectToClass.ClassID =2)

- shahkalpesh

---

@tcables 这个在SSMS或Visual Studio里为什么不行呢？我在SSMS 2008和Visual Studio 2012里完美执行。 - Chris Schiffhauer

SQL Studio 不喜欢 WHERE NOT EXISTS 后面的语句。可能只是我的设置问题，但当我在下面的答案中替换列名时，它就可以工作了。 - tcables

Answer 2

你需要使用NOT IN运算符 - 而不是<>（那是VB或其他语言的运算符）。

SELECT 
   DISTINCT(BS_Subject.SubjectID) DepartmentID, 
   SubjectName, SubjectDescription, SubjectShortCode
FROM dbo.BS_Subject
WHERE 
    BS_Subject.SubjectID NOT IN
       (SELECT DISTINCT(BS_Subject.SubjectID)
        FROM dbo.BS_Subject, dbo.BS_SubjectToClass
        WHERE BS_Subject.SubjectID = BS_SubjectToClass.SubjectID
        AND BS_SubjectToClass.ClassID = 2)

- marc_s

---

有趣的事实是，在MS SQL中，您可以使用<>以及!=（尽管这仍然不同于NOT IN）。 - Hannele