能不能用 SQL 查出没有在数据表里出现的值

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 627 天前的主题，其中的信息可能已经有所发展或是发生改变。

一个数据表只有一个 name 列, 这个数据表只有两行, 分别是 A 和 B 。

name
A
B

现在有几个数据，分别是 A,B,C,D ，有没有办法使用 SQL 语句查出 A,B,C,D 这 4 个中哪些没有出现在数据表里？
按我举的这个例子，最后应该输出 C 和 D

SQL

表里

table

name

25 条回复 • 2023-04-10 10:48:39 +08:00

yuann72

2023-04-09 13:15:33 +08:00

我问了 chatGPT ，它完全没理解我的意思😅

可以使用 SQL 中的 NOT IN 子句来解决这个问题。具体地，可以写出如下的 SQL 语句:
SELECT name FROM table_name WHERE name NOT IN ('A', 'B', 'C', 'D');
其中，table_name 是要查询的数据表名，name 是列名，'A', 'B', 'C', 'D' 是要查询的字符串值。这个语句会返回那些在数据表中没有出现的 name 值。

在你举的例子中，执行这个语句会得到以下结果:
C
D
因为 C 和 D 没有出现在这个数据表中。

Ambition95

2023-04-09 13:50:03 +08:00

把 abcd 搞成表 t1 ，假设原表是 t2
select
*
from t1
left anti join t2
on t1.name = t2.name

hahastudio

2023-04-09 13:52:42 +08:00

https://stackoverflow.com/questions/8007821/sql-single-query-to-return-values-that-are-not-present
但是一般会问为什么会要有这样的需求，不能从 SQL 外解决吗

tomczhen

2023-04-09 13:54:38 +08:00 via Android

按集合的概念来描述就应该能理解了。

Biggoldfish

2023-04-09 13:55:42 +08:00 via Android

WHERE NOT IN 就行吧

yjim

2023-04-09 14:15:34 +08:00

你这个需求脱离 SQL 实现方便点，如果一定要用 SQL 来执行的话，了解下存储过程

Hurriance

2023-04-09 14:21:23 +08:00

基于你的「几个数据」建立临时表 t1 ，用 not in 对比原表 t2 ，不过这种实现似乎并不太「优雅」？

<script src="https://gist.github.com/KailokFung/fb75a7a6c1bd8aa75c40ac3e5f58971f.js"></script>

xiangyuecn

2023-04-09 14:33:47 +08:00

牛逼到不行的 union all ，轻松构造多行数据

select name from

(
select 'A' as name
union all select 'B' as name
union all select 'C' as name
union all select 'D' as name
) as List

where name not in(
-- select name from 你的表
(
select 'A' as name
union all select 'B' as name
)
)

akira

2023-04-09 15:04:35 +08:00

SQL 查有不查无。
构建一个临时表放好 ABCD ，然后再来做这个事情就简单了。

yuann72

2023-04-09 15:18:12 +08:00

@Ambition95 #2
@hahastudio #3
@yjim #6
@Hurriance #7
@xiangyuecn #8
有时会需要临时查下一批数据中哪些没有出现在表里。
目前的做法确实是用 SQL 以外的方式实现，但不够方便，就是把表的数据查出来复制到 EXCEL 里进行对比，有些麻烦，数据表里只有几千行还行，要是大几万行就更麻烦。
通过创建临时表的方式也行但也不够方便，建表>插入数据>查询完>删表，而且如果是在生产环境里查，生产环境里也不给建临时表啊

yuann72

2023-04-09 15:18:33 +08:00

SQL 里有没有这种函数：可以传入任意个参数，自动转成一张临时表。
这样就能用这张临时表来 JOIN 或者 NOT IN 查询了

yjim

2023-04-09 15:33:30 +08:00

@yuann72 你这几万行数据指的是有几万个不同的 name ？还是指有几万条 name 数据，但是来来去去就那么几个值？

如果是后者可以用 select distinct(name) from table 去重。

lower

2023-04-09 15:38:57 +08:00

布隆过滤？

yjim

2023-04-09 15:39:19 +08:00

@yuann72 建表>插入数据>查询完>删表你这一套方法用存储过程，不需要建表也可以完成，而且写一次存储过程下次直接在数据库里调用就可以获得检查结果了。缺点是存储过程的写法和坑实在让人蛋疼。好处嘛，一次写完下次直接跑就行了。

akira

2023-04-09 15:40:45 +08:00

生产环境单独加一个库专门用来放这种临时需求的表数据。
而且这种查询的表你可以保留下来，不做删除，方便后续回查。

如果你们有数仓的话，这类查询放数仓就更方便了。

awen233333

2023-04-09 15:54:48 +08:00 via iPhone

@yuann72 表值函数返回 ABCD ，然后用 minus 运算符减去 AB

fackVL

2023-04-09 19:22:00 +08:00 via iPhone

感觉你走两个极端了，人家说另外的方式不是说导出来用 Excel 。可以试试用代码实现啊

takato

2023-04-09 19:49:02 +08:00

@lower 布隆过滤器是概率情况，如果一定要确定值的时候不要使用。

documentzhangx66

2023-04-09 20:19:58 +08:00

临时表 + 游标轻松解决。

给 A 、B 、C 、D 构建临时表：
{
ID int 主键，自增。
ValueToSearch int ，用来存放 A 、B 、C 、D
IsValueInDataTable bool ，该值是否存在于数据表中。
}

对其用游标遍历，然后每个值去数据表中查，查询的结果 Update 到临时表中的 IsValueInDataTable 字段。

很多人应该是一行 SQL 写多了，要写多行 SQL 就不会了。

c6h6benzene

2023-04-09 20:21:19 +08:00 via iPhone

@yuann72 not in 里面的是用逗号分割的项，一般多个值拼一下就好了。

至于有没有这种函数，要看你用的数据库版本，oracle 和 sql server 都有 listagg/string_agg ，没有的话大概也可以拼起来之后再拆开。SQL SERVER 常见招式是 xml path 和 stuff ，然后 split_string

c6h6benzene

2023-04-09 20:23:14 +08:00 via iPhone

或者 select except

CHUB

2023-04-09 23:09:41 +08:00

SELECT unnest(ARRAY['A', 'B', 'C', 'D']) EXCEPT SELECT column_name FROM table_name;

或者更简单的：

SELECT column_name FROM table_name GROUP BY column_name; 再用目测法看看缺了哪个

jhdxr

2023-04-10 06:08:32 +08:00

pgsql 和 mssql 上面都有答案了，我来贴个 mysql 的： https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

（ mysql 8 之前或者 mariadb 用变量+字符串函数也行，就是写起来太太太太麻烦了。。。

hero0earth

2023-04-10 08:33:31 +08:00

可以使用公共表达式（ https://dev.mysql.com/doc/refman/8.0/en/with.html ）构建临时表，再做外连接

arvinsilm

2023-04-10 10:48:39 +08:00

可以干，但属实没必要，数据库不是用来干这事的。不如写个脚本，用时跑一把完事。