选择其他表中不存在的行

Question

我有两个postgresql表：

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

我想获取login_log其中没有一行的每个IP地址ip_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`

我也想知道此查询（进行调整以使其工作）是否是为此目的性能最佳的查询。

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
   );

[LEFT JOIN / IS NULL](https://www.postgresql.org/docs/current/queries-

table-expressions.html#QUERIES-FROM)

有时这是最快的。通常最短。通常会产生与相同的查询计划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](https://www.postgresql.org/docs/current/functions-

subquery.html#FUNCTIONS-SUBQUERY-NOTIN)

只有没有NULL价值或知道如何NULL正确处理才行。我 不会
将其用于此目的。
此外，更大的表可能会降低性能。

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