SQLで重複しているレコードを全て抽出する (GROUP BY + HAVING)

大量のデータの中からあるキーとなる値が重複するレコードを抽出したい時があります。
例えばキーとなる値が一意になるように作成したつもりのテストデータが、意図せずに重複している場合など…。
今回はSQLで重複しているレコードを抽出する方法の紹介です。

サンプルデータの作成

重複するレコードを抽出するためのサンプルデータを作成します。
キーとなる値を保持する列として電話番号を持ち、電話番号の所有者を表す氏名の列も持つ電話番号リストテーブルを作成します。

テーブルのCREATEとデータのINSERT

データは「テスト 氏名1」と「テスト 氏名7」の電話番号を重複させておきます。

上記のTEL_LISTテーブルへのデータのインサートは、複数のレコードを1回のSQLで一括挿入する形式で記述しています。
1回のINSERTで複数行のレコードをテーブルに一括で挿入する方法については、以下の記事を参照してください。

1回のINSERT（インサートSQL）で複数行のレコードを一括挿入（追加）する

SQLServerやMySQLなどのデータベースで、テーブルにレコードをINSERT文使用して追加するには、通常は以下のように記述します。 ...

johobase.com

重複レコードを抽出する

重複するレコードを抽出するにはGROUP BY句を使用します。
GROUP BY句にで一意となるキーを指定して取得します。

上記のSQLを実行すると7件のレコードが取得できます。

COUNT関数で電話番号の件数を取得して「09012345678」のレコードが2件あることが確認します。

HAVING句を使用する方法

HAVING句を使用する場合はGROUP BY句でグループ化して、COUNT関数で件数を取得した結果が1より大きいレコード（2件以上あるレコード）をHAVING句の条件に指定します。
HAVING句を使用して重複するレコードを取得するSQLは以下のようになります。

HAVING句に指定した条件によって電話番号が「09012345678」の「テスト 氏名1」と「テスト 氏名7」の2件のレコードが対象になっているのがわかります。

サブクエリにする方法

重複しているレコードの取得はHAVING句を使わずにグループ化した結果をサブクエリとして、サブクエリに自分自身のテーブルを自己結合することで取得することもできます。
以下にGROUP BYの結果をサブクエリにして重複するレコードを取得するSQLを記載します。

上記のSQLではグループ化した結果をサブクエリにして（6～14行目）WHERE句の条件に件数が1より大きいレコード（2件以上あるレコード）を指定します。

自己結合して対象のレコードを全て取得

最後に取得した重複レコードの結果と自分自身のテーブル（TEL_LIST）を自己結合して電話番号が重複している人の氏名（FULL_NAME）を取得する例を記載しておきます。

上記のSQLではグループ化したサブクエリの結果表に自分自身のテーブル（TEL_LIST）を電話番号（TEL）をキーにINNER JOINで内部結合しています。
自己結合することで重複している全てのレコードを結果表として取得できます。

自己結合した結果、「テスト 氏名1」と「テスト 氏名7」の2件のレコードが取得されます。