SQL Server - 二つのテーブルのデータが完全に同じか比較する方法

ここでは、SQL Server で二つのテーブルのデータが完全に同じか比較する方法をご説明します。

テーブルの差分を比較する方法はいろいろありますが、今回は EXCEPT と JOIN を使う方法をご紹介します。

二つのテーブルは全く同じ構造で、ユニークで NOT NULL のキーとなるカラムが存在する前提でおこないます。

二つのテーブルを比較するサンプルデータを生成する

二つのテーブルのデータを比較するために、以下のスクリプトを実行して Student1 と Student2 テーブルを作ります。

StudentID がプライマリーキーです。

※ Student1 と Student2 テーブルがデータベースに既に存在している時は削除されてしまいますのでご注意ください。

IF OBJECT_ID(N'dbo.Student1', N'U') IS NOT NULL DROP TABLE Student1;
IF OBJECT_ID(N'dbo.Student2', N'U') IS NOT NULL DROP TABLE Student2;

CREATE TABLE Student1 (
   StudentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   FirstName VARCHAR(50) NULL,
   LastName VARCHAR(50) NULL,
   Birthday DATE NULL,
   Gender CHAR(1) NULL
);

CREATE TABLE Student2 (
   StudentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   FirstName VARCHAR(50) NULL,
   LastName VARCHAR(50) NULL,
   Birthday DATE NULL,
   Gender CHAR(1) NULL
);

INSERT INTO Student1
  (FirstName, LastName, Birthday, Gender)
 VALUES
  ('Taro', 'Yamada', '2011-02-10', 'M'),
  ('Hanako', 'Tanaka' ,'2010-12-30', 'F'),
  ('Yuko', 'Suzuki', '2010-07-07', 'F'),
  ('Makoto', 'Sato', '2011-03-12', 'M'),
  ('Hiroki', 'Takagi', '2010-04-05', 'M');


INSERT INTO Student2
  (FirstName, LastName, Birthday, Gender)
 VALUES
  ('Taro', 'Yamada', '2011-02-15', 'M'),
  ('Hanako', 'Tanaka' ,'2010-12-30', 'F'),
  ('Yuko', 'Suzuki', '2010-07-07', 'F'),
  ('Makoto', 'Sato', '2011-03-12', 'M'),
  ('Hiroki', 'Takagi', '2010-04-05', 'M'),
  ('Yuka', 'Kimura', '2012-03-27', 'F');

以下の緑の部分が違っている箇所です。

EXCEPT を使って二つのテーブルのデータを比較する

まずは、EXCEPT 演算子 を使って二つのテーブルのデータが完全に同じかを比較する方法です。

EXCEPT 演算子は EXCEPT の左側の結果セットに存在していて、右側の結果セットには存在していない値のみを返してくれるオペレータで、構文は次の通りです。

[ クエリー 1 ] EXCEPT [ クエリー 2 ]

EXCEPT 演算子の結果セットは DISTINCT な値を返します。

それぞれのクエリーは、カラムの数、順番が一致 していなければなりません。

また、データ型も一致しているか、暗黙に同じ型に変換可能 なものでなければなりません。

EXCEPT 演算子を使って Student1 に存在していて、Student2 に存在しないデータと、Student2 に存在していて Student1 に存在しないデータを確認します。

SELECT	*
FROM	Student1
EXCEPT
SELECT	*
FROM	Student2;

SELECT	*
FROM	Student2
EXCEPT
SELECT	*
FROM	Student1;

二つのテーブルのカラム数、データ型、カラムの順番が一致しているので * で比較できます。

EXCEPT を使うと DISTINCT な値で比較するので、全く同じ値のレコードが片方のテーブルに多く存在していたとしても、差分として出てきません。

ですが、プライマリーキーカラムが含まれているため、ひとつのテーブルに重複データが存在しないので大丈夫です。

