¿Cómo seleccionar filas sin entrada coincidente en otra tabla?

Question

¿Cómo seleccionar filas sin entrada coincidente en otra tabla?

Estoy haciendo algunos trabajos de mantenimiento en una aplicación de base de datos y he descubierto que, alegría de alegrías, a pesar de que los valores de una tabla se están utilizando en el estilo de claves foráneas, no hay restricciones de clave foránea en las tablas.

Estoy tratando de agregar restricciones FK en estas columnas, pero estoy encontrando que, porque ya hay toda una carga de datos malos en las tablas de errores anteriores que han sido corregidos ingenuamente, necesito encontrar las filas que no coinciden con la otra tabla y luego eliminarlos.

He encontrado algunos ejemplos de este tipo de consulta en la web, pero todos parecen proporcionar ejemplos en lugar de explicaciones, y no entiendo por qué funcionan.

¿Puede alguien explicarme cómo construir una consulta que devuelve todas las filas sin coincidencias en otra tabla, y qué está haciendo, para que pueda hacer estas consultas yo mismo, en lugar de correr para cada tabla en este mess que no tiene restricciones FK?

217

sql foreign-keys

Author: Kara, 2010-11-02

Source

7 answers

Yo usaría EXISTS expresión dado que es más potente, puede, por ejemplo, elegir con más precisión las filas que desea unir, en el caso de LEFT JOIN tiene que tomar todo lo que está en la tabla unida. Su eficiencia es probablemente la misma que en el caso de LEFT JOIN con prueba nula.

SELECT t1.ID
FROM Table1 t1
WHERE NOT EXISTS (SELECT t2.ID FROM Table2 t2 WHERE t1.ID = t2.ID)

33

Author: Ondrej Bozek,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-04-18 12:48:18

SELECT id FROM table1 WHERE foreign_key_id_column NOT IN (SELECT id FROM table2)

La tabla 1 tiene una columna a la que desea agregar la restricción de clave externa, pero los valores en la columna foreign_key_id_column no coinciden todos con un id en la tabla 2.

La selección inicial enumera los id de la tabla1. estas serán las filas que queremos eliminar.
La cláusula 'not in' en la instrucción where limita la consulta a solo filas donde el valor en la foreign_key_id_column no está en la lista de identificadores de la tabla 2.
La instrucción select entre paréntesis obtendrá una lista de todos los ID que están en la tabla 2.

7

Author: Theo Voss,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-05-31 17:36:39

Donde T2 es la tabla a la que está agregando la restricción:

SELECT *
FROM T2
WHERE constrain_field NOT
IN (
    SELECT DISTINCT t.constrain_field
    FROM T2 
    INNER JOIN T1 t
    USING ( constrain_field )
)

Y eliminar los resultados.

6

Author: Karel,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-04-16 18:41:44

Vamos a tener las siguientes 2 tablas (salario y empleado)

Ahora quiero esos registros de la tabla de empleados que no están en el salario. Podemos hacer esto de 3 maneras-

Usando inner Join

Seleccione * del empleado donde no está el id (seleccione e.id de empleado e interno unirse salario s en e.id=s.id)

Usando la unión externa izquierda

Seleccione * de empleado e izquierda ingreso externo e.id=s.id donde s.id es null

Usando Full Join

Seleccione * del empleado e salario externo completo s on e.id=s.id donde e.id no en (seleccionar id de salario)

1

Author: Debendra Dash,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-01-14 15:32:55

No Sabía Cuál Está Optimizado (en comparación con @AdaTheDev ) pero este parece ser más rápido cuando uso (al menos para mí)

SELECT id FROM table_1 EXCEPT SELECT DISTINCT (table1_id) table1_id FROM table_2

Si desea obtener cualquier otro atributo específico, puede usar:

SELECT COUNT(*) FROM table_1 where id in (SELECT id FROM table_1 EXCEPT SELECT DISTINCT (table1_id) table1_id FROM table_2);

0

Author: Jerin K. John,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-01-25 06:59:38

¿Cómo seleccionar filas sin entrada coincidente en ambas tablas?


    select * from [dbo].[EmppDetails] e
     right join [Employee].[Gender] d on e.Gid=d.Gid
    where e.Gid is Null

    union 
    select * from [dbo].[EmppDetails] e
     left join [Employee].[Gender] d on e.Gid=d.Gid
    where d.Gid is Null

-2

Author: user6341745,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-06-06 05:45:55

score 423 · Accepted Answer

Aquí hay una simple consulta:

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

Los puntos clave son:

LEFT JOIN se utiliza; esto devolverá TODAS las filas de Table1, independientemente de si hay o no una fila coincidente en Table2.
La cláusula WHERE t2.ID IS NULL; esto restringirá los resultados devueltos solo a aquellas filas donde el ID devuelto desde Table2 es nulo - en otras palabras, no hay NINGÚN registro en Table2 para ese ID particular de Table1. Table2.ID se devolverá como NULL para todos los registros de Table1 donde el ID no coincide en Table2.