我有两个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:
- 选择第一列中第二列的值不存在的行
更多推荐
选择其他表中不存在的行
发布评论