Requête SQL pour trouver un enregistrement dont l'ID ne se trouve pas dans une autre table

Question

J'ai deux tables avec des clés primaires liées dans la base de données et je souhaite trouver un ensemble disjoint entre elles. Par exemple,

• Table1 a des colonnes ( ID, Name ) et des données échantillons : (1 ,John), (2, Peter), (3, Mary)
• Table2 a des colonnes ( ID, Address ) et des données échantillons : (1, address2), (2, address2)

Alors comment puis-je créer une requête SQL afin de récupérer la ligne avec l'ID à partir de table1 qui n'est pas dans table2 . Dans ce cas, (3, Mary) devrait être renvoyé ?

PS : L'ID est la clé primaire de ces deux tables.

Answer 1

Essayez ceci

SELECT ID, Name 
FROM   Table1 
WHERE  ID NOT IN (SELECT ID FROM Table2)

Answer 2

Utilisez LEFT JOIN

SELECT  a.*
FROM    table1 a
            LEFT JOIN table2 b
                on a.ID = b.ID
WHERE   b.id IS NULL

Answer 3

Il y a essentiellement 3 approches pour cela : not exists , not in y left join / is null .

JOINT GAUCHE avec IS NULL

SELECT  l.*
FROM    t_left l
LEFT JOIN
        t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

PAS DANS

SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
        (
        SELECT  value
        FROM    t_right r
        )

N'EXISTE PAS

SELECT  l.*
FROM    t_left l
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    t_right r
        WHERE   r.value = l.value
        )

Laquelle est la meilleure ? Pour répondre à cette question, il serait peut-être préférable de s'adresser aux principaux fournisseurs de SGBDR. D'une manière générale, il faut éviter d'utiliser select ... where ... in (select...) lorsque l'ordre de grandeur du nombre d'enregistrements dans la sous-requête est inconnu. Certains fournisseurs peuvent limiter la taille. Oracle, par exemple, a une limite de 1 000 . La meilleure chose à faire est d'essayer les trois et de montrer le plan d'exécution.

Spécifiquement sous PostgreSQL, le plan d'exécution de NOT EXISTS y LEFT JOIN / IS NULL sont les mêmes. Je préfère personnellement le NOT EXISTS option car elle montre mieux l'intention. Après tout, la sémantique est que vous voulez trouver des enregistrements dans A que son pk n'existent pas en B .

Vieux mais toujours doré, spécifique à PostgreSQL cependant : https://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/

Answer 4

Alternative rapide

J'ai effectué quelques tests (sur postgres 9.5) en utilisant deux tables avec ~2M de lignes chacune. La requête ci-dessous a donné des résultats au moins 5* meilleurs que les autres requêtes proposées :

-- Count
SELECT count(*) FROM (
    (SELECT id FROM table1) EXCEPT (SELECT id FROM table2)
) t1_not_in_t2;

-- Get full row
SELECT table1.* FROM (
    (SELECT id FROM table1) EXCEPT (SELECT id FROM table2)
) t1_not_in_t2 JOIN table1 ON t1_not_in_t2.id=table1.id;

Answer 5

En gardant à l'esprit les points soulevés dans le commentaire/lien de @John Woo ci-dessus, voici comment je m'y prendrais en général :

SELECT t1.ID, t1.Name 
FROM   Table1 t1
WHERE  NOT EXISTS (
    SELECT TOP 1 NULL
    FROM Table2 t2
    WHERE t1.ID = t2.ID
)