选择其他表中不存在的行

编程入门行业动态更新时间:2024-10-25 20:20:40

本文介绍了选择其他表中不存在的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我有两个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: