T-sql,从键列表中查找不匹配的元素

每个人都有一个正确的想法 ANTI JOIN . 然而,值得注意的是,提出的答案并不总是会产生完全相同的结果,每个解决方案都有不同的性能影响。MatBailie提议的是如何进行反连接,Alexander提议的是如何进行反连接 ANTI SEMI JOIN .

亚历山大在我看来更正确,因为我们正在寻找一个 ANTI SEMI JOIN ; 一 左 反半加入,具体来说,以您的“某处”员工ID列表作为 左边 表和邮件列表作为 正当 桌子

反连接返回中存在的记录 这 中不存在的集合 那个 设置通过集合,我指的是表、视图、子查询等。通过“this”集合,我指的是左表,通过“that”集合,我指的是右表。半连接仅在 一 返回左表中的匹配行。换句话说,半连接返回 不同的 设置

使用提供的样本数据。比如说,通过“某处”,你指的是一张桌子。(我将数字5包括两次,以演示和反连接与反半连接之间的区别)

CREATE TABLE dbo.somewhere (employeeId int);
INSERT dbo.somewhere VALUES (1),(2),(3),(4),(5),(5);

您可以使用 NOT IN 或 NOT EXISTS

-- ANTI JOIN USING NOT IN
SELECT somewhere.EmployeeId--, <other columns>
FROM dbo.somewhere
WHERE somewhere.EmployeeId NOT IN (SELECT EmployeeId FROM dbo.MailingList); -- EXLCLUDE IDs NOT IN MailingList

-- ANTI JOIN USING NOT EXISTS
SELECT somewhere.EmployeeId--, <other columns>
FROM dbo.somewhere
WHERE NOT EXISTS 
(
  SELECT EmployeeId 
  FROM dbo.MailingList ML
  WHERE ML.EmployeeId = somewhere.employeeId
);

请注意,每个函数都会返回数字5两次。如果您只需要它一次,您可以使用它来执行类似的反半连接:

SELECT somewhere.EmployeeId
FROM dbo.somewhere
EXCEPT -- SET OPERATOR (SET OPERATORS INCLUDE: UNION, UNION ALL, EXCEPT, INTERSECT)
SELECT EmployeeId 
FROM dbo.MailingList; -- EXLCLUDE IDs NOT IN MailingList

除了是一个 Set Operator 喜欢 UNION 和 INTERSECT . 集合运算符返回唯一的结果集。(唯一的例外是联合所有人)。如果希望使用NOT IN或NOT EXISTS获得唯一的结果集,则还需要包含DISTINCT或GROUP BY所有要唯一的列。

如果“某处”指的是逗号分隔的列表或XML或JSON文件/片段,那么首先需要将该列表、XML、JSON或任何内容转换到左边的表中。使用SQL Server 2016 string_split (或另一个“拆分器”功能)您可以执行以下操作:

-- "somewhere" is a csv, list or array
DECLARE @somewhere varchar(1000) = '1,2,3,4,5';

-- ANTI JOIN WITH NOT IN
SELECT EmployeeId = [value]
FROM string_split(@somewhere, ',')
WHERE [value] NOT IN (SELECT EmployeeId FROM dbo.MailingList);

-- ANTI SEMI JOIN WITH NOT IN
SELECT DISTINCT EmployeeId = [value]
FROM string_split(@somewhere, ',')
WHERE [value] NOT IN (SELECT EmployeeId FROM dbo.MailingList);

-- ANTI SEMI JOIN WITH EXCEPT
SELECT EmployeeId = [value]
FROM string_split(@somewhere, ',')
EXCEPT 
SELECT EmployeeId FROM dbo.MailingList;
GO

.. 或者,如果是XML,则有一个选项如下所示:

-- "somewhere" is XML
DECLARE @somewhere XML =
'<employees>
 <employee>1</employee>
 <employee>2</employee>
 <employee>3</employee>
 <employee>4</employee>
 <employee>5</employee>
 </employees>'

-- ANTI SEMI JOIN using EXCEPT    
SELECT employeeId = emp.id.value('.', 'int')
FROM (VALUES (@somewhere)) s(empid)
CROSS APPLY empid.nodes('/employees/employee') emp(id)
EXCEPT 
SELECT employeeId 
FROM dbo.MailingList;

最后一点您需要在邮件列表表中建立EmployeeId索引。在我的示例中,您需要dbo上的索引。也在某处。如果您正在进行半连接,那么您希望这些索引是唯一的。

您可以使用 EXCEPT 命令 例子:

SELECT *
FROM 
(
    SELECT 1 AS Id
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
) AS t
EXCEPT
SELECT Id FROM MailingList

你似乎并不是在问逻辑,而是在问如何“最好地”表现集合 {1,2,3,4,5} .

正如你提到的,一个答案是一个临时表。

另一个是子查询或具有一系列 UNION ALL 声明。

另一种是使用 VALUES (1), (2), (3), (4), (5) 在CTE或子查询中。

但这里有一个突出的点。如果你有一张桌子 EmployeeID 字段,然后 当然 你有一个 Employee 桌子在这种情况下,你应该能够从那里“衍生”你的5名员工?

(SELECT id FROM employee WHERE manager_id = 666)

or...

(SELECT id FROM employee WHERE staff_ref IN ('111', '222', '333', '444', '555'))

etc, etc...

编辑:

至于实际逻辑,一旦你的集合代表了你的5名员工,你可以使用 LEFT JOIN 和 IS NULL ...

SELECT
    Employee.*
FROM
    Employee
LEFT JOIN
    MailingList
        ON  MailingList.list_id     = 789
        AND MailingList.employee_id = Employee.id
WHERE
    Employee.manager_id = 666
    AND MailingList.employee_id IS NULL

=>经理为#666但不在邮寄名单上的员工为#789