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

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

サンプルデータの作成

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

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

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

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

1回のINSERT(インサートSQL)で複数行のレコードを一括挿入(追加)する
SQLServerやMySQLなどのデータベースで、テーブルにレコードをINSERT文使用して追加するには、通常は以下のように記述します。 ...

重複レコードを抽出する

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

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

実行結果

電話番号リストテーブルを電話番号でグループ化して取得

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

HAVING句を使用する方法

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

実行結果

電話番号の重複をGROUP BYとHAVINGで取得

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

サブクエリにする方法

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

実行結果

電話番号の重複をGROUP BYとサブクエリで取得

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

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

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

HAVING句のSQLと自己結合

サブクエリのSQLと自己結合

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

取得結果

電話番号が重複する全てのレコードを自己結合して取得

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