La comparaison de la Table SQL à lui-même (Auto-jointure)

J'essaie de trouver les doublons de lignes basé sur des colonnes. Ceci est un exemple de ce que j'ai:

CREATE TABLE Test
(
   id INT PRIMARY KEY,
   test1 varchar(124),
   test2 varchar(124)
)

INSERT INTO TEST ( id, test1, test2 ) VALUES ( 1, 'A', 'B' )
INSERT INTO TEST ( id, test1, test2 ) VALUES ( 2, 'B', 'C' )

Maintenant, si je lance cette requête:

SELECT [LEFT].[ID] 
FROM [TEST] AS [LEFT] 
   INNER JOIN [TEST] AS [RIGHT] 
   ON [LEFT].[ID] != [RIGHT].[ID] 
WHERE [LEFT].[TEST1] = [RIGHT].[TEST2]

Je m'attends à dos les deux id. (1 et 2), cependant je ne jamais revenir le une ligne.

Mes pensées serait qu'il doit comparer chaque ligne, mais je suppose que ce n'est pas correct?
Pour corriger cela, j'avais changé ma requête:

SELECT [LEFT].[ID] 
FROM [TEST] AS [LEFT] 
   INNER JOIN [TEST] AS [RIGHT] 
   ON [LEFT].[ID] != [RIGHT].[ID] 
WHERE [LEFT].[TEST1] = [RIGHT].[TEST2] 
OR [LEFT].[TEST2] = [RIGHT].[TEST1]

Qui me donne à la fois les lignes, mais les performances se dégradent très rapidement sur la base du nombre de lignes.

La solution finale, je suis venu pour les performances et les résultats a été d'utiliser une union:

SELECT [LEFT].[ID] 
FROM [TEST] AS [LEFT] 
   INNER JOIN [TEST] AS [RIGHT] 
   ON [LEFT].[ID] != [RIGHT].[ID] 
WHERE [LEFT].[TEST1] = [RIGHT].[TEST2] 
UNION
SELECT [LEFT].[ID] 
FROM [TEST] AS [LEFT] 
   INNER JOIN [TEST] AS [RIGHT] 
   ON [LEFT].[ID] != [RIGHT].[ID] 
WHERE [LEFT].[TEST2] = [RIGHT].[TEST1]

Mais dans l'ensemble, je suis évidemment manquant de comprendre pourquoi ceci ne fonctionne pas, ce qui signifie que je suis probablement fait quelque chose de mal. Quelqu'un pourrait-il me diriger dans la bonne direction?

Essayez la première requête avec ces données: INSERT INTO TEST ( id, test1, test2 ) VALUES ( 1, 'C', 'B' ) INSERT INTO TEST ( id, test1, test2 ) VALUES ( 2, 'B', 'C' ) Qui devrait vous donner à la fois les lignes.

OriginalL'auteur Kyle | 2009-12-11

11

Ne pas ADHÉRER à une inégalité; il semble que la JOINTURE et OÙ les conditions sont inversées.
```
SELECT t1.id
FROM Test t1
INNER JOIN Test t2
ON ((t1.test1 = t2.test2) OR (t1.test2 = t2.test1))
WHERE t1.id <> t2.id
```
Devrait fonctionner correctement.

Bonjour, Depuis quelques tests cela semble encore plus lent qu'à l'aide de l'union: Quelle est la raison de ne jamais rejoindre sur les inégalités? Ne serait pas l'instruction where être le même? (Bien que potentiellement votre jointure retourne de moins en moins de lignes que les autres, ce qui peut accélérer la requête. Est-ce la raison?)
Dans mon test, l'UNION version prend plus de 3 fois plus longtemps. Comment êtes-vous tester exactement? La raison de ne pas ADHÉRER à une inégalité, c'est que l'optimiseur doit lire chaque ligne unique de satisfaction que l'état (c'est à dire presque tous) et le filtre par la suite; cette version permet d'utiliser un index sur la colonne test1 ou test2 ou les deux. À moins que l'optimiseur est en quelque sorte la réécriture de votre requête, vous devriez voir une énorme amélioration des performances si vous utilisez cette version avec le bon index.
En fait, maintenant que j'y pense, depuis votre schéma ne semble pas avoir l'index utiles, la requête que j'ai posté effectuera le même que l'inégalité-requête de jointure; peu importe ce que vous faites, vous vous retrouverez avec deux cluster-index analyse, qui est horrible. Vous avez besoin de la couverture des index sur (test1, test2) et (test2, test1) pour obtenir de meilleures performances.

OriginalL'auteur Aaronaught
5

Vous obtenez seulement deux id si vous sélectionnez:
```
SELECT [LEFT].[ID], [RIGHT].[ID] 
FROM [TEST] AS [LEFT] 
   INNER JOIN [TEST] AS [RIGHT] 
   ON [LEFT].[ID] != [RIGHT].[ID] 
WHERE [LEFT].[TEST1] = [RIGHT].[TEST2]
```
La raison que qu'une seule LIGNE, une seule ligne (à savoir la ligne n ° 2) a une TEST1 qui est égale à une autre ligne, TEST2.

+1 parce que vous l'avez expliqué pourquoi la syntaxe d'origine ne fonctionnait pas. Et parce que votre réponse œuvres. "Cette réponse est utile"

OriginalL'auteur Klaus Byskov Pedersen
2

Je dirait que vous êtes en train de travailler très rapidement vers un Cartiesian Rejoindre. Normalement, si vous êtes à la recherche pour le retour des doublons, vous devez exécuter quelque chose comme:
```
SELECT [LEFT].*
FROM [TEST]  AS [LEFT]
INNER JOIN [TEST] AS [RIGHT]
    ON [LEFT].[test1] = [RIGHT].[test1]
        AND [LEFT].[test2] = [RIGHT].[test2]
        AND [LEFT].[id] <> [RIGHT].[id]
```
Si vous avez besoin de mélanger les colonnes, puis mélanger les conditions requises, mais faire quelque chose comme:
```
SELECT [LEFT].*
FROM [TEST] AS [LEFT]
INNER JOIN [TEST] AS [RIGHT]
    ON (
        [LEFT].[test1] = [RIGHT].[test2]
            OR [LEFT].[test2] = [RIGHT].[test1]
       )
        AND [LEFT].[id] <> [RIGHT].[id]
```
En utilisant cela, vous comparez le à droite à gauche et de gauche à droite dans chaque jointure, éliminant ainsi la nécessité pour les OÙ tout.

Cependant, ce style de requête croît de façon exponentielle en temps d'exécution pour chaque ligne insérée dans la table, puisque vous êtes en comparant chaque ligne à chaque ligne.

OriginalL'auteur Nathan Wheeler
0

Cela peut être fait avec des jointures internes si je ne me trompe pas.
Mon premier temps de répondre à mysql genre de question, mais je suis juste de répondre pour obtenir plus de points ici sur StackOverflow.
La virgule est très important pour que mysql ne se plaint pas.
```
SELECT [LEFT].[ID] FROM [TEST] AS [LEFT], [TEST] AS [RIGHT] 
WHERE [LEFT].[ID] != [RIGHT].[ID] 
AND [LEFT].[TEST1] = [RIGHT].[TEST2];
```
OriginalL'auteur lucky85dog

Vous devez vous connecter pour publier un commentaire.