sql - tablas - Seleccionar filas que no están presentes en otra tabla

restricciones base de datos sql

Question

Tengo dos tablas postgresql:

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

Quiero obtener cada dirección IP de login_log que no tiene una fila en ip_location .
Intenté esta consulta, pero arroja un error de sintaxis.

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`

También me pregunto si esta consulta (con ajustes para que funcione) es la consulta que mejor funciona para este propósito.

Answer 1

A.) El comando NO ES EXISTE, te falta la ''S''.

B.) Use NOT IN en su lugar

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

Answer 2

Básicamente hay 4 técnicas para esta tarea, todas ellas SQL estándar.

NOT EXISTS

A menudo, este es el más rápido en 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
   );

Considera también:

• ¿Qué es más fácil de leer en las subconsultas EXISTS?

LEFT JOIN / IS NULL

Algunas veces esto es más rápido. A menudo más corto.

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

Corto. No se integra tan fácilmente en consultas más complejas.

SELECT ip 
FROM   login_log

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

Tenga en cuenta que ( EXCEPT ):

los duplicados se eliminan a menos que se use EXCEPT ALL .

Por lo general, querrás la palabra clave ALL . Si no te importa, utilízalo porque agiliza la consulta.

NOT IN

¡Lo único bueno sin valores NULL o si sabe manejar NULL correctamente! No lo usaría para este propósito. El rendimiento puede deteriorarse con tablas más grandes.

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

NOT IN lleva una "trampa" para valores NULL en cualquier lado:

• Encuentra registros donde una unión no existe

Pregunta similar en dba.SE dirigida a MySQL:

• Seleccionar filas donde el valor de la segunda columna no está presente en la primera columna

Answer 3

esto también se puede intentar ...

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

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

Aquí la tabla testcases1 contiene todos los datos y la tabla de executions1 contiene algunos datos entre la tabla testcases1. Estoy recuperando solo los datos que no están presentes en la tabla exections1. (e incluso estoy dando algunas condiciones en el interior que también puedes dar.) especifique la condición que no debería estar allí para recuperar datos debe estar entre corchetes.