Comment sélectionner des lignes sans entrée correspondante dans une autre table ?

Question

J'effectue quelques travaux de maintenance sur une application de base de données et j'ai découvert que, joie des joies, même si les valeurs d'une table sont utilisées dans le style des clés étrangères, il n'y a pas de contraintes de clé étrangère sur les tables.

J'essaie d'ajouter des contraintes FK sur ces colonnes, mais je me rends compte que, parce qu'il y a déjà tout un tas de mauvaises données dans les tables provenant d'erreurs précédentes qui ont été naïvement corrigées, je dois trouver les lignes qui ne correspondent pas à l'autre table, puis les supprimer.

J'ai trouvé quelques exemples de ce type de requête sur le web, mais ils semblent tous fournir des exemples plutôt que des explications, et je ne comprends pas pourquoi ils fonctionnent.

Quelqu'un peut-il m'expliquer comment construire une requête qui renvoie toutes les lignes qui n'ont pas de correspondance dans une autre table, et ce qu'elle fait, de sorte que je puisse faire ces requêtes moi-même, plutôt que de courir à SO pour chaque table dans ce désordre qui n'a pas de contraintes FK ?

Answer 1

A partir d'une question similaire ici Requête de jointure interne MySQL pour obtenir des enregistrements non présents dans l'autre table J'ai réussi à faire fonctionner ceci

SELECT * FROM bigtable 
LEFT JOIN smalltable ON bigtable.id = smalltable.id 
WHERE smalltable.id IS NULL

smalltable est l'endroit où vous avez des enregistrements manquants, bigtable est l'endroit où vous avez tous les enregistrements. La requête liste tous les enregistrements qui n'existent pas dans la base de données. smalltable mais existe sur le bigtable . Vous pourriez remplacer id par tout autre critère de correspondance.

Answer 2

Je ne sais pas lequel est optimisé (par rapport à @AdaTheDev ) mais celui-ci semble être plus rapide lorsque je l'utilise (du moins pour moi).

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

Si vous voulez obtenir tout autre attribut spécifique, vous pouvez utiliser :

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

Answer 3

Vous pouvez opter pour Vues comme indiqué ci-dessous :

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

et ensuite travailler sur la vue à sélectionner ou à mettre à jour :

select * from AuthorizedUserProjectView where projectid = 49

ce qui donne le résultat montré dans l'image ci-dessous, c'est-à-dire que pour les colonnes non correspondantes, la valeur null a été remplie.

[Result of select on the view][1]

Answer 4

Vous pouvez faire quelque chose comme ceci

   SELECT IFNULL(`price`.`fPrice`,100) as fPrice,product.ProductId,ProductName 
          FROM `products` left join `price` ON 
          price.ProductId=product.ProductId AND (GeoFancingId=1 OR GeoFancingId 
          IS NULL) WHERE Status="Active" AND Delete="No"

Answer 5

SELECT * FROM First_table MOINS SELECT * FROM another