Sélectionner les lignes qui ne sont pas présentes dans d'autres tableaux

Question

J'ai deux tables postgresql :

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

Je veux obtenir toutes les adresses IP de login_log qui n'a pas de ligne dans ip_location .
J'ai essayé cette requête mais elle génère une erreur de syntaxe.

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`

Je me demande également si cette requête (avec des ajustements pour la faire fonctionner) est la plus performante à cette fin.

Answer 1

Il existe essentiellement 4 techniques pour cette tâche, toutes des techniques SQL standard.

NOT EXISTS

Souvent le plus rapide dans 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
   );

A considérer également :

• Qu'est-ce qui est le plus facile à lire dans les sous-requêtes EXISTS ?

LEFT JOIN / IS NULL

C'est parfois le plus rapide. Souvent la plus courte. aboutit souvent au même plan d'interrogation que 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

Court. Ne s'intègre pas aussi facilement dans des requêtes plus complexes.

SELECT ip 
FROM   login_log

EXCEPT ALL  -- "ALL" keeps duplicates and makes it faster
SELECT ip
FROM   ip_location;

Il convient de noter que ( par documentation ):

les doublons sont éliminés, sauf si EXCEPT ALL est utilisé.

En règle générale, vous souhaitez que le ALL mot-clé. Si vous ne vous en souciez pas, utilisez-le quand même parce qu'il rend la requête plus facile à comprendre. plus rapide .

NOT IN

Seulement bon sans null ou si vous savez manipuler les valeurs null correctement. Je voudrais pas l'utiliser à cette fin. En outre, les performances peuvent se dégrader avec des tables plus grandes.

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

NOT IN comporte un "piège" pour null de part et d'autre :

• Trouver des enregistrements où la jointure n'existe pas

Question similaire sur dba.SE pour MySQL :

• Sélectionner les lignes où la valeur de la deuxième colonne n'est pas présente dans la première colonne

Answer 2

A.) La commande n'existe pas, il manque le "S".

B.) Utiliser NOT IN à la place

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

Answer 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 ;

Ici, le tableau testcases1 contient toutes les données et le tableau executions1 contient certaines données du tableau testcases1. Je ne récupère que les données qui ne sont pas présentes dans la table exections1. Je ne récupère que les données qui ne sont pas présentes dans la table exections1 (et je donne même quelques conditions à l'intérieur que vous pouvez également donner).

Answer 4

Ceci peut également être essayé...

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