提问人：stUrb 提问时间：10/14/2013 最后编辑：Radek PostołowiczstUrb 更新时间：2/15/2023 访问量：276274
选择其他表中不存在的行
Select rows which are not present in other table
问：
我有两个postgresql表：
table name column names
----------- ------------------------
login_log ip | etc.
ip_location ip | location | hostname | etc.
我想从中获取每个没有行的 IP 地址。
我尝试了这个查询，但它抛出了一个语法错误。login_logip_location
SELECT login_log.ip
FROM login_log
WHERE NOT EXIST (SELECT ip_location.ip
FROM ip_location
WHERE login_log.ip = ip_location.ip)
ERROR: syntax error at or near "SELECT" LINE 3: WHERE NOT EXIST (SELECT ip_location.ip`
我还想知道这个查询（经过调整以使其工作）是否是用于此目的的最佳查询。
答：
10赞
caleb.breckon
10/14/2013
#1
A.)该命令不存在，您缺少“S”。
B.)请改用 NOT IN
SELECT ip
FROM login_log
WHERE ip NOT IN (
SELECT ip
FROM ip_location
)
;
评论
11赞
Grzegorz Grabek
9/5/2018
在大型数据集上不加入是一个糟糕的主意。非常非常慢。这很糟糕，应该避免。
4赞
TheRealChx101
9/11/2021
@GrzegorzGrabek 尝试提供替代方案，而不仅仅是驳回其他人的答案
1赞
Grzegorz Grabek
9/15/2021
@TheRealChx101评论是根据 Celeb 的建议编写的，即使用 NOT IN 而不是 NOT EXISTS。它（评论）对于大多数人来说非常清楚，因为你可以看到这个评论有多少人投票有用。
593赞
Erwin Brandstetter
10/15/2013
#2
此任务基本上有 4 种技术，它们都是标准 SQL。
NOT EXISTS
在 Postgres 中通常最快。
SELECT ip
FROM login_log l
WHERE NOT EXISTS (
SELECT -- SELECT list mostly irrelevant; can just be empty in Postgres
FROM ip_location
WHERE ip = l.ip
);
还要考虑：
LEFT JOIN / IS NULL
有时这是最快的。通常最短。通常生成与 相同的查询计划。NOT EXISTS
SELECT l.ip
FROM login_log l
LEFT JOIN ip_location i USING (ip) -- short for: ON i.ip = l.ip
WHERE i.ip IS NULL;
EXCEPT
短。不容易集成到更复杂的查询中。
SELECT ip
FROM login_log
EXCEPT ALL -- "ALL" keeps duplicates and makes it faster
SELECT ip
FROM ip_location;
请注意（根据文档）：
除非使用，否则将消除重复项。
EXCEPT ALL
通常，您需要关键字。如果您不在乎，请仍然使用它，因为它使查询速度更快。ALL
NOT IN
只有没有价值观或你知道正确处理的好。我不会将它用于此目的。此外，使用更大的表时，性能可能会下降。nullnull
SELECT ip
FROM login_log
WHERE ip NOT IN (
SELECT DISTINCT ip -- DISTINCT is optional
FROM ip_location
);
NOT IN两边的值都带有一个“陷阱”：null
针对MySQL的 dba.SE 类似问题：
评论
4赞
Teja
9/14/2016
考虑到两个表中的数据量都很高，哪个 SQL 会运行得更快。（ 假设 以十亿计 ）
1赞
Dan Parker
12/3/2019
除了对我来说是最快的
2赞
Matthias Fripp
6/4/2020
请注意 — 如果查找表中有多个匹配行，这将在主查询中为每个匹配行创建一个重复的条目，这可能是不需要的。LEFT JOIN
3赞
Erwin Brandstetter
6/4/2020
@MatthiasFripp：除了这永远不会发生，这意味着根本没有匹配。WHERE i.ip IS NULL
2赞
Matthias Fripp
6/9/2020
@erwin-brandtetter：说得好。我绊倒了自己，想着多次积极匹配的可能性，但当然这些都会被排除在外。
1赞
Ahnaf
3/12/2015
#3
这也可以尝试...
SELECT l.ip, tbl2.ip as ip2, tbl2.hostname
FROM login_log l
LEFT JOIN (SELECT ip_location.ip, ip_location.hostname
FROM ip_location
WHERE ip_location.ip is null)tbl2
评论
4赞
Istiaque Ahmed
11/10/2017
WHERE ip_location.ip is null- 条件怎么可能是真的？此外，子查询不是相关的。WHERE
3赞
Deepak N
12/4/2017
#4
SELECT *
FROM testcases1 t
WHERE NOT EXISTS (
SELECT 1
FROM executions1 i
WHERE t.tc_id = i.tc_id and t.pro_id=i.pro_id and pro_id=7 and version_id=5
) and pro_id=7 ;
这里的 testcases1 表包含所有数据，executions1 表包含 testcases1 表中的一些数据。我只检索 exections1 表中不存在的数据。（甚至我在里面给出了一些条件，你也可以给出。指定检索数据时不应存在的条件应放在括号内。
评论