[SQL] MariaDB/MySQL 테이블 비교(Compare two table)

두 테이블에 대한 컬럼 속성 정보를 비교하는 쿼리를 작성합니다.

 

아래는 우선 두 테이블의 데이터를 비교해서 같지 않은 항목을 추출한 후에 row 형태로 변경 합니다.

 

1. 테이블 생성

/* 현재 사용자 정보  */
CREATE TABLE EST_CURR_USER (USER_NO INT NOT NULL PRIMARY KEY, 
  "USER_NM" varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  "USER_GRP_NM" varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL ,
  "USER_DESC" varchar(200) CHARACTER SET utf8 COLLATE utf8_bin 
) ENGINE=INNODB;

/* 변경 사용자 정보  */
CREATE TABLE EST_CHG_USER (USER_NO INT NOT NULL PRIMARY KEY, 
  "USER_NM" varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  "USER_GRP_NM" varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL ,
  "USER_DESC" varchar(200) CHARACTER SET utf8 COLLATE utf8_bin 
) ENGINE=INNODB;

2. 샘플 데이터 생성 - EST_CURR_USER

  - 현재 사용자 정보를 생성 합니다.

INSERT INTO EST_CURR_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(140,'TEST','DB팀','관리권한자')
,(11011,'TEST11','DB팀',NULL)
,(11020,'TEST1010','백앤드팀',NULL)
,(11021,'TEST1111','백앤드팀',NULL)
,(11022,'TEST1212','백앤드팀','백앤드 파이썬')
,(11211,'TEST111','백앤드팀',NULL)
,(11212,'TEST122','백앤드팀','백앤드 자바 ')
,(11220,'TEST11010','백앤드팀','백앤드 자바 ')
,(11221,'TEST11111','DB팀','관리권한자')
,(11222,'TEST11212','백앤드팀','백앤드 자바 ')
;
INSERT INTO EST_CURR_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(11223,'TEST11313','DB팀','관리권한자')
,(11224,'TEST11414','백앤드팀',NULL)
,(11225,'TEST11515','백앤드팀',NULL)
,(11226,'TEST11616','백앤드팀',NULL)
,(11227,'TEST11717','백앤드팀',NULL)
,(11228,'TEST11818','백앤드팀',NULL)
,(11229,'TEST11919','DB팀','관리권한자')
,(11230,'TEST12020','파생상품팀','파생 상품 관리팀')
,(11231,'TEST12121','파생상품팀',NULL)
,(11232,'TEST12222','파생상품팀',NULL)
;
INSERT INTO EST_CURR_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(11233,'TEST12323','파생상품팀',NULL)
,(11234,'TEST12424','DB팀','관리권한자')
,(11235,'TEST12525','파생상품팀',NULL)
,(11236,'TEST12626','파생상품팀',NULL)
,(11237,'TEST12727','파생상품팀',NULL)
,(11310,'TEST1100100','클라우드기술팀',NULL)
,(21811,'TEST11','클라우드기술팀',NULL)
,(21812,'TEST22','DB팀',NULL)
,(21814,'TEST44','DB팀',NULL)
,(21821,'TEST1111','DB팀',NULL)
;
INSERT INTO EST_CURR_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(21826,'TEST1616','DB팀',NULL)
,(21828,'TEST1818','클라우드기술팀','클라우드 기술 분석가')
,(21829,'TEST1919','클라우드기술팀','클라우드 기술 분석가')
,(21830,'TEST2020','클라우드기술팀','클라우드 기술 분석가')
,(21831,'TEST2121','클라우드기술팀','클라우드 기술 분석가')
,(21832,'TEST2222','DB팀',NULL)
,(21833,'TEST2323','클라우드기술팀','클라우드 기술 분석가')
,(21834,'TEST2424','클라우드기술팀','클라우드 기술 분석가')
,(21835,'TEST2525','클라우드기술팀',NULL)
,(21836,'TEST2626','DB팀',NULL)
;
INSERT INTO EST_CURR_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(21837,'TEST2727','클라우드기술팀',NULL)
,(21838,'TEST2828','DB팀',NULL)
,(21839,'TEST2929','클라우드기술팀',NULL)
,(21840,'TEST3030','클라우드기술팀',NULL)
,(21841,'TEST3131','클라우드기술팀',NULL)
,(21842,'TEST3232','클라우드기술팀',NULL)
,(21843,'TEST3333','DB팀',NULL)
,(21844,'TEST3434','클라우드기술팀',NULL)
,(21845,'TEST3535','DB팀','관리권한자')
,(21846,'TEST3636','클라우드기술팀',NULL)
;
INSERT INTO EST_CURR_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(21847,'TEST3737','클라우드기술팀',NULL)
,(21848,'TEST3838','클라우드기술팀',NULL)
,(21849,'TEST3939','클라우드기술팀',NULL)
,(21850,'TEST4040','클라우드기술팀',NULL)
,(21851,'TEST4141','DB팀','관리권한자')
,(21852,'TEST4242','클라우드기술팀',NULL)
,(21853,'TEST4343','파생상품팀',NULL)
,(21854,'TEST4444','파생상품팀',NULL)
,(21855,'TEST4545','파생상품팀',NULL)
,(21856,'TEST4646','파생상품팀','파생 상품 관리팀')
;
INSERT INTO EST_CURR_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(21857,'TEST4747','DB팀','관리권한자')
,(21858,'TEST4848','파생상품팀','파생 상품 관리팀')
,(21859,'TEST4949','파생상품팀','파생 상품 관리팀')
,(21860,'TEST5050','파생상품팀','파생 상품 관리팀')
,(21861,'TEST5151','파생상품팀','파생 상품 관리팀')
,(21862,'TEST5252','DB팀','관리권한자')
,(21863,'TEST5353','파생상품팀','파생 상품 관리팀')
,(21864,'TEST5454','파생상품팀','파생 상품 관리팀')
,(21868,'TEST5858','DB팀','관리권한자')
,(21871,'TEST6161','DB팀','관리권한자')
;
INSERT INTO EST_CURR_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(21874,'TEST6464','DB팀','관리권한자')
,(21875,'TEST6565','파생상품팀','파생 상품 관리팀')
,(21876,'TEST6666','파생상품팀','파생 상품 관리팀')
,(21877,'TEST6767','DB팀','관리권한자')
,(21878,'TEST6868','백앤드팀',NULL)
,(21879,'TEST6969','백앤드팀',NULL)
,(21880,'TEST7070','백앤드팀',NULL)
,(21881,'TEST7171','백앤드팀','백앤드 자바 ')
,(21882,'TEST7272','DB팀','관리권한자')
,(21883,'TEST7373','DB팀','관리권한자')
;
INSERT INTO EST_CURR_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(21884,'TEST7474','파생상품팀','파생 상품 관리팀')
,(21885,'TEST7575','파생상품팀','파생 상품 관리팀')
,(21886,'TEST7676','DB팀','관리권한자')
,(21910,'TEST100100','파생상품팀','파생 상품 관리팀')
;

