[解決済み】他のテーブルに存在しない行を選択する

質問

Postgresqlのテーブルを2つ持っています。

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`

また、このクエリ（うまくいくように調整したもの）が、この目的のために最もパフォーマンスの高いクエリであるかどうかも気になるところです。

どのように解決するのですか？

このタスクには基本的に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
   );

も検討してください。

• EXISTSサブクエリでは何が読みやすいか？

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 の値を左右に表示します。

• joinが存在しないレコードを検索

MySQL を対象とした dba.SE での同様の質問。

• 2列目の値が1列目に存在しない行を選択する