もし、テーブルが同じかどうかを取得したいのであれば、上の結果セットの存在を確認することで取得できます。

IF EXISTS
  (SELECT *
   FROM   Student1
   EXCEPT
   SELECT *
   FROM   Student2)
 OR EXISTS 
  (SELECT *
   FROM   Student2
   EXCEPT
   SELECT *
   FROM   Student1)
BEGIN
   SELECT 'Different' AS Result;
END
ELSE
BEGIN
   SELECT 'Same' AS Result;
END

JOIN を使って二つのテーブルのデータを比較する

次は JOIN を使って、二つのテーブルのデータが完全に同じか比較する方法です。

ユニークで NOT NULL なキーをカラムを使って、二つのテーブルを結合します。

まず、キーとなるカラムで INNER JOIN してそれ以外のカラムの値がひとつでも等しくないレコードを抽出します。

等しいかどうかをチェックする際には値に NULL があるとその条件式が TRUE になることはないので気をつけてくださいね。

次に、二つのテーブルをキーとなるカラムで LEFT JOIN して、左側のテーブル 1 にあって右側のテーブル 2 にないレコードを抽出します。

そして、テーブルを反対にして LEFT JOIN して、左側のテーブル 2 にあって右側のテーブル 1 にないレコードを抽出します。

テーブルを反対にせずに RIGHT JOIN を使っても大丈夫です。

この三つのクエリーを実行することで、二つのテーブルのデータで一致していない差分のレコードを取得することができます。

SELECT T1.*,
       T2.*
FROM   Student1 AS T1
          INNER JOIN Student2 AS T2
            ON T1.StudentID = T2.StudentID
WHERE  ISNULL(T1.FirstName, '') <> ISNULL(T2.FirstName, '')
       OR ISNULL(T1.LastName, '') <> ISNULL(T2.LastName, '')
       OR ISNULL(T1.Birthday, '1900-01-01') <> ISNULL(T2.Birthday, '1900-01-01')
       OR ISNULL(T1.Gender, '') <> ISNULL(T2.Gender, '');

SELECT T1.*,
       T2.*
FROM   Student1 AS T1
	   LEFT JOIN Student2 AS T2
	      ON T1.StudentID = T2.StudentID
WHERE  T2.StudentID IS NULL;

SELECT T1.*,
       T2.*
FROM   Student2 AS T2
           LEFT JOIN Student1 AS T1
	      ON T2.StudentID = T1.StudentID
WHERE  T1.StudentID IS NULL;

もし、テーブルが同じかどうかだけを取得したいのであれば、先ほどの EXCEPT と同様に上の三つのクエリーの結果セットの存在を確認することで取得できますね。

IF EXISTS
  (SELECT *
   FROM   Student1 AS T1
            INNER JOIN Student2 AS T2
               ON T1.StudentID = T2.StudentID
   WHERE  ISNULL(T1.FirstName, '') <> ISNULL(T2.FirstName, '')
          OR ISNULL(T1.LastName, '') <> ISNULL(T2.LastName, '')
          OR ISNULL(T1.Birthday, '1900-01-01') <> ISNULL(T2.Birthday, '1900-01-01')
          OR ISNULL(T1.Gender, '') <> ISNULL(T2.Gender, ''))
 OR EXISTS
  (SELECT *
   FROM   Student1 AS T1
             LEFT JOIN Student2 AS T2
                ON T1.StudentID = T2.StudentID
   WHERE  T2.StudentID IS NULL)
 OR EXISTS
  (SELECT *
   FROM   Student2 AS T2
            LEFT JOIN Student1 AS T1
               ON T2.StudentID = T1.StudentID
   WHERE  T1.StudentID IS NULL)
BEGIN
   SELECT 'Different' AS Result;
END
ELSE
BEGIN
   SELECT 'Same' AS Result;
END

以上、SQL Server で二つのテーブルのデータが完全に同じか比較する方法をご紹介しました。