选择其他表中不存在的行 [英] Select rows which are not present in other table

问题描述

我有两个postgresql表:

I've got two postgresql tables:

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

我想从login_log获取每个IP地址，而ip_location中没有一行.
我尝试了此查询，但引发了语法错误.

I want to get every IP address from login_log which doesn't have a row in ip_location.
I tried this query but it throws a syntax error.

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`

我还想知道此查询(经过调整以使其工作)是否是为此目的效果最好的查询.

I'm also wondering if this query (with adjustments to make it work) is the best performing query for this purpose.

推荐答案

此任务基本上有4种技术，全部都是标准SQL.

There are basically 4 techniques for this task, all of them standard SQL.

在Postgres中通常最快.

Often fastest in 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子查询中更容易阅读的是什么?

有时这是最快的.通常最短.通常会导致与NOT EXISTS相同的查询计划.

Sometimes this is fastest. Often shortest. Often results in the same query plan as 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

简短.不太容易集成到更复杂的查询中.

EXCEPT

Short. Not as easily integrated in more complex queries.

SELECT ip 
FROM   login_log

EXCEPT ALL  -- "ALL" keeps duplicates and makes it faster
SELECT ip
FROM   ip_location;

请注意(每个文档):

除非使用EXCEPT ALL，否则将删除

重复项.

duplicates are eliminated unless EXCEPT ALL is used.

通常，您需要ALL关键字.如果您不在乎，请继续使用它，因为它会使查询更快 .

Typically, you'll want the ALL keyword. If you don't care, still use it because it makes the query faster.

仅当没有NULL值时使用，或者您知道正确处理NULL时，才是好.我不将其用于此目的.较大的表可能会降低性能.

Only good without NULL values or if you know to handle NULL properly. I would not use it for this purpose. Performance can deteriorate with bigger tables.

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

NOT IN在任一侧带有一个NULL值的陷阱":

NOT IN carries a "trap" for NULL values on either side:

• 查找不存在联接的记录

针对dba.SE的类似问题针对MySQL:

Similar question on dba.SE targeted at MySQL:

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

这篇关于选择其他表中不存在的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！