¿Cómo seleccionar filas sin entradas coincidentes en otra tabla?
Estoy haciendo un trabajo de mantenimiento en una aplicación de base de datos y descubrí que, ¡qué alegría!, aunque los valores de una tabla se usan al estilo de claves externas, no hay restricciones de clave externa en las tablas.
Estoy intentando agregar restricciones FK en estas columnas, pero descubro que, debido a que ya hay una gran cantidad de datos incorrectos en las tablas de errores anteriores que se han corregido ingenuamente, necesito encontrar las filas que no lo hacen. coincidir con la otra tabla y luego eliminarlos.
Encontré algunos ejemplos de este tipo de consultas en la web, pero todos parecen proporcionar ejemplos en lugar de explicaciones y no entiendo por qué funcionan.
¿Alguien puede explicarme cómo construir una consulta que devuelva todas las filas sin coincidencias en otra tabla, y qué está haciendo, para que pueda hacer estas consultas yo mismo, en lugar de ejecutar SO para cada tabla en este lío que tiene ¿Sin restricciones FK?
Aquí hay una consulta simple:
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 JOINse utiliza; esto devolverá TODAS las filas deTable1, independientemente de si hay o no una fila coincidente enTable2.La
WHERE t2.ID IS NULLcláusula; esto restringirá los resultados devueltos solo a aquellas filas donde el ID devuelto es nulo; en otras palabras, NOTable2hay ningún registro para ese ID en particular . se devolverá como NULL para todos los registros en los que el ID no coincida .Table2Table1Table2.IDTable1Table2
Yo usaría EXISTSla expresión ya que es más potente; por ejemplo, puedes elegir con mayor precisión las filas que te gustaría unir. En el caso de LEFT JOIN, debes tomar todo lo que está en la tabla unida. Su eficiencia es probablemente la misma que en el caso de LEFT JOINrestricción nula.
SELECT t1.ID
FROM Table1 t1
WHERE NOT EXISTS (SELECT t2.ID FROM Table2 t2 WHERE t1.ID = t2.ID)
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 foreign_key_id_columnno todos los valores de la tabla 2 coinciden con los idde la tabla 2.
- La selección inicial enumera los
idmensajes de la tabla1. Estas serán las filas que queremos eliminar. - La
NOT INcláusula en la declaración donde limita la consulta solo a filas donde el valor enforeign_key_id_columnno está en la lista de la tabla 2id. - La
SELECTdeclaración entre paréntesis obtendrá una lista de todos losidque se encuentran en la tabla 2.
Tengamos las siguientes 2 tablas (salario y empleado)
Ahora quiero esos registros de la tabla de empleados que no están en salario. Esto lo podemos hacer de 3 maneras:
- Usando unión interna
select * from employee
where id not in(select e.id from employee e inner join salary s on e.id=s.id)
- Usando unión exterior izquierda
select * from employee e
left outer join salary s on e.id=s.id where s.id is null
- Usando unión completa
select * from employee e
full outer join salary s on e.id=s.id where e.id not in(select id from salary)