3. 샘플 데이터 생성 - EST_CHG_USER

  - 변경된 사용자 정보를 생성 합니다.

IINSERT INTO EST_CHG_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(140,'TEST','DB팀','관리권한자')
,(11011,'TEST11','데이터분석팀','TEST')
,(11020,'TEST1010','백앤드팀',NULL)
,(11021,'TEST1111','백앤드팀',NULL)
,(11022,'TEST1212','백앤드팀','백앤드 파이썬')
,(11211,'TEST111','백앤드팀',NULL)
,(11212,'TEST122','백앤드팀','백앤드 자바 ')
,(11221,'TEST11111','DB팀','관리권한자')
,(11222,'TEST11212','백앤드팀','백앤드 총괄')
,(11223,'TEST11313','DB팀','관리권한자')
;
INSERT INTO EST_CHG_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(11224,'박수철','백앤드팀',NULL)
,(11225,'박이선','백앤드팀',NULL)
,(11226,'TEST11616','백앤드팀',NULL)
,(11227,'TEST11717','백앤드팀',NULL)
,(11229,'TEST11919','DB팀','관리권한자')
,(11230,'TEST12020','파생상품팀','파생 상품 관리팀')
,(11231,'TEST12121','파생상품팀',NULL)
,(11232,'TEST12222','파생상품팀',NULL)
,(11234,'TEST12424','DB팀','관리권한자')
,(11235,'TEST12525','파생상품팀',NULL)
;
INSERT INTO EST_CHG_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(11236,'TEST12626','파생상품팀',NULL)
,(11237,'TEST12727','파생상품팀',NULL)
,(11310,'TEST1100100','클라우드기술팀',NULL)
,(21812,'TEST22','DB팀',NULL)
,(21813,'TEST33','클라우드기술팀',NULL)
,(21814,'TEST44','DB팀',NULL)
,(21815,'홍길동','클라우드기술팀',NULL)
,(21816,'TEST66','클라우드기술팀','클라우드 기술 분석가')
,(21820,'TEST1010','클라우드기술팀','클라우드 기술 분석가')
,(21822,'TEST1212','클라우드기술팀','클라우드 기술 분석가')
;
INSERT INTO EST_CHG_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(21823,'TEST1313','클라우드기술팀','클라우드 기술 분석가')
,(21824,'TEST1414','클라우드기술팀','클라우드 기술 분석가')
,(21825,'TEST1515','클라우드기술팀','클라우드 기술 분석가')
,(21826,'TEST1616','DB팀',NULL)
,(21827,'제임스','클라우드기술팀','클라우드분석가')
,(21828,'TEST1818','클라우드기술팀','클라우드분석가')
,(21829,'TEST1919','클라우드기술팀','클라우드분석가')
,(21830,'TEST2020','클라우드기술팀','클라우드분석가')
,(21831,'TEST2121','클라우드기술팀','클라우드분석가')
,(21832,'TEST2222','데이터분석팀',NULL)
;
INSERT INTO EST_CHG_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(21833,'TEST2323','클라우드기술팀','클라우드 기술 분석가')
,(21834,'TEST2424','클라우드기술팀','테스트분석가')
,(21835,'TEST2525','클라우드기술팀','클라우드분석가')
,(21836,'TEST2626','DB팀',NULL)
,(21837,'TEST2727','클라우드기술팀',NULL)
,(21838,'브라이언','DB팀',NULL)
,(21839,'TEST2929','클라우드기술팀',NULL)
,(21840,'TEST3030','클라우드기술팀',NULL)
,(21841,'TEST3131','클라우드기술팀',NULL)
,(21842,'TEST3232','클라우드기술팀',NULL)
;
INSERT INTO EST_CHG_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(21843,'TEST3333','DB팀',NULL)
,(21844,'TEST3434','클라우드기술팀',NULL)
,(21845,'TEST3535','DB팀','관리권한자')
,(21846,'TEST3636','클라우드기술팀',NULL)
,(21847,'토마스','클라우드기술팀',NULL)
,(21848,'TEST3838','클라우드기술팀',NULL)
,(21849,'TEST3939','클라우드기술팀',NULL)
,(21850,'TEST4040','클라우드기술팀',NULL)
,(21851,'TEST4141','DB팀','관리권한자')
,(21852,'TEST4242','클라우드기술팀',NULL)
;
INSERT INTO EST_CHG_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(21853,'르브론','파생상품팀',NULL)
,(21854,'TEST4444','파생상품팀',NULL)
,(21855,'TEST4545','파생상품팀',NULL)
,(21856,'TEST4646','파생상품팀','파생 상품 관리팀')
,(21857,'TEST4747','DB팀','관리권한자')
,(21858,'TEST4848','파생상품팀','파생 상품 관리팀')
,(21859,'TEST4949','파생상품팀','파생 상품 관리팀')
,(21860,'어빙','파생상품팀',NULL)
,(21861,'TEST5151','파생상품팀','파생 상품 관리팀')
,(21862,'TEST5252','데이터분석팀','관리권한자')
;
INSERT INTO EST_CHG_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(21863,'TEST5353','파생상품팀','파생 상품 관리팀')
,(21864,'TEST5454','파생상품팀','파생 상품 관리팀')
,(21865,'탐슨','파생상품팀','파생 상품 관리팀')
,(21866,'TEST5656','파생상품팀','파생 상품 관리팀')
,(21867,'TEST5757','파생상품팀','파생 상품 관리팀')
,(21868,'TEST5858','데이터분석팀','관리권한자')
,(21869,'TEST5959','파생상품팀','파생 상품 관리팀')
,(21870,'TEST6060','파생상품팀','파생 상품 관리팀')
,(21871,'커리','데이터분석팀','관리권한자')
,(21872,'TEST6262','파생상품팀','파생 상품 관리팀')
;
INSERT INTO EST_CHG_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(21873,'TEST6363','파생상품팀','파생 상품 관리팀')
,(21874,'TEST6464','DB팀','관리권한자')
,(21875,'TEST6565','파생상품팀','파생 상품 관리팀')
,(21876,'TEST6666','파생상품팀','파생 상품 관리팀')
,(21877,'손흥민','DB팀','관리권한자')
,(21878,'TEST6868','백앤드팀',NULL)
,(21879,'TEST6969','백앤드팀',NULL)
,(21880,'TEST7070','백앤드팀',NULL)
,(21881,'TEST7171','백앤드팀','백앤드 자바 ')
,(21882,'TEST7272','DB팀','관리권한자')
;
INSERT INTO EST_CHG_USER (USER_NO,USER_NM,USER_GRP_NM,USER_DESC) VALUES 
(21883,'TEST7373','DB팀','관리권한자')
,(21884,'박지성','파생상품팀','파생 상품 관리팀')
,(21885,'TEST7575','파생상품팀','파생 상품 관리팀')
,(21886,'TEST7676','DB팀','관리권한자')
,(21910,'TEST100100','파생상품팀','파생 상품 관리팀')
;

 

