DB

SQL Serverで複雑なクエリをもつデータ間の差分を抽出してみた

 

SQL Serverで、2つのSELECT文の抽出結果差分を取得するにはEXCEPT演算子を利用すればよいが、WITH句を含むSELECT文の場合、一時テーブルを利用して差分を抽出するとよい。

今回は、2つのSELECT文の抽出結果差分を取得してみたので、そのSQLを共有する。

前提条件

下記記事の「前提条件」の内容が完了していること。

Spring BootでSQL Serverに接続しMyBatisを利用してみた今回は、Spring Bootアプリケーションで接続するデータベースをSQL Serverに変更してみたので、そのサンプルプログラムを共...

差分データの抽出

差分データの抽出用データと抽出用SQL・抽出結果は、以下の通り。

1) 今回は、以下のデータを利用するものとする。

SELECT * FROM user_data
差分データの抽出_1_1
SELECT * FROM user_address
WHERE user_key IN ('post', 'address')
差分データの抽出_1_2 エンジニアファーストバナー

2) 1)のデータから、住所が東京または神奈川であるユーザーのID・氏名・郵便番号・住所を抽出した結果は、以下の通り。

WITH address AS (
    SELECT
         user_id
       , user_value
    FROM user_address
    WHERE user_key = 'address'
    AND user_value LIKE N'東京%' OR user_value LIKE N'神奈川%'
), post AS (
    SELECT
         user_id
       , user_value
    FROM user_address
    WHERE user_key = 'post'
)
SELECT 
      ud.id
    , ud.name
    , p.user_value AS post
    , a.user_value AS address
FROM user_data ud
INNER JOIN address a
    ON ud.id = a.user_id
LEFT JOIN post p
    ON ud.id = p.user_id
差分データの抽出_2

3) 1)のデータから、住所が東京または埼玉であるユーザーのID・氏名・郵便番号・住所を抽出した結果は、以下の通り。

WITH address AS (
    SELECT
         user_id
       , user_value
    FROM user_address
    WHERE user_key = 'address'
    AND user_value LIKE N'東京%' OR user_value LIKE N'埼玉%'
), post AS (
    SELECT
         user_id
       , user_value
    FROM user_address
    WHERE user_key = 'post'
)
SELECT 
      ud.id
    , ud.name
    , p.user_value AS post
    , a.user_value AS address
FROM user_data ud
INNER JOIN address a
    ON ud.id = a.user_id
LEFT JOIN post p
    ON ud.id = p.user_id
差分データの抽出_3

4) 2)3)のデータ間の差分を抽出するプログラム(select_difference.sql)の内容は、以下の通り。

-- 差分格納用一時テーブルを作成
CREATE TABLE #tmpUser1 (
    id INT
  , name NVARCHAR(40)
  , post NVARCHAR(1024)
  , address NVARCHAR(1024)
);

CREATE TABLE #tmpUser2 (
    id INT
  , name NVARCHAR(40)
  , post NVARCHAR(1024)
  , address NVARCHAR(1024)
);

-- 東京または神奈川の住所をもつユーザー情報を一時テーブル(#tmpUser1)に格納
WITH address AS (
    SELECT
         user_id
       , user_value
    FROM user_address
    WHERE user_key = 'address'
    AND user_value LIKE N'東京%' OR user_value LIKE N'神奈川%'
), post AS (
    SELECT
         user_id
       , user_value
    FROM user_address
    WHERE user_key = 'post'
)
INSERT INTO #tmpUser1
SELECT 
      ud.id
    , ud.name
    , p.user_value AS post
    , a.user_value AS address
FROM user_data ud
INNER JOIN address a
    ON ud.id = a.user_id
LEFT JOIN post p
    ON ud.id = p.user_id;

-- 東京または埼玉の住所をもつユーザー情報を一時テーブル(#tmpUser2)に格納
WITH address AS (
    SELECT
         user_id
       , user_value
    FROM user_address
    WHERE user_key = 'address'
    AND user_value LIKE N'東京%' OR user_value LIKE N'埼玉%'
), post AS (
    SELECT
         user_id
       , user_value
    FROM user_address
    WHERE user_key = 'post'
)
INSERT INTO #tmpUser2
SELECT 
      ud.id
    , ud.name
    , p.user_value AS post
    , a.user_value AS address
FROM user_data ud
INNER JOIN address a
    ON ud.id = a.user_id
LEFT JOIN post p
    ON ud.id = p.user_id;

-- (#tmpUser1のデータ)-(#tmpUser2のデータ)を表示
SELECT * FROM #tmpUser1
EXCEPT
SELECT * FROM #tmpUser2;

-- (#tmpUser2のデータ)-(#tmpUser1のデータ)を表示
SELECT * FROM #tmpUser2
EXCEPT
SELECT * FROM #tmpUser1;

-- 差分格納用一時テーブルを削除
DROP TABLE #tmpUser1;
DROP TABLE #tmpUser2;

上記SQLを実行した結果は以下の通りで、WITH句を含む検索結果(#tmpUser1, #tmpUser2)の差分が表示されることが確認できる。
差分データの抽出_4

要点まとめ

  • SQL Serverで、2つのSELECT文の抽出結果差分を取得するにはEXCEPT演算子を利用すればよいが、WITH句を含むSELECT文の場合、一時テーブルを利用して差分を抽出するとよい。
RELATED POST