ejemplos - foreign key sql server ejemplo

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

Question

Estoy realizando un trabajo de mantenimiento en una aplicación de base de datos y descubrí que, aunque no se utilicen los valores de una tabla en el estilo de las claves externas, no hay restricciones de clave externa en las tablas.

Estoy tratando de agregar restricciones FK en estas columnas, pero me parece que, debido a que ya hay una gran cantidad de datos erróneos en las tablas de errores anteriores que se han corregido ingenuamente, tengo que encontrar las filas que no lo hacen. coincide con la otra tabla y luego elimínelos.

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.

Alguien puede explicarme cómo construir una consulta que devuelva todas las filas sin coincidencias en otra tabla, y qué está haciendo, para que yo pueda realizar estas consultas yo mismo, en lugar de ir corriendo a SO para cada tabla en este lío no hay restricciones FK?

Answer 1

¿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

Answer 2

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:

1. Se utiliza la LEFT JOIN ; esto devolverá TODAS las filas de la Table1 , independientemente de si hay o no una fila coincidente en la Table2 .

2. La WHERE t2.ID IS NULL ; esto restringirá los resultados devueltos solo a aquellas filas donde el ID devuelto de la Table2 es nulo; en otras palabras, NO hay registro en la Table2 para esa ID en particular de la Table1 . Table2.ID se devolverá como NULL para todos los registros de Table1 donde la ID no coincida en Table2 .

Answer 3

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 borra los resultados.

Answer 4

No sé cuál está optimizado (comparado con @AdaTheDev), pero este parece ser más rápido cuando lo 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 utilizar:

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

Answer 5

Puedes optar por las Vistas como se muestra a continuación:

CREATE VIEW AuthorizedUserProjectView AS select t1.username as username, t1.email as useremail, p.id as projectid, 
(select m.role from userproject m where m.projectid = p.id and m.userid = t1.id) as role 
FROM authorizeduser as t1, project as p

y luego trabajar en la vista para seleccionar o actualizar:

select * from AuthorizedUserProjectView where projectid = 49

que produce el resultado como se muestra en la imagen a continuación, es decir, para la columna nula no coincidente se ha completado.

[Result of select on the view][1]

Answer 6

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

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

1. Usando la unión interna

   seleccione * de empleado donde id no está en (seleccione e.id de empleado e salario de unión interna s en e.id = s.id)

2. Usando la unión externa izquierda

   seleccione * del empleado e dejó el salario de la combinación externa s en e.id = s.id donde s.id es nulo

3. Usando Full Join

   seleccione * del empleado e salario externo de la combinación completa en e.id = s.id donde e.id no está en (seleccione id del salario)

Answer 7

Yo usaría la expresión EXISTS ya que es más poderosa, es decir, puede elegir más precisamente las filas a las que le gustaría unirse, en caso de LEFT JOIN , debe 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)

Answer 8

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 foránea, pero los valores en el foreign_key_id_column no coinciden todos con una identificación en la tabla 2.

1. La selección inicial enumera los identificadores de la tabla1. estas serán las filas que queremos eliminar.
2. La cláusula ''no está'' en la declaración donde limita la consulta a solo filas en las que el valor en el archivo foreign_key_id_column no se encuentra en la lista de identificadores de la tabla 2.
3. La instrucción de selección entre paréntesis obtendrá una lista de todos los identificadores que se encuentran en la tabla 2.