SQL中哪些值在列表中缺失？

Question

我有一个值列表，比如说1,2,3,4,5,6。我有一个 SQL 表格，其中一个字段存储了这些值，但仅有三行，即2,4,5。什么样的查询语句会为我返回三行1,3,6？（把所有六个值存储在另一个表格中并进行左连接是欺骗行为。）

- chx

---

将所有值存储在查找表中，然后进行连接是迄今为止最好的方法。 - HLGEM

请提供您所使用的数据库类型、表结构以及返回三行数据的SQL查询语句。 - John D

你的意思是像反连接或者NOT IN()子句这样吗？ - Jeremy Holovacs

适合我的答案在这里：https://dev59.com/JWkw5IYBdhLWcg3wQ4dc - knocte

Answer 1

您没有说明您使用的数据库管理系统，因此这是ANSI SQL版本：

with the_values (id) as ( 
  values (1),(2),(3),(4),(5),(6)
)
select v.id 
from the_values v
  left join the_real_table t on t.id = v.id
where t.id is null;

- user330315

---

那个CTE是ANSI标准语法？太神奇了。 - Jeremy Holovacs

@samyi：实际上它说“*将其存储在临时表中并左连接”是作弊。但我没有将它们存储在临时表中，所以布尔表达式仍然为真 ;) - user330315

语法在SQL Server 2008中不起作用，但有类似的东西。 - Gordon Linoff

我有些犹豫不决，不知道该接受哪个答案——这是 ANSI 标准，但在 MySQL 中无法使用，在 SQL Server 上要使其工作，需要使用 SELECT - UNION 结构，如果您将其写出来，那么更容易在该结构上使用 JOIN 而不使用 WITH。听起来 WITH 是 SQL'99 的标准。https://dev59.com/U3RC5IYBdhLWcg3wW_pk - chx

完美无缺！ - Gaurav

Answer 2

你可以尝试使用 EXCEPT（类似于 Oracle 中的 MINUS）：

(SELECT 1
UNION
SELECT 2
UNION 
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6)
EXCEPT
(SELECT 2
 UNION
 SELECT 3
 UNION
 SELECT 4)

或者，更符合您的例子：

或者，更与您的示例相关：

(SELECT 1
UNION
SELECT 2
UNION 
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6)
EXCEPT
(SELECT Field FROM Table)        

其中字段包含2、4和5。

- TelJanini

---

这是一个很好的想法。这里有一个小修改，即使在MySQL中也可以工作：SELECT missing.1 AS missing FROM test RIGHT JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) x ON x.1 = test.foo WHERE foo IS NULL; - chx

不错的解决方案。对于支持values行构造函数的数据库，可以缩短为values (1),(2),(3),(4),(5),(6) EXCEPT SELECT col FROM foo。 - user330315

你提到我的查询在Oracle/DB2/Firebird上无法工作--那么 SELECT missing.1 AS missing FROM test RIGHT JOIN (values (1),(2),(3),(4),(5),(6))... 在那里能行吗？ - chx

@chx 在MySQL中无法工作，它会给出Unknown column 'missing.1' in 'field list'的错误提示。 - knocte

这适用于MySQL：https://dev59.com/questions/JWkw5IYBdhLWcg3wQ4dc - knocte

Answer 3

not in 运算符将执行您想要的检查。

declare @allValues table (value int)
insert @allValues values (1)
insert @allValues values (2)
insert @allValues values (3)
insert @allValues values (4)
insert @allValues values (5)
insert @allValues values (6)

declare @someValues table (value int)
insert @someValues values (2)
insert @someValues values (4)
insert @someValues values (5)

select
    *
from
    @allValues
where
    value not in (select value from @someValues)

另一种可能更快的方法是使用连接：

select
    av.value
from
    @allValues av
    left join @someValues sv on (av.value = sv.value)
where
    sv.value is null

- tenfour

Answer 4

使用Mysql 8，您可以使用JSON_TABLE并从JSON值数组中选择表

SELECT *
      FROM
     JSON_TABLE(
       '[1,2,3,4,5,6]',
       '$[*]' COLUMNS( id INT PATH '$' ERROR ON ERROR )
     ) as v
 left join the_real_table t on t.id = v.id
where t.id is null;

- Adam Mátl

Answer 5

如果它完成了工作，那么就不算作弊。设置一个临时表或包含所有可能行的表变量，然后使用WHERE NOT EXISTS或LEFT OUTER JOIN与WHERE TableVariableID IS NULL相结合。

- KeithS

---

它不能完成任务的简单原因是我没有权限创建临时表...我只有对一个非常大的表的基本选择访问权限和一些外部ID的任意列表，我需要找出哪些ID在该表中没有任何条目。（该列表来自完全外部的来源--如果它们不存在于该表中，则它们根本不存在于数据库中。）最终我选择了存在的那些，并使用Excel和繁琐的手动过程。 - user3067860

Answer 6

你可以使用一个 NOT IN 子句。

SELECT column FROM table WHERE column NOT IN (2,4,5)

- Marcus Recck