Выберите строки, отсутствующие в другой таблице.

У меня есть две таблицы 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`

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

Ответы

Ответ 1

Есть в основном 4 метода для этой задачи, все они стандартные 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:

Ответ 2

A.) Команда НЕ СУЩЕСТВУЕТ, вам не хватает "S".

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

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

Ответ 3

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

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

Ответ 4

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