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

Voici une requête simple :

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

Les points clés sont les suivants :

1. LEFT JOIN est utilisé ; cela renverra TOUTES les lignes de l'application Table1 indépendamment du fait qu'il existe ou non une ligne correspondante dans Table2 .

2. El WHERE t2.ID IS NULL ce qui limitera les résultats renvoyés aux seules lignes pour lesquelles l'ID renvoyé par la clause Table2 est nulle - en d'autres termes, il y a NON record en Table2 pour cet ID particulier à partir de Table1 . Table2.ID sera renvoyé comme NULL pour tous les enregistrements de la catégorie Table1 où l'ID ne correspond pas à l'ID dans Table2 .

Answer 2

J'utiliserais EXISTS expression car elle est plus puissante, vous pouvez, par exemple, choisir plus précisément les lignes que vous souhaitez joindre. Dans le cas de LEFT JOIN tu dois prendre tout ce qui est dans la table jointe. Son efficacité est probablement la même que dans le cas de LEFT JOIN avec une contrainte nulle.

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

Answer 3

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

Le tableau 1 possède une colonne à laquelle vous souhaitez ajouter la contrainte de clé étrangère, mais les valeurs dans la colonne foreign_key_id_column ne correspondent pas toutes à un id dans le tableau 2.

1. La sélection initiale énumère les id s du tableau 1. Ce seront les lignes que nous voulons supprimer.
2. El NOT IN dans l'instruction where limite la requête aux seules lignes pour lesquelles la valeur de la clause foreign_key_id_column n'est pas dans la liste du tableau 2 id s.
3. El SELECT entre parenthèses permet d'obtenir une liste de toutes les id qui figurent dans le tableau 2.

Answer 4

Nous avons les 2 tableaux suivants (salaire et employé)

Maintenant je veux les enregistrements de la table des employés qui ne sont pas dans le salaire. Nous pouvons le faire de trois façons :

1. Utilisation de la jointure interne

   select * from employee where id not in(select e.id from employee e inner join salary s on e.id=s.id)

2. Utilisation de la jointure externe gauche

   select * from employee e left outer join salary s on e.id=s.id where s.id is null

3. Utilisation de la jointure complète

   select * from employee e full outer join salary s on e.id=s.id where e.id not in(select id from salary)

Answer 5

Où T2 est la table à laquelle vous ajoutez la contrainte :

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

Et supprimez les résultats.