UNION ALL 和 NOT IN在一起

2021-09-14 00:00:00 sql union sql-server-2008 sql-server

SQL Server - 我有 3 个简单的表(Fname、Lname 和 Exceptions)，每一列都称为 Name.我希望我的最终结果看起来像:(Fname 中的每个人 + LName 中的每个人)-(例外中的每个人).

SQL Server - I have 3 simple tables (Fname, Lname and Exceptions) with one column each called Name. I want my end result to look like: (Everybody in Fname + Everybody in LName) - (Everybody in Exceptions).

名称:

Name
A
B

L 名称:

Name
Y
Z

例外:

Name
A
Z

预期查询结果集:

B
Y

当前 SQL 查询:

Select Name from Fname
UNION ALL
Select Name from Lname
WHERE Name NOT IN
(Select Name from Exceptions)

SQL 查询仅适用于删除出现在 LName 中但不在 Fname 中的数据.有人可以帮忙吗.

The SQL query only works on removing data which appears in LName but not in Fname. Can somebody please help.

推荐答案

UNION 的各个部分作为单独的查询处理，因此您可以将它们分组在子查询中:

The parts of a UNION are handled as separate queries, so you can group them in a subquery:

SELECT Name 
FROM (Select Name from Fname
      UNION ALL
      Select Name from Lname)sub
WHERE Name NOT IN (Select Name from Exceptions)

如果您不关心重复，您可以将其保留为 UNION ALL.

You can keep that as UNION ALL if you don't care about duplicates.

相关文章