选择不在其他表中存在的行

Question

我有两个PostgreSQL表：

table name     column names
-----------    ------------------------
login_log      ip | etc.
ip_location    ip | location | hostname | etc.

我想从login_log中获取每个没有在ip_location中有记录的IP地址。
我尝试了这个查询，但它抛出了一个语法错误。

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`

我也在想，如果对此查询进行调整使其正常工作，是否这个查询是实现此目的的最佳性能查询。

- stUrb

Answer 1

这个任务基本上有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
   );

还要考虑：

• 什么是使用 EXISTS 子查询更易读的方法？

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

只有当没有 null 值或者您知道如何正确处理 null 时才适用。对于此目的，我不会使用它。此外，在处理较大表时，性能可能会下降。

SELECT ip 
FROM   login_log
WHERE  ip NOT IN (
   SELECT DISTINCT ip  -- DISTINCT is optional
   FROM   ip_location
   );

NOT IN在两侧有null值时存在一个“陷阱”：

• 查找不存在连接的记录

类似的问题针对MySQL在dba.SE上提出：

• 选择第二列的值不在第一列中出现的行

- Erwin Brandstetter

---

如果两个数据表的数据量都非常大（假设在数十亿级别），哪种 SQL 语句能更快地运行？ - Teja

除了 ALL 以外，其他方法对我来说速度都比较慢。 - Dan Parker

使用LEFT JOIN时要小心 - 如果在查找表中有多个匹配行，则每个匹配行都会在主查询中创建一个重复条目，这可能不是所需的。 - Matthias Fripp

除非使用 WHERE i.ip IS NULL，否则永远不可能发生这种情况，这意味着完全没有匹配。 - Erwin Brandstetter

@erwin-brandstetter: 很好的观点。我自己被绊倒了，考虑到多个正匹配的可能性，但当然这些都将被排除。 - Matthias Fripp

在Snowflake中使用NOT EXISTS选项时，我必须包含*或字段；我不能将SELECT单独留空或完全省略。当省略SELECT或不包含字段时，会出现语法错误：意外的'FROM'。 - undefined

Answer 2

A.) 命令应该是NOT EXISTS，你少了一个'S'。

B.) 使用NOT IN代替。

SELECT ip 
  FROM login_log 
  WHERE ip NOT IN (
    SELECT ip
    FROM ip_location
  )
;

- caleb.breckon

---

在大型数据集上使用NOT IN是一个糟糕的想法，非常非常慢。这是不好的，应该避免使用。 - Grzegorz Grabek

@GrzegorzGrabek 请尝试提供替代方案，而不是仅仅驳回其他人的答案。 - TheRealChx101

@TheRealChx101的评论根据Celeb的建议使用了NOT IN而非NOT EXISTS。正如你可以看到有多少人投票支持此评论，大多数人都能清楚地理解它。 - Grzegorz Grabek

Answer 3

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表中存在的数据（甚至我也在内部给出一些条件，你也可以给出）。指定不应在检索数据中存在的条件应放在括号内。

- Deepak N

Answer 4

这也可以尝试...

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

- Ahnaf

---

“WHERE ip_location.ip is null” - 这个“WHERE”条件怎么可能成立？而且子查询并不是相关子查询。 - Istiaque Ahmed