4. 두 테이블 비교 쿼리

  - 변경된 데이터를 기준으로 원본 데이터를 비교해서 결과를 추출합니다. 

  - 변경 전/후 동일한지 비교 여부(Y/N)에 따라서 같지 않은 데이터를 추출해서 비교 대상 항목에 따라 row로 변경 합니다.

  - row로 변경된 컬럼을 다시 항목별로 데이터를 정리합니다.

SELECT   A.USER_NO
        ,A.NAME
        ,A.BF_VALUE
        ,A.AF_vALUE
FROM     (
         SELECT   A.USER_NO
                 ,CASE WHEN B.RN = 1 THEN 'User Name'
                       WHEN B.RN = 2 THEN 'User group Name'
                       WHEN B.RN = 3 THEN 'User Description'
                  END AS NAME
                 ,CASE WHEN B.RN = 1 AND A.USER_NM_CHG_YN = 'N'     THEN BF_USER_NM
                       WHEN B.RN = 2 AND A.USER_GRP_NM_CHG_YN = 'N' THEN BF_USER_GRP_NM
                       WHEN B.RN = 3 AND A.USER_DESC_CHG_YN = 'N'   THEN BF_USER_DESC
                       ELSE NULL
                  END AS BF_VALUE
                 ,CASE WHEN B.RN = 1 AND A.USER_NM_CHG_YN = 'N'     THEN AF_USER_NM
                       WHEN B.RN = 2 AND A.USER_GRP_NM_CHG_YN = 'N' THEN AF_USER_GRP_NM
                       WHEN B.RN = 3 AND A.USER_DESC_CHG_YN = 'N'   THEN AF_USER_DESC
                       ELSE NULL
                  END AS AF_VALUE         
         FROM     (
                  SELECT   A.USER_NO
                          ,A.USER_NM AS AF_USER_NM
                          ,B.USER_NM AS BF_USER_NM
                          ,CASE WHEN IFNULL(A.USER_NM, '#NULL#') = IFNULL(B.USER_NM, '#NULL#') THEN 'Y' ELSE 'N' END USER_NM_CHG_YN
                          ,A.USER_GRP_NM AS AF_USER_GRP_NM
                          ,B.USER_GRP_NM AS BF_USER_GRP_NM
                          ,CASE WHEN IFNULL(A.USER_GRP_NM, '#NULL#') = IFNULL(B.USER_GRP_NM, '#NULL#') THEN 'Y' ELSE 'N' END USER_GRP_NM_CHG_YN
                          ,A.USER_DESC AS AF_USER_DESC
                          ,B.USER_DESC AS BF_USER_DESC
                          ,CASE WHEN IFNULL(A.USER_DESC, '#NULL#') = IFNULL(B.USER_DESC, '#NULL#') THEN 'Y' ELSE 'N' END USER_DESC_CHG_YN  
                  FROM     EST_CHG_USER A /* 변경된 데이터를 기준  */
                           INNER JOIN EST_CURR_USER B ON (B.USER_NO = A.USER_NO)
                  ) A,
                  (SELECT   SEQ AS RN FROM     seq_1_to_3) B /* 항목 기준으로 카티션 곱  */
         WHERE    'N' IN (A.USER_NM_CHG_YN, A.USER_GRP_NM_CHG_YN, A.USER_DESC_CHG_YN) /* [조건] 변경된 항목 조회  */
         ) A
