Выберите строки, которых нет в другой таблице

У меня есть две таблицы postgresql:

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

Я хочу получить каждый IP-адрес от login_log, у которого нет строки в 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`

Мне также интересно, является ли этот запрос (с настройками, чтобы он работал) наиболее эффективным запросом для этой цели.


postgresql null sql left-join exists
person stUrb    schedule 14.10.2013    source источник

Ответы (4)


arrow_upward
459
arrow_downward

Для этой задачи существует четыре основных метода, все они являются стандартным 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

Иногда это самый быстрый способ. Часто самый короткий. Часто приводит к тому же плану запроса, что и 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

Хорошо только без NULL значений или если вы знаете, что NULL правильно обращаться. Я бы не его использовал для этой цели. Кроме того, производительность может ухудшиться с большими таблицами.

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

NOT IN содержит "ловушку" для NULL значений с обеих сторон:

Аналогичный вопрос о dba.SE ориентирован на MySQL:

person Erwin Brandstetter    schedule 14.10.2013
comment
Какой SQL будет работать быстрее, учитывая большие объемы данных в обеих таблицах. (в миллиардах) - person Teja; 13.09.2016
comment
КРОМЕ ВСЕГО был самым быстрым для меня - person Dan Parker; 02.12.2019
comment
Будьте осторожны с LEFT JOIN - если в таблице поиска есть несколько совпадающих строк, это создаст повторяющуюся запись в вашем основном запросе для каждой совпадающей строки, которая может быть нежелательной. - person Matthias Fripp; 04.06.2020
comment
@MatthiasFripp: За исключением того, что это никогда не может произойти с WHERE i.ip IS NULL, что означает нет совпадений. - person Erwin Brandstetter; 04.06.2020
comment
@ Эрвин-брандстеттер: Хорошее замечание. Я сбился с пути, думая о возможности нескольких положительных матчей, но, конечно, все они будут исключены. - person Matthias Fripp; 09.06.2020

arrow_upward
4
arrow_downward

A.) Команда НЕ СУЩЕСТВУЕТ, вам не хватает буквы «S».

Б.) Вместо этого используйте NOT IN

SELECT ip 
  FROM login_log 
  WHERE ip NOT IN (
    SELECT ip
    FROM ip_location
  )
;
person caleb.breckon    schedule 14.10.2013
comment
НЕ ВХОДИТ в большие наборы данных - ужасная идея. Очень-очень медленно. Это плохо, и этого следует избегать. - person Grzegorz Grabek; 04.09.2018

arrow_upward
2
arrow_downward

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 содержит все данные, а таблица executes1 содержит некоторые данные из таблицы testcases1. Я получаю только те данные, которых нет в таблице exections1. (и даже я даю внутри некоторые условия, которые вы также можете указать.) В скобках должно быть указано условие, которого не должно быть при извлечении данных.

person Deepak N    schedule 04.12.2017

arrow_upward
0
arrow_downward

это тоже можно попробовать ...

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
person Ahnaf    schedule 11.03.2015
comment
WHERE ip_location.ip is null - как WHEREусловие может быть когда-либо истинным? Кроме того, подзапрос не является коррелированным. - person Istiaque Ahmed; 10.11.2017