选择其他表中不存在的行

I've got two postgresql tables:

我有两个postgresql表:

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

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.

我想从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`

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

我还想知道这个查询(通过调整使其工作)是否是这个目的的最佳执行查询。

4 个解决方案

#1

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

这个任务基本上有4种技术，都是标准SQL。

NOT EXISTS

Often, this is fastest in Postgres.

通常，这在Postgres中是最快的。

SELECT ip 
FROM   login_log l 
WHERE  NOT EXISTS (
   SELECT 1              -- it's mostly irrelevant what you put here
   FROM   ip_location i
   WHERE  l.ip = i.ip
   );

Also consider:

还要考虑:

• What is easier to read in EXISTS subqueries?
• 在现有的子查询中，什么更容易阅读?

LEFT JOIN / IS NULL

Sometimes this is fastest. Often shortest.

有时这是最快的。通常最短。

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

Short. Not as easily integrated in more complex queries.

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

SELECT ip 
FROM   login_log

EXCEPT ALL               -- ALL, to keep duplicate rows and make it faster
SELECT ip
FROM   ip_location;

Note that (per documentation):

请注意,(每个文档):

duplicates are eliminated unless EXCEPT ALL is used.

除非全部使用，否则重复的部分将被删除。

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

通常，您需要所有关键字。如果您不介意，仍然使用它，因为它使查询速度更快。

NOT IN

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 carries a "trap" for NULL values on either side:

在任何一方都不存在空值的“陷阱”:

• Find records where join doesn't exist
• 查找连接不存在的记录

Similar question on dba.SE targeted at MySQL:

类似的问题在dba。SE针对MySQL:

• Select rows where value of second column is not present in first column
• 选择第一行中不存在第二列值的行

#2

A.) The command is NOT EXISTS, you're missing the 'S'.

a .)该命令不存在，您正在丢失'S'。

B.) Use NOT IN instead

b .)使用不

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

#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 t中选择*(从executions1 i中选择1，其中t为t)。tc_id =我。tc_id t.pro_id =我。pro_id和pro_id=7, version_id=5)和pro_id=7;

Here testcases1 table contains all datas and executions1 table contains some data among testcases1 table. I am retrieving only the datas which are not present in exections1 table. ( and even I am giving some conditions inside that you can also give.) specify condition which should not be there in retrieving data should be inside brackets.

在这里，testcases1表包含所有的数据，而executions1表包含testcases1表中的一些数据。我只检索在exections1表中不存在的数据。(甚至我也给出了一些内部条件，您也可以给出)指定在获取数据时不应该存在的条件应该在括号内。

#4

this can also be tried...

这也可以尝试……

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