WHERE    A.BF_VALUE IS NOT NULL 
OR       A.AF_VALUE IS NOT NULL
;

[결과]

|USER_NO    |NAME            |BF_VALUE            |AF_VALUE         |
|-----------|----------------|--------------------|-----------------|
|11011      |User group Name |DB팀                 |데이터분석팀          |
|11011      |User Description|                    |TEST             |
|11222      |User Description|백앤드 자바              |백앤드 총괄           |
|11224      |User Name       |TEST11414           |박수철              |
|11225      |User Name       |TEST11515           |박이선              |
|21828      |User Description|클라우드 기술 분석가         |클라우드분석가          |
|21829      |User Description|클라우드 기술 분석가         |클라우드분석가          |
|21830      |User Description|클라우드 기술 분석가         |클라우드분석가          |
|21831      |User Description|클라우드 기술 분석가         |클라우드분석가          |
|21832      |User group Name |DB팀                 |데이터분석팀          |
|21834      |User Description|클라우드 기술 분석가         |테스트분석가           |
|21835      |User Description|                    |클라우드분석가         |
|21838      |User Name       |TEST2828            |브라이언             |
|21847      |User Name       |TEST3737            |토마스              |
|21853      |User Name       |TEST4343            |르브론              |
|21860      |User Name       |TEST5050            |어빙               |
|21860      |User Description|파생 상품 관리팀           |                |
|21862      |User group Name |DB팀                 |데이터분석팀          |
|21868      |User group Name |DB팀                 |데이터분석팀          |
|21871      |User Name       |TEST6161            |커리               |
|21871      |User group Name |DB팀                 |데이터분석팀          |
|21877      |User Name       |TEST6767            |손흥민              |
|21884      |User Name       |TEST7474            |박지